*valid if pulldown list

[Qshafeek]

How to make values that share a ref id dissapear from a "valid if" list as they are added to a table.

I currently use [column]-[column] to make the ref relayed values dissapear as new rows are added to the table, but I want a new set of otherly ref entries to ignore the previous ref entries in the table & the *value if list to show all values again.

[WhoTripathi]

@Qshafeek 

Use the following expression syntax in the "valid if" or "suggested value" field :

Parent_Table[key Column]- This_Child_Tablet[ref column]

[Qshafeek]

I think I understand you, but I want to be able to do it multiple times in the same table wherever the new entries share a value of a different column so that the list "Refresh" for new engagements..

Example. Tables: building, rooms, work orders, Inspections

Where:

Rooms have building Id's & List of rooms

Work orders have building id's- Selecting building

Inspections have Work order Id's- Rooms are selected from the list forced by "Valid if": Controlled by: [Room ID]-Inspections[Room ID].

The problem is you can only do it once. I want to do it again when the new rows have different work order ID's.

[AleksiAlkio]

Is my assumption correct.. you have a Ref column and you want to filter the dropdown based on the already selected values? It needs to show all rows from the parent table minus already selected, right?

[Qshafeek]

Right, But only for the selected values of that particular ref id.

Example. Tables: building, rooms, work orders, Inspections

Where:

Rooms have building Id's & List of rooms

Work orders have building id's- Selecting building

Inspections have Work order Id's- Rooms are selected from the list forced by "Valid if": Controlled by: [Room ID]-Inspections[Room ID].

The problem is you can only do it once. I want to do it again when the new rows have different work order ID's.

[Steve]

Your problem description is horrible. Stop using jargon and describe what you want to accomplish rather than how you think it should be implemented. No AppSheet terms. Describe the goal as you would to your grandmother.

[Qshafeek]

The goal is to make the list come back

[Steve]

Not helpful.

[Qshafeek]

Thanks, Grandma.

[Steve]

Good luck, then, You came to us. Try coming correct.

[Qshafeek]

Several people have given very good feedback. You know you're the only person that responded, "I dOnT GeT It, AnD ItS AlL YoUr FaUlT..."

[Qshafeek]

Solution:

To achieve this, you need to:

1. Filter the dropdown list of rooms by excluding already selected rooms for the same work order.
2. Ensure the dropdown resets or re-filters for new rows with different work orders, allowing room selections specific to each work order.

You can do this by adjusting the Valid_if formula in the [Room ID] column in your "Inspections" table.

Steps:

1. Identify the parent Work Order in the Inspection row:

   • You need to reference the current Work Order ID to ensure you're filtering room selections based on that specific work order.

2. Use SELECT to filter available rooms:

   • You will create a Valid_if expression that selects all rooms for the current building, but excludes rooms already assigned to inspections for that specific work order.

Valid_if Expression for [Room ID]:

appsheet

CopyEdit

SELECT( Rooms[Room ID], AND( [Building ID] = [_THISROW].[Work Order].[Building ID], NOT(IN([Room ID], SELECT(Inspections[Room ID], [Work Order ID] = [_THISROW].[Work Order ID]))) ) )

Explanation:

• Rooms[Room ID]: Refers to the list of all rooms in the "Rooms" table.
• [Building ID] = [_THISROW].[Work Order].[Building ID]: This ensures that the rooms displayed are filtered to only those within the building related to the selected work order.
• NOT(IN([Room ID], SELECT(Inspections[Room ID], [Work Order ID] = [_THISROW].[Work Order ID]))): This part ensures that the dropdown excludes any rooms that have already been assigned to an inspection for the same work order. The inner SELECT finds all rooms already used in inspections for the current work order.

How It Works:

• When you add a new inspection and select a work order, the Valid_if will filter the rooms dropdown to show only rooms that haven’t yet been used for that specific work order.
• If a new row is added with a different work order, the expression resets, and the dropdown list will be based on that new work order, filtering out any rooms that have already been assigned to that new work order.

Additional Notes:

• Ensure that your "Inspections" table has a proper reference relationship with both the "Rooms" and "Work Orders" tables.
• The [Work Order].[Building ID] reference must be set up correctly so that you can access the building from the selected work order in the [_THISROW] context.

This setup should allow you to re-use the logic for new rows with different work order IDs while maintaining the room filtering for each work order individually.