Dependent Drop-down in Nested Form

Luis_Paliotes · 01-05-2021 01:10 PM

Hello,

I’m new at appsheet and on my first attempt to create an app I already big (at least for my skills…).

My app has a nested form with four tables.
PARENT FORM
CHILD FORM
COMPANIES
EXPENSES

In the parent form which company is selected through a dependent drop-down list (Region - County - Company based on values in Table “Companies”)

In the child form is the expense submition. Here I would like to based on the country selected in Parent Form, to build a dependent drop-down list for the expenses (Country - Expense Category - Expense Type, based on the information in “Expenses” table.

Is this possible?

LeventK

Luis_Paliotes

I suppose it’s great that several users such as miself without much knowledge on Appsheet or programming see the potential in this tool to aim for the Pulitzer Price.

As I mentioned, I’m a beginner so I wasn’t really sure what to look for and therefore I’ve asked that’s what community are for.

Before posting I’ve searched and re-searched based on my need and not on technical stuff which I was unaware of.

Marc_Dillon

We recently gained the ability to use chained de-refs, So I’m going to try and use those, but the following is not tested.

I assume you have a [Country] column selection in the parent table, and your first selection in the child table will be the [Expense Category]. In this case, set a valid_if expression for the [Expense Category] column, similar to:

SELECT( Expenses[Expense Category] , [Country] = [_THISROW].[Parent Ref].[Country] )

Luis_Paliotes

Thank you very much for the advise. I will try it tomorrow. I let you know If it worked and in case I’ll need to make some readjustment I’ll provide with the solution based on your guidance.

Luis_Paliotes

Hello Marc,

Once again, I’m thankfull for you help.

I readjusted the name of columns and tables accordingly as follows:
“SELECT(Accounting Information[Expense Category] , [Country] = [_THISROW].[Request Number].[Country of Company] )”

This raised another question which is the selection of the Expense Type which should be dependable of the Expense Category.

I’ve tried applying the same logic, but it didn’t worked.

Logic:
Form 1 (Parent): Region > Country > Company
Nested Form 2 (Chlid) Country (previously selected) > Expense Category > Expense Type

I was thinking that in Form2 Table I could add a Column “Country” that would be automatically populated upon the selection of Country in Form 1 using Initial values, then follow with the Normal Depend Dropdown List logic (“Form2[Country], Form2[Expense Category], Form2[Expense type]”)

Marc_Dillon

I most often try to avoid Appsheet’s automatic dependent dropdown functionality. That’s just a personal preference, I find it a bit…fiddly?..maybe presumptive?

But sure, you could do that by adding a [Country] column to your child table, which auto-populates from the parent’s [Country]. Then you wouldn’t use the SELECT() expression from my first post.

Or, you don’t need to redundantly copy that data. For the next column [Expense Type], you can set a valid_if expression similar to my first SELECT() expression. How do you think you would modify that to achieve what you need? To filter the Types by the previously selected category (also maybe AND the Country again, depending on your needs).

Luis_Paliotes

I’ve went for option Two by creating an extra column in Child Form Table.

I’ve tried so many things, that in fact I no longer know what exactly I’ve done to make it work.

Tomorrow I’ll start to make version 2 from scratch. Hopefully I will better understand what I’m doing.l and I’ll leave the solution here in case it’s useful to someone.

Luis_Paliotes

Ok, so I’ve build the version 2 of my app with Dependent Dropdow List in Nested Form.

Tables:
Expense (Parent)
Receipt (Child)
List of Country (Containing Region, Country, Company)
Accounting Information (Containing Country, Expense Category, Expense Type)

My objective was to have the correspondet Expense Category in Child based on the Country selected in the Parent.

I got this by creating a Country Column in the Child.
For this Column which is set as Text, I have in initial value formula “[_THISROW].[Expense].[Country]” then a Valid If “Accounting Information[Country]”

@Marc_Dillon once again, Thank YOU!!! Although I didn’t used your solution, your solution/feedback not only unblocked this for me but also made understand a little bit better APPHEET behaviour. I’m really appreciated.

Marc_Dillon

Great! Glad to help, and glad you’ve got a fuller understanding.