enumlist with a variable list based on inputs given by the user

[Carlo_COSTA]

I am tying to implement a list that changes based on the inputs of the user.

the behavior i am trying to implement is to show a subset of the table Appro if the emplyee number starts with 51 and show another subset of the table Appro if the employee number starts with somthing else than 51. 

here is my attempt of implementing this : 

filter( Appro, or( and( [Unite] = 51, number(left([UADM],2)) >= 51), isblank([Unite]) ) )

the output of this function is correct but the problem is that the dropdown list does not appear when I put the code in the valid_if section

this is the view I get with only formulaire[Appro] in the valid_if

this is the view i get with my code in the valid_if

[OptimiX_XcrY]

My rookie way to a similar situation would be : 

1. Create a slice for the subset you need. 

2. For the Enumlist dropdown you need , I'd use a Suggested Value , sort(TableSlice[column you want])

Edit : I think the smart way would be to use a select() function, instead of filter() , so you could study it for your case

[WillowMobileSys]

I don't see anything logically wrong with your expression.  Check that the column data types for Unite and UADM are Number

Here is how I would write the expression - this assumes that you have only the 2 options and that types are Text:

if (STARTSWITH([UADM, "51"),
    FILTER("Appro", [Unite] = "51"),
    FILTER("Appro", ISBLANK([Unite]))

)

I find this easier to understand.  

[Joseph_Seddik]

I think, for the drop list to show, you have to put your expression in: Suggested Values field. 

[WillowMobileSys]

Wait a minute!   Your filter expression is using the column named "Appro" as the table name.  It should be like this:

filter( "formulaire", or( and( [Unite] = 51, number(left([UADM],2)) >= 51), isblank([Unite]) ) )

The FILTER returns row references.  In order for the dropdown to show the Appro column value in the dropdown, the Appro column must be marked as the Label column.

[Ryan_Mortimer]

This may or may not help, but to get things thinking, here is what I would suggest as a starting point.

In your data set, I would structure things a little differently. I would create a list of records and their corresponding "attribute". Take my example below using car makes and models.

Unite Appro

51	Option1
51	Option2
59	Option3
51	Option4

Now, for your first column

Column: Unite

Type: ENUM / EnumList

Suggested Values:

 SELECT(YourSheetNameHere[Unite], TRUE)​

and your second column column

Column: Make

Type: ENUM / EnumList

Suggested Values: 

SELECT(YourSheetNameHere[Appro], [Unite] = [_THISROW].[Unite])​

So in that example, assuming that you selected the value of "51" in the Unite column on your form entry - your Appro column will then have the following values to select from:

• Option 1
• Option 2
• Option 4

[Carlo_COSTA]

I tried all the suggested solutions but nothing worked!

what i now have is the expression : select(Appro[Appro], [Unite] = 51)

I know that the code does not have the same output then the original code but i wanted to keep the expression super simple because the main probleme is that the sorted list does not appear in my form.

i have set the "Approvisionnement" variable as an enum 

and the code above is in the valid_if section

 the capture shows what the output should be in the list of my form 

here we can see that the whole list is displayed instead of only Interrogation de l'approvisionnement and  Saisie des demandes d'achat (AEL)

 

 

[WillowMobileSys]

---

@Carlo_COSTA wrote:

here we can see that the whole list is displayed instead of only Interrogation de l'approvisionnement and  Saisie des demandes d'achat (AEL)

---

A couple things:

• In your column [Approvisionnement], is there any expression inserted into "Auto Compute -> Suggested Values"  property?  If so, remove it.
•  I am a little confused by the name [Appro].  Just in the column screenshot I see the name used twice in different ways - one as a Yes/No column and the other is in a different table used at the return text value for the list.  Do you really have the same column name used for completely different purposes?  It's ok if you do and can keep them straight but it can lead to some confusion. 

It might be good for a reset.  What you are trying to do is not complicated and the fact we are struggling to help usually means we are not getting the necessary details we need.  So I would like to request:

• Additional details of the [Approvisionnement] column
• Screenshots of the Formulaire table columns - from  AppSheet not the Google sheet.
• Screenshots of the Appro table columns - from  AppSheet not the Google sheet.
• Updated screenshot of the Appro sheet in Google

[Carlo_COSTA]

there is nothing inside the auto compute section. 

 

Appro is the name of a table and the name of a column 

 

i dont see where the Appro is used as a yes/no so i cant really comment on this point.

the screenshots of the formulaire table would be too long to post however, I think that this will be enough 

 

if more information is needed I will gladly provide it !

[WillowMobileSys · Accepted solution]

Just yesterday I discovered an issue where <empty> cells were not being treated as I know they were in the past.  It was a column for TRUE/FALSE values so I just populated it with FALSE and moved on.

I wonder if the <empty> cells are causing bigger issues, maybe some bug recently introduce?  Try inserting values for [Unite] in the rest of the Appro table and then double-check the dropdown to see if it is filtered properly then.

One odd thing I noticed,  in your first screenshot of the [Approvisionnement] column the Show If has the expression "[Appro] =y" - this looks to be a Yes/No column?  I don't see an [Appro] column in the Formulaire table image but maybe it's just not shown?  This likely has nothing to do with the rest of the issues you are seeing but just seems strange.  I wonder if there could be any naming clash with your Valid If expression?  If you do have an [Appro] column in the Formulaire table as a Yes/No type, then you might consider renaming it to maybe [Appro?] to eliminate any possible naming clash.

Other than these obscur possible crazy issues, I am not seeing anything else that would explain the behavior you are seeing.

[Carlo_COSTA]

okay now i see what @WillowMobileSys meant by the [Appro] being a boolean, i changed the name of the variable to Appro_bool, 

 

i also changed the unite column and added zeros to all the rows that did not contain the value 51 and now it works thank you so much guys! 

you were of great help