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.
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.
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.
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]
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?
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].
@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] , "," )
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?
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?
When editing a column of type Name, the app will suggest entries from the deviceโs contacts.
User | Count |
---|---|
40 | |
36 | |
34 | |
23 | |
17 |