Dynamic Enum Lists from Non-Selected Enum List

D_harrier
Participant III

Greetings!

Here is my challenge that I am trying to solve. I have a database with over 300 sites, and 5000 objects at those sites. The sites get updates monthly, and the objects get updated weekly. Uploading one master sheet and building this survey tool is the most effective way to mange.

So here is the basis of what I have. Currently have a table with two reference columns that create dynamic enum lists for each. [Sites] and [Objects]. These are EnumLists, with the base type of Ref and the โ€˜Mainโ€™ as the table referenced above. This portion works great.

What I needโ€ฆ is we have 5 areas of conditioning that needs to be done on each object per site visit. Iโ€™ve got a form created that has each area in a tab, and the EnumList of the objects below. My challenge is that ALL of the listed objects must be conditioned each visit, and each object can only be conditioned in on category.

Example:
Good Condition - Tab (header row)
Enum List:
Object 1
Object 2
Object 3

Fair Condition - Tab (header row)
Enum List:
Object 1
Object 2
Object 3

etcโ€ฆ

If the user selected Object 2 and 3 in the โ€˜goodโ€™ tab, I would want those to be eliminated as selections in the โ€˜fairโ€™ tab. Only Object 1 would be listed as only it would be selected. Finally, before submitting, if any object had not been conditioned, it would be listed on a โ€˜missingโ€™ list, and require the user to go back and condition.

Any help is appreciated!

Thanks

Solved Solved
0 13 1,914
1 ACCEPTED SOLUTION

Good enough! In your expressions, instead of using Sites[Objects], use this:

SELECT(
  Sites[Objects],
  AND(
    ISNOTBLANK([Location]),
    ([Location] = [_THISROW].[Location])
  )
)

This gets the Objects column values in the Sites table only from rows where the Location column has a value and that value is the same as the Location column in current Objects table row.

See also:

View solution in original post

13 REPLIES 13

LeventK
Participant V

Thanks.
I see the example, and can make the lists work in a simplistic view. If I wanted to make a new Enumlist from the items not selected in the previous Enumlist, how would this look?

Assuming the main Enumlist that pulls from the Ref table, would remain unchanged, then the subsequent lists would use the list expression to build them?

How would you build the โ€˜Fair Conditionโ€™ Emumlist, with only the non-selected Objects from the โ€˜Good Conditionโ€™ list, listed?

Thank you!

Use list subtraction.

unchosen options = all options - chosen options

Thanks Steve.

I must JUST be missing this, but this is where I am stuck at:
Fair Condition Emum list, has a base type of Ref with reference to the existing table โ€˜Conditionโ€™ with the other condition questions (good, fair, poor, etc).

I am using the Valid_if expression [Good Condition]-[Fair Condition] and I am getting (almost) the values I want in the new Enumlist [Fair Condition]. Its showing all of the SELECTED items from the Good Condition list, not the Non-Selected items.

Iโ€™ve tried switching the columns around in the expression, with no luck. Has to be something simple I am missing right?

Thanks

Please post a screenshot of the tableโ€™s column list from the Data >> Columns tab in the app editor, showing at least each of the columns youโ€™ve mentioned above. That should help me give you some concrete expressions.

Generally, though, the Valid If expression of each successive status would start with the list of all options then subtract the choices from all of the other status choices.

(Main[Objects] - [Fair Condition] - [Poor Condition] ...)
(Main[Objects] - [Good Condition] - [Poor Condition] ...)
(Main[Objects] - [Good Condition] - [Fair Condition] ...)

etc.

Thanks, that gets really close. The tricky part is that using the example you gave, I am not getting only the filtered objects for that particular Location (like what is displayed in the first Enumlist โ€˜Good Conditionโ€™), it is giving me ALL the objects that are possible from All Locations on the Sites[Objects] ref table when I use the expression (Sites[Objects] - [Good Condition] - [Fair Condition]) in the [Poor Condition] Valid_if field.

I need to be able to condition only the objects that are present at each Location, as they mostly different between sites, and very dynamic. Thus using a main table to manage the cascade of enum values.

Below is the screen shot of the column names from the example above:

Okay, this should be easy to fix. Sites[Objects] produces a list of all values of the Objects column of the Sites table, but you appear to want only those Objects column values from rows of the Sites table that are related to the current location identified by the current row of the Objects table.

How does the current row of the Objects table identify the current location? How can that be used to identify related rows of the Sites table?

Your summary is spot on.

The table that feeds sites has [Location] and [Objects] columns, both are set to text. Then on the Objects table for [Location] I use Valid if = Sites[Location], and then Valid if = Sites[Objects] for the various condition EnumLists.

Good enough! In your expressions, instead of using Sites[Objects], use this:

SELECT(
  Sites[Objects],
  AND(
    ISNOTBLANK([Location]),
    ([Location] = [_THISROW].[Location])
  )
)

This gets the Objects column values in the Sites table only from rows where the Location column has a value and that value is the same as the Location column in current Objects table row.

See also:

Really appreciate you walking through this. It works perfectly! I added the list subtractions for the various column field and it works exactly as intended.

Thank you!

Sir, 

I need your suggestion for emumlist. 

I have 2 column one is enum and other is enumlist. In enum i have 1,2,3,4 dropdown and in enumlist i have 13 dropdown. But i want if I select 4 in enum column then I can't select more than 4 values from enum list. Kindly suggest

Please start a new topic for help with this.

Top Labels in this Space