How to filter a dropdown list based on value of a row on another table?

[Omid_hrawy]

Hi folk 

I would appreciate your expertise.

I have got a simple asset maintenance management app where assets are listed in the Assets Table. users can create work orders for services, installations, or repairs against each asset which stores in Work Orders Table.

My question is, how can I ensure that assets with open work orders are excluded from the dropdown list? if work order is open there is a column "workorder_status" with "Open" value.

in other way, I want to prevent users from opening two work orders for the same asset at the same time.

Any insights on this?

[MatthewEngel]

Hi there 

What i would do is to use the suggested values option in the dropdown that links the two sheets, most likely the assets column in the work orders table as a ref. 

Under suggested values use a select and isblank for the column in the assets table so that if the work order column is blank in the assets table it will show it as an option, alternatively if the column has a work order number then it wont show any values 🙂 

Let me know if this works? 

[MatthewEngel]

I think the syntex could look like this: 

Select([AssetID].[Assets Table],[Asset].[workorder_status]<>"Open")

Not sure if this would work, but worth a shot 🙂

[Omid_hrawy]

Thanks @MatthewEngel 

I tried your expression didn’t work.

if I select based on the asset table how I can include the condition on work order table.

[Omid_hrawy]

Thanks @MatthewEngel for the reply.

As the relation between Asset and Workorder is one to many an asset can have multiple work orders, I want to not allow users to open two work orders at same time.

what you have mentioned I tried, but now it shows only the assets which got closed work order.