Separating Comma Vales in Cells into Filtered Drop...

Quinn_Tucker · 08-08-2021 11:13 PM

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 Avail.	Server Naming Scheme	School Address
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

Steve

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.

Quinn_Tucker

Hi Steve

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

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.

Marc_Dillon

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.

Quinn_Tucker

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

Marc_Dillon

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]

Quinn_Tucker

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?

Marc_Dillon

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

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

Huh?

Quinn_Tucker

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?

Marc_Dillon

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].

Quinn_Tucker

@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?

Marc_Dillon

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] , "," )

Quinn_Tucker

@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.

What should I try?

Quinn_Tucker

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

Quinn_Tucker

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?

Steve

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

Separating Comma Vales in Cells into Filtered Dropdown Menus