Mar 22, 2019 09:32 PM
Trying to write a formula that will recognise a word value in one field then if one or more word values are meet in the second field then it returns a value in a third field. This needs to be able to meet several values in both the first and second fields and return a different value in the third field depending on the condition of the first and second fields.
Mar 23, 2019 04:43 AM
Can we discuss in details for this ?
Mar 23, 2019 02:34 PM
Yes please would love to be able to get this sorted if it is possible
Mar 24, 2019 03:24 PM
@Brian_Masters sounds like you could use SWITCH logic depending on how many options you have in the first or second field:
SWITCH(field1 & ':' & field2, 'field1word1:field2word1', 'field3option1', 'field1word1:field2word2', 'field3option2', 'field1word1:field2word3', 'field3option3', 'field1word2:field2word1', 'field3option4', 'field1word2:field2word2', 'field3option5', 'field1word2:field2word3', 'field3option6' )
This would work well if each combination led to a unique outcome. Otherwise, it’d work to use nested IF statements. Let me know if this project runs into any issues I’d be happy to help.
Mar 24, 2019 03:50 PM
There are 5 option in both 1 and 2 fields giving 25 possible outcomes, however some of the outcomes are the same for each combination option of field 1 and 2.
I will give it a go and see if it works. I have tried to us a nested IF statement however it would not return the values I selected. It might have been the way I set the formula up.
Thanks for the advice and I will let you know h ok w I go.
Mar 25, 2019 07:41 PM
Thank you for the information on the formula, it works well with what I was trying to accomplish.
P: 03 6705 7070
M: 0447359 335
Mar 25, 2019 10:46 PM
Hi @Brian_Masters - as an alternative you could also try using a reference table. The problem I find with complicated formulas is that they are easy to get wrong and difficult to debug. So, you could create a table that references the two choices and the result. In my example below, I’m choosing a client and a service to provide and the combination of these two determines who will do the work.
I’ve created the primary key of the table to be the combination of the client and service (using CONCATENATE).
I also have a projects table:
Here, the client/service field is a link field to the client service table. The Person field is a lookup on the same table, i.e. choosing “Apple - Development” automatically looks up Jim.
Mar 25, 2019 11:23 PM
Thank you Jonathan,
I used a formula for what I needed and it is working for me at the moment. My fields are in the middle of the table so a little more difficult to work with.
Appreciate your time and suggestion will keep this in mine for few other tables I’m working on.