Separating Comma Vales in Cells into Filtered Dropdown Menus

Good afternoon

I’ve made an app that allows me to gather performance indicators on employees across a range of sites. Here are some example tables:

Contacts

SiteID Site Suburb Region Employee Employee Mobile Employee Avail. Employee Email Manager Server Naming Scheme School Address Latitude Longitude LatLong
123 Site Jelly Suburb A Alpha Julie M, T, W, Th, F 123 - Site Jelly, Suburb A 10 Fake Address, Suburb A
456 Site Oreo Suburb B Alpha Wendy,
Luke
M, T, W
W, Th, F
124 - Site Jelly, Suburb A 11 Fake Address, Suburb B
789 Site Jelly Suburb C Delta Charlie Th, F 125 - Site Jelly, Suburb A 12 Fake Address, Suburb C

Employee Records

Date Site Employee Work Overview Workload Projects Comments Manager Comments
01/01/20 123 Julie Completed monthly sales reports High None at this time.
01/01/20 789 Charlie Completed onboarding Average Sales initiative

These tables are referencing each other via the Site cell (i.e. SiteID is being used as the Key and Site as the Label). The Trainee cell is derefferenced based off of what Site is selected in the form.

This method will not work for rows of data such as these where more than one employee exists in the employee cell such as below:

SiteID Site Suburb Region Employee Employee Mobile Employee Avail. Employee Email Manager Server Naming Scheme School Address Latitude Longitude LatLong
456 Site Oreo Suburb B Alpha Wendy,
Luke
M, T, W
W, Th, F
124 - Site Jelly, Suburb A 11 Fake Address, Suburb B

There are numerous entries such as these within the full Contacts spreadsheet and this is unable to be changed (i.e. this spreadsheet links to other documents within my organisation).

In short:

Can I have AppSheet read and separate these data values (e.g. “Wendy, Luke”) and display them as a dropdown menu IF I select a site that has more than one employee in the Employee Column?

I’m venturing into new territory here, and I’m sure to run into more bumps and snags whilst figuring this out.

Any advise is appreciated and please feel free to ask me for more info.

Kind regards

This is way too much for me to digest. Would suggest you post only one problem at a time, and find a way to simplify the problem description.

2 Likes

Hi Steve

Apologies, I figured more information was the way to go :sweat_smile:.

I’ve cut down my question and will post the remainder in a new thread - please let me know if you needed clarification on anything I’ve posted.

2 Likes

Do you mean “Employee” here instead of “Trainee”? I’m going to assume so.

Sure. You can keep your current dereference expression as a portion of the Initial Value, but add in an IF() to only run it if there is a single Employee, and return a blank string if multiple. Then you can set a Valid_If expression to produce a List of the de-ref’d Employees if there are multiple.

What column Type is the [Employee] column in Contacts Table?

If it is just Text, you’ll need to use SPLIT() to convert it to a List. If it already is an EnumList, then you can just reference it in your Valid_if straight away. And utilize COUNT() to determine if there are more than one.

3 Likes

Hi Marc

Thanks for getting back to me.

Yes sorry I meant “Employee” instead of “Trainee”. On top of that, I had some weird times when setting the column type for Employee. I had set it to Name , then some expressions didn’t like that so I tried setting it as a Ref but the same would happen again.

This continued for a bit and I wasn’t sure what to select; I didn’t want App Users to be able to edit the actual name of the Employee in the form if it was just a Text .

How would should I go about constructing the IF statement, something like:

IF((COUNT([Employee])>1), [Site].[Employee],__________________)

Something similar to that?

Kind regards

I can’t say I’ve ever understood what the benefit of the Name column type is, above and beyond a simple Text. For what reason did you set it to Ref, and which Table were you referencing, an Employee Table perhaps? In either case, since you’ve already mentioned it may contain more than one value, you should probably start by setting it as EnumList, then setting the Base Type to what you want.

You should use an appropriate Valid_if expression to validate any input.

Let’s assume you do set it as an EnumList above. Closer to this, for the Initial Value:

IF(
  COUNT( [Site].[Employee] ) = 1 ,
  ANY( [Site].[Employee] ) ,
  ""
)

and since, as an EnumList, it will be a List type, even if there only is one record, the Valid_if can simply be:

[Site].[Employee]
2 Likes

Hi Marc

I meddled around with a few expressions and came up with this one for the Initial Value field:

IF((COUNT(Contacts[Employee]))>1,“HAHA”, [Site].[Employee])

This kind of did something? At the very least didn’t get any errors.

I then tried the one pasted above, but got the following error on the Column view and the Expression builder respectively:

Column Name ‘Trainee’ in Schema ‘Trainee Records_Schema’ of Column Type ‘EnumList’ has an invalid Initial Value of ‘=IF( COUNT( [School].[Trainee] ) = 1 , ANY( [School].[Trainee] ) , “” )’. COUNT function is used incorrectly

COUNT function is used incorrectly

^^School = Site, Trainee = Employee | I’ve adapted my data tables as shown above as they contained sensitive info.^^

Employee column was made EnumList and Enum with a base of Name, Text, and List.

Any ideas?

That’s essentially just counting the number of records in your Table.

That would indicate the referenced column ( [Trainee] ) is not a List.

Huh?

1 Like

When editing a column of type Name, the app will suggest entries from the device’s contacts.

3 Likes

Hi Marc

Sorry, I tried making the Employee column a few different types - seeing what would happen.

I’ve thrown this in the pot - it doesn’t work but I don’t get errors at least.

IF(COUNT(SPLIT([_THISROW].[Employee],","))=1,“HAHA”, “HEHE”)

I’m using this data:

SiteID Site Suburb Region Employee Employee Mobile Employee Avail. Employee Email Manager Server Naming Scheme School Address Latitude Longitude LatLong
456 Site Oreo Suburb B Alpha Wendy,
Luke
M, T, W
W, Th, F
124 - Site Jelly, Suburb A 11 Fake Address, Suburb B

When I select Site Oreo for Wendy and Luke (i.e. Wendy, Luke) I still get “HAHA” and not “HEHE”.

(i.e. I will need to create a Computed Label at some point as there are multiple ‘Site Oreo’ in my data tables - but that should be simple)

What should I try?

I was going to suggest using SPLIT if you had set the column type to Text, instead of EnumList.

Why are you using [_THISROW] though? You’re running this expression in the other Table that is ref’d to the data you’ve shown, right? You should be using [Site].[Employee].

2 Likes

@Marc_Dillon
I’m having issues with the ‘Reply’ feature, I always hit reply but it consistently tries making it a new comment.

Ah - yes another blunder on my part. Secondly, AGHH I’ve gotten “HEHE”, I’ve gotten “HEHE”!

Currently the column type is ‘Name’ - but I tested with using Enum and EnumList → AppSheet was happy with all three. I figure I will keep it at Enum as I only want the user to select 1 Employee per Site.

I can see ALL the Employee’s however when I go into the dropdown but I figure that is because I’ve not made a Valid_If rule yet no?

The IF is working - now I just need actual conditions.

I referred to something earlier along in the thread and tried using ANY([Site].[Employee]) - it did not like this and gave a long winded error that I used ANY incorrectly.

How can I get it to display/select the Employee/s for the respective Site? (i.e. as a dropdown if there is more than one Employee like Wendy, Luke)

Where to from here?

Sounds like you’re wanting to use a non-List type for the parent’s [Employee] column, which means you have to SPLIT() it into a List to use it how I mentioned above, including with ANY().

…or rather SPLIT( [Site].[Employee] , "," )

1 Like

@Marc_Dillon

Hey Marc

For the Initial Value field I inputted this:
IF(COUNT(SPLIT([Site].[Employee],","))=1,ANY(LIST([Site].[Employee])),ANY((SPLIT(LIST([Site].[Employee]),","))))

I’m not sure if there was a cleaner way to do this but I’m rolling with it. I run into the issue where it will show me Wendy, but not Luke in the drop down menu (i.e. this is with Valid_If not applied).

For the Valid_If field I inputted this:

SPLIT( [Site].[Employee] , “,” ) - This somewhat worked and would show me the Employee for the selected site - but strangely as Buttons for some, and Dropdowns for others. This time it would show me Wendy and Luke but I could only select Wendy.

ABBI

What should I try?

Edit: This seemed to go away after I resynced a few times - I am not sure what caused it.

Hi @Marc_Dillon

Thank you for your patience throughout all this, but I think I have something:

Initial Value
IF(COUNT(SPLIT([Site].[Employee],","))=1,ANY(LIST([Site].[Employee])),SPLIT([Site].[Employee],", "))

Valid_If
SPLIT([Site].[Employee],", ")

Employee Column Type
Enum

Previously I was having the issue of it not allowing me to select “Luke”, then it was displaying Wendy as 'Wendy,". I added a single space to the delimiter syntax portion of SPLIT (i.e. ", ").

I don’t know how but it seems to be displaying both Wendy and Luke from the table data “Wendy, Luke” and it allows me to select both.

Does this look right and would you be able to help me unpack it?