Look up or Filter from form

1

I created a form where the user choose a project from a list of existing projects. To create this list I referenced a seperate table called PROJECTDB that list all projects where the display field is MasterProject.

Back on the form, when the user selects the project, the choices I gave them are not just the project but a combo of their name and the project. I did this by concatonate their name and MasterProject columns.

Now I want to use what they choose (the concatonated answer) to find prepopulate another field called Project Description. I added a similar column in the PROJECTDB that matches the concatonated results called RplusO so it can be searched.

All seems ok so far but I cannot get the right formula to return the description column. I tried lookup and filter but can’t figure out how to say lookup this output from my current for in PROJECTDB in RplusO column. Then when you find the row with that (each has a unique name so no issue with multiple) then populate the answer with the column PROJECTDB.description.

I tried so many options but can’t change the Display Column. Tried changing the display column a different View but couldn’t do that because the RplusO is a formula so i guess it cannot be a display column (doesn’t give me the option like other columns).

Tried various filter formulas but couldn’t get it to reference the users answer in the non submitted form against the PROJECTDB table.

I think it is a coding grammar issue but not sure.
Thanks for all the great help so far.

1 Like
2

Keep trying, read some documentation, check some youtube videos…

There are no shortcuts, you need to experiment and struggle a bit until you have your aha moment and then everything will fall into place.

1 Like
3

Man, I wish they were useful. Watched them both about 3 times. Will keep searching. Appreciate the help so far.

1 Like
4

Hello @Chris_DeAngelis,

Although I agree with @Pablo_DV that sometimes we need to struggle a bit to learn, I think that’s not the case here. Your use case isn’t the default behavior, and it requires some workarounds to make it function properly.

I haven’t tested your specific use case, and I don’t have much time to create a doc, so it’s always easier to help if you share a dummy doc that demonstrates the behavior along with an explanation of what you’re trying to achieve.

Here are a few points regarding your use case:

  1. Are you trying to have a calculated column that updates as soon as you select the project field in a form?

  2. Is your selection column a list of texts? For example, in the options formula, are you using something like: PROJECTDB.ForEach(Concatenate(currentValue.Name, " - ", currentValue.Desc))

  3. Are you using the native form or a published/embedded form? If you’re using a published/embedded form (accessible via a public URL), access to the doc’s data may be restricted. Try adding the same form inside the doc (using /form on any page) and see if the issue persists.

Possible Causes and Solutions

  • If it works in an in-doc form: The problem likely lies in how the data is being accessed. My initial thought is that the form table is not linked to the PROJECTDB table through a relation column, or you haven’t activated the “Include reference tables” option in the form settings.
  • If it doesn’t work in an in-doc form: Then the issue is likely with a formula or filter in your setup.

Additional Resources

Check out my two posts about forms; they include some useful tricks:

Let me know if this helps! :blush:

Best regards
Arnhold

2 Likes
5

Thank. And I do appreciate Pablo’s comments. The good news is I succeeded. I watched 20 videos on Youtube and elsewhere and couldn’t find a solution. I think I eventually started to get the logic of what filter was doing and then just tried a lot of things until it worked.

I also solved the problem of the fields not properly populating was due to two things. First, i had to change the field type on the form to either Canvas or Text. Secondly I had to use the form preview or link - for some reason some fields don’t populate on my desktop directly. But making progress.

Btw, all of this is to test and build something I can demo with my team but I am about to run into the limitations of the free account. Does Coda offer free trials on advanced features or temporary relaxation on limits. I hate paying to build a trial just to test if it works.

2 Likes
6

@Chris_DeAngelis
Are you serious?
Per month $12 for pro or $36 for team.
I switched from free to team within days of using Coda…and I never looked back.
It still is the very best deal out there (but yes, that is just my opinion).
Greetings, Joost

1 Like
7

After a good night sleep I realized the point of currentValue and figured it out

OpenRow(AddRow(Milestones, ActionDB.OverallDBinAI, OverallDB.filter(CurrentValue.MasterName=thisRow.MasterName)), Milestones)

8

Works but it still returns mutliple values from the filter table if the table has multiple values. Can’t get the placement right on unique() .

tried unique(OverallDB.filter(CurrentValue.MasterName=thisRow.MasterName)) and
tried OverallDB.filter(CurrentValue.MasterName=thisRow.MasterName).unique()

No luck so far

9

first() did the trick but not sure why unique didnt

10

Those two formulas do the same thing :slight_smile:

  • list.Unique()
  • Unique(list)

Unique() just takes a list and returns of list where it removed duplicate entries

11

So your saying unique() won’t work with a filter since the filter is not a list? Could I add list() to turn the results into a list and then use unique?

12

Filter() already returns a list so Unique() does work with that, but it doesn’t sound like Unique() is what you want

Let me show you some examples

Unique(list(3, 3)) -> list(3)

Unique(list(3)) -> list(3)

First(list(3)) -> 3
2 Likes
13

Thanks. So simple but yet important. Getting the concepts around an item or a list or what returns true/false takes some time. Much appreciated. Glad First was a reasonable approach.

1 Like