This is about the Construction Daily Reports sample app.
In the Foreman daily report view (this is a form view wherein a daily report is entered) the Crew on site field appears.
What I want:
Once a foreman fills out a daily report and selects the individuals on their site, I don’t want those individuals to appear in the enumlist to other foreman while they are filling their report for the same day.
Why I want this:
To eliminate the possibility of a foreman making a mistake by selecting individuals who were otherwise on another site & not on his.
The works won’t be recognized as being on-site until the foreman saves the form (given your current setup), so there’s no way to hide them from others. You’d need to separate identifying the on-site crew from the daily report.
ok, so if foreman 1 selects peter paul & bob from the list of 20 individuals and submits the report, then can the foreman 2 not have peter paul & bob show in his crew on site field while showing the remaining 17 individuals to choose from?
Thanks in advance!
Thanks Steve, please tell me how can this be achieved.
You can remove entries from a list by subtracting another list from it:
LIST("Bob", "Harry", "Tom") - LIST("Harry")
This would give you a list with only Bob and Tom.
Commonly, a list of potential workers would be contained in a separate table (e.g., Workers). Let’s suppose the table’s key column is named ID. To get a list of references to all workers, you could use:
Suppose your Jobs table has a column named Workers of type EnumList with a base type of Ref and a referenced table of Workers. This allows the Workers column value to contain the list of workers for the job.
You can use the column’s Suggested values expression to generate a list of workers that haven’t yet been assigned to a job:
(Workers[ID] - SPLIT(SELECT(Jobs[Workers], TRUE), " , "))
This starts with the list of all workers (
Workers[ID]) and removes from it the list of workers currently assigned (
SELECT(Jobs[Workers], TRUE)). Because SELECT() returns a list, and the Workers column contains an EnumList, we get a list of lists, which doesn’t work for our needs.
SPLIT(..., " , ") converts the list of lists into just one big list, which is what we need.
Sir, It worked beautifully!! Thanks a ton!
I also added the today function for only eliminating those entries that were entered today.
Same app as before but in the table Job I have a list of all the ongoing projects. This table only gets a new record whenever there is a new project awarded to us.
Now I have added a virtual column to this table with the following formula
COUNT(SPLIT(SELECT(Foreman Daily Report[Crew_On_Site],(AND([Date]<=TODAY(),[ProjectID]=[_THISROW].[Job_Name]))),","))
Why: I have a view for the Job table where I want to display this virtual column which shows the number of total man days (sum of workers present on that project on each day).
When I enter this expression and test it, it performs as expected but when I go to the View, the value that is shown there is 0 for this column.
Probable Cause: I am not entering any new data to the table Job, I am just trying to view the data in a View and expecting the virtual column value to update as the Foreman Daily Reports get filled everyday.
I hope it is making sense, if not then let me know and I will have another go at it.
Thanks in advance.
Your expression, reformatted for clarity:
COUNT( SPLIT( SELECT( Foreman Daily Report[Crew_On_Site], ( AND( ([Date] <= TODAY()), ([ProjectID] = [_THISROW].[Job_Name]) ) ) ), "," ) )
Note that the parentheses around the AND() expression are unneeded.
Try this instead:
COUNT( SPLIT( ( "" & SELECT( Foreman Daily Report[Crew_On_Site], AND( ([Date] <= TODAY()), ([ProjectID] = [_THISROW].[Job_Name]) ) ) ), " , " ) )
Sir, it worked as your solutions always do.
Thanks a ton.
I wanna know one thing though, for my learning, your article on the SPLIT() function says the following:
SPLIT( some-text , delimiter )
Whereas your solution has “”& inside of the split function, before the beginning of the select function. Why?
am I missing something somewhere?
("" & SELECT(...)) expression is a trick to convert the List that SELECT() produces into the Text that SPLIT() expects. Without it, I don’t fully understand what SPLIT() does with the List value, but it doesn’t seem to do anything useful. This is something I’ve figured out through trial and error.