Option elimination in enumlist

[Mayank_G]

Hi All,
Background:
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.
Thanks !

[Steve]

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.

[Mayank_G]

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!

[Steve]

Yes

[Mayank_G]

Thanks Steve, please tell me how can this be achieved.

[Steve]

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:

Workers[ID]

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.

[FrontLine]

Took me ages to find this but boy was it worth it. Sorted my issue out in minutes, the explanation and breakdown of the code really helped.

[Mayank_G]

Sir, It worked beautifully!! Thanks a ton!
I also added the today function for only eliminating those entries that were entered today.

[Mayank_G]

Hi Steve,
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.

[Steve · Accepted solution]

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

[Mayank_G]

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:

Syntax

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?

[Steve]

The ("" & 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.

[Mayank_G]