Help with dependent dropdown filter

Zee_W · 02-06-2020 04:34 PM

Hello, I’m stuck on something for the last few days.

I am creating an app to track the amount of time operators spend on a given activity during production. The app is based on the structure of the Order Capture app.

Operators will enter top level data for any given production order, i.e. Employee Name, Date, Category, Production order, in the Entries table. I then created a related table (Entry Details) where they will breakdown the hours spent based on specific operation and activity related to the specified production order.

The Entry Details table has the following columns: DetailsID (key), EntryID(ref to EntryID in Entries table), Operation, Machine, Activity, Part, Time Spent, Time Type.

I have an Operation table with the following columns: Code, Work Center, Operation Code, Operation Description. Key is Concatenate Code and Operation Code.

I am trying to set up so that when operators input the specific Operation, they only see the operations relevant to them.

I have an Operators table with the following columns: EmployeeID, Name, Work Center, Primary Work Area. Key is Name.

I also created a dereference between the Entry Details and Operators table in a virtual column [EntryID].[Employee Name].

In the Operation column of the Entry Details table, I have it set as Enum and Valid_if expression: SELECT(Operation[Operation Description],([Work Centre] = [_THISROW].[EmployeeRef])).

Nothing is showing up now. I know I’m missing something but not sure where to begin!

Advice, please?

Kirk_Masden

I’m not sure I fully understand the problem, but I’ll take a stab. You listed the columns in your tables but I don’t see either [Work Centre] or [EmployeeRef] in your lists.

If you wanted to get a list of all of the [Operation Description]s for a particular employee, wouldn’t you want your formula to be something like the following?

SELECT(Operation[Operation Description],([EmployeeRef] = [_THISROW].[EmployeeRef]))

Again, sorry if I’m failing to understand what you are trying to do.

SELECT(Orders[Order ID], ([Customer] = [_THISROW].[Customer])) returns the Order ID column values (the row keys) for rows in the Orders data set in which the Customers column value is equal to the Customers column value of the current form (i.e., orders for this customer). Equivalent to FILTER(“Orders”, ([Customer] = [_THISROW].[Customer])). See also: FILTER().

Zee_W

Hi @Kirk_Masden , my head was wrecked at 1am so probably didn’t do well explaining myself!

[Work Centre] is in the “Operators” table and [EmployeeRef] is in the “Entry Details” table. I was trying to create a dereference there.

I tried that but AppSheet was giving me all the Operation Descriptions

Yep, that’s the basis of what I’m trying to do trying a few variations didn’t produce what I’m looking for.

Kirk_Masden

Thanks for the clarification, @Zee_W!

I think I should admit that this is getting over my head. Perhaps @Marc_Dillon knows what the next step should be. If not, perhaps some other AppSheet wizards (I mean besides Marc – I certain don’t include myself in the ‘wizards’ category ) will drop by to help.

Marc_Dillon

This column is called [EmployeeRef], correct?
Let’s create another de-ref column in “Entry Details” table, with expression [EmployeeRef].[WorkCenter], call it [EWC].
Then in your Select expression, replace [_THISROW].[EmployeeRef] with [_THISROW].[EWC].

Zee_W

Hi Marc!

The expression is giving me the list of Operation Descriptions (woohoo!) but it’s not being filtered by the Operator, i.e. Employee Name.

Steve

Try:

SORT(
  SELECT(
    Operation[Work Description],
    IN(
      [Work Center],
      SELECT(
        Operators[Work Center],
        ([_THISROW].[Employee Name] = [Name])
      )
    ),
    TRUE
  )
)

SELECT(Operators[Work Center], ...) gathers a list of Work Center column values from rows of the Operators table that match the given criteria (...; see (2)).
([_THISROW].[Employee Name] = [Name]) asks, does the Employee Name column value in form match the Name column value of the row of the Operators table?
SELECT(Operation[Work Description], ...) gathers a list of Work Description column values from the Operation table that match the given criteria (...; see (4)).
IN([Work Center], ...) asks, does the Work Center column value of the row of the Operation table match any of the values in the given list (...; see (1)).
SORT(...) sorts the resulting list (...; see (3)) in ascending (A-Z) order.

Or perhaps easier to understand:

Looking only the operators with the current employee’s name (2), get a list of the employee’s/operator’s work centers (1). Then, looking at only operations with a work center in that list (4), get the list of those operations’ descriptions (3) and sort that list (5).

Marc_Dillon

Just an update for others here. Zee has contacted me directly about the issue (we’ve worked together before). There were multiple issues causing the problem. The solution I posted above turned out to essentially be correct upon fixing the other unrelated issues, with just a few column name changes.

Steve’s answer also would have worked, again with a couple column name changes, but I believe the double Select expressions would have been more resource-intensive than pulling the 2nd de-ref, [EWC].

Zee_W

Thanks everyone for your helpful suggestions! I certainly confused myself with some of the column names and types. I do want to keep synch times down and aspects of the app as less resource intensive as possible (no high-speed broadband where we are).