Sorted Dependant Dropdowns with Ref Columns

[Skip2MiLu]

Need some help from you guys on below.

Firstly, here is and overview of my table structure:

• Red = Separate worksheets (i.e. Products and Product Refs excel worksheets)
• Green = Tables within the worksheet
• Pink = Columns
• Underlined = Key columns
• Dotted lines connecting tables = Ref column types and where they are connect to

Secondly, is an example of data in the table:

Questions & Comments

1. Firstly, is it better to have all table located in one worksheet / spreadsheet in terms of performance?
   1.1. Will placing them separately like I have in any scenario be a benefit?
   1.2. I initially thought that separate them in different worksheets might be beneficial if I later decide to have other apps that will use specific table and that having them all in one worksheet would mean that the whole worksheet would need to be loaded by the app even though I only need 2 or 3 tables from 10 tables on the worksheet.

2. Is my data setup in the correct way?
   2.1. I am not sure if my ref are connect probably or efficiently.
   2.2. For example I was not sure if the Crop column in my Product table should be referenced to my the Crop table - like I have it - or if it should be referenced to the Crop column in the Product Segment table. I however thought that I could not have any other way as you probably can not ref another ref if you catch my drift.
   2.3. I also need to connect data via a ref as I later would like to connect another referenced table. For example a Note table that captures notes for the Crop / Crop Segment / Product Segment table in question. Or a Documents table that links to the same tables where I can example upload a general growing guide for lettuce (i.e Crop table) or maybe a more specific growing guide for Iceberg Lettuce (Crop Segment table). You get the idea.

3. I would now like to setup Drop Downs for the following columns in respective tables, but can not seem to get the Valid If expression correct.
   -They need to be Sorted by the Key Description column (black font) and not the Key column (red font).
   -Also some of them also need to be Dependant form what every is selected in the previous column.

3.1. Crop Segment Table:
3.1.1. Crop column = Sorted by Crop description

3.2. Prod Segment Table:
3.2.1. Crop column = Sorted by Crop description
3.2.2. Crop Segment column = Dependant following input from the previous Crop column input. Also needs to be sorted by Crop Segment description column in the Crop Segment Table.

3.3. Product Table:
3.3.1. Crop column = Sorted by Crop description
3.2.2. Crop Segment column = Dependant following input from previous Crop column input. Also needs to be sorted by Crop Segment description column in the Crop Segment Table.
3.2.3. Product Segment column = Dependant following input from the previous Crop Segment column input. Also needs to be sorted by Product Segment description column in the Product Segment column.

I hope my explanation is clear.
Thank for the help in advanced!

[Marc_Dillon]

Michael_Pinto:

Firstly, is it better to have all table located in one worksheet / spreadsheet in terms of performance?

In general, Appsheet has stated a solid “Yes”.

Michael_Pinto:

1.2. I initially thought that separate them in different worksheets might be beneficial if I later decide to have other apps that will use specific table and that having them all in one worksheet would mean that the whole worksheet would need to be loaded by the app even though I only need 2 or 3 tables from 10 tables on the worksheet.

I think as long as the extraneous tables aren’t loaded into the app, then it won’t largely affect performance. Could be wrong on this one.

Michael_Pinto:

Is my data setup in the correct way?

Not sure, please describe what a Crop,Crop Segment, Product, and Product Segment are meant to represent.

Michael_Pinto:

I would now like to setup Drop Downs for the following columns in respective tables, but can not seem to get the Valid If expression correct.
-They need to be Sorted by the Key Description column (black font) and not the Key column (red font).
-Also some of them also need to be Dependant form what every is selected in the previous column.

help.appsheet.com

ORDERBY()

Sort row references

help.appsheet.com

Dependent Dropdown

How to make a dropdown with choices that depend on the value selected in another dropdown.

You can always use an ORDERBY(FILTER()) in the valid_if field to get it to display a dropdown of exactly what you want. I’m not a huge fan of depending on the auto-filtering that Appsheet applies to sequential Enum fields, but then again I’ve also never took the time to learn exactly how it works.