Filter expression for ref list in data entry ...

ux
(StudentHomes Plymouth) #1

Filter expression for ref list in data entry form

I am trying to filter the records displayed within a ref dropdown relative to an earlier entry in the same form. In summary my form is to log and assign actions required and the related categories to be selected in the following sequence:-

  1. Select a property (building) 2. Select a trade (e.g. carpenter, decorator, cleaner, etc) 3. Enter brief description of works required (simple long text field) 4. Assign the work to a company relative to the trade 5. Enter various statements about completion target date etc. all of which work fine.

Items 1-3 work fine but I am falling down when it comes to item 4. Basically I only want the dropdown for item 4 to show me the companies that have been assigned the corresponding trade selected under item 2 but have been unable to achieve this. My current tables and column names relevant to this are:-

(a) TRADES - contains 2 columns [TradesID] & [Trade] (b) CONTACTSTRADE - contains columns with [ContactTradesID], [TradeID],[Trade] & [TradeName] © ACTIONS (to which I am trying to add records) - contains columns [ActionsID], [PropertyID], [Property Name], [TradesID], [Trade], [ActionDescription], [ContactTradesID], [TradeName]

So, essentially I want the dropdown for item 4 to only show me the companies that can carryout the works for the trade selected as item 2. Would think something like [TradesID]=[ContactTradesID]. Problem is I cannot find a way of achieving this. Can anyone help to explain how to filter the list displayed and where to do this e.g. initial value filter, data slice or what?

(Suvrutt Gurjar) #2

Also please explore if the following sample app helps with dependent drop down concept.

appsheet.com - Dependent Dropdowns - How to create a dropdown menu with values that depend on earlier choices Dependent Dropdowns - How to create a dropdown menu with values that depend on earlier choices appsheet.com

(Suvrutt Gurjar) #3

Hi @StudentHomes_Plymout, You may wish to explore the concept of dependent drop downs to achieve the requirement you have mentioned.

help.appsheet.com - Dependent Dropdown Dependent Dropdown help.appsheet.com

(StudentHomes Plymouth) #4

Thank you Suvrutt I will take a look at the links provided…

#5

Hi, I’m not sure on your exact situation but it may be easier to just have one Trades table instead of two with all the trades info and then use the dependent dropdowns as Suvrutt suggested.

(StudentHomes Plymouth) #6

Sorry been silent on this for a while as having studied everything I still couldn’t manage to sort it out. Lynn, I agree two tables would be simpler but I couldn’t figure out a way to do that as the relationship between the tables needs to be:

Trades (SINGLE ENTRY) >> TradeContacts (SINGLE ENTRY) >> TradeContactAllocations (ONE TO MANY)

Anyway, finally sorted and should anyone else have the misfortune to stumble across this problem the formula that worked is (although the table names have also been changed from the original post):

SELECT(TradeContactAllocations[Tradename],[Trade]=[_THISROW].[Trade])

I’d love to take credit for resolving this one but despite several hours spent trying to crack it I had to seek assistance from Appsheet support. All credit goes to Aleksi who was able to point me in the right direction. Thank you Appsheet for great support and to Suvrutt & Lynn whose input gave me a nudge towards the solution!

(Carl Foster) #7

@StudentHomes_Plymout I’m trying to do something similar. Where did the formula go? In the valid_if box? Also, is the column you’re selecting a trade a ref type or just text?

(StudentHomes Plymouth) #8

Correct - put formula in valid_if

(StudentHomes Plymouth) #9
(Carl Foster) #10

Thank you, finally got it to work as well.

(StudentHomes Plymouth) #11

Your welcome, I know how frustrating it can be!