Help

Returns the result based on two conditions in lookup columns

Topic Labels: Formulas
Solved
Jump to Solution
4564 9
cancel
Showing results for 
Search instead for 
Did you mean: 
leandro_medeiro
6 - Interface Innovator
6 - Interface Innovator

Hello. I have a table that it is filled using a forms. there are two fields coming from two other tables that are selected by the customer.

I need a formula column where it analyzes the two filled fields and returns the result (remuneration) based on a third table (remuneration_table).

below I am attaching a photo to illustrate.

image

basically I need the formula to return the candidate’s remuneration based on the sector and position he will be hired.

Could someone help me?

1 Solution

Accepted Solutions

Click the blue plus button next to {SALARIO 2} to pull in the {SECTOR + FUNCAO} field

See Solution in Thread

9 Replies 9

A formula alone can’t do this. Here’s what I would try:

  • Set your formula to concatenate your {SECTOR} and {Attachments} fields together
{SETOR} & " " & {Attachments}
  • Ensure the primary field of the RENUMERATION_TABLE concatenates the {SECTOR} and {OFFICE} fields in the same pattern
  • Add a field that links the RECRUTAMENTO table to the RENUMERATION_TABLE
  • Copy the {RENUMERATION} formula field value into the link field (either manually or use an Automation to do it for you)
  • Return the {VALUE} field in the RECRUTAMENTO table using a Lookup field

Hi Kamille, thanks in advance for your support.
I didn’t quite understand how step 3 " * Copy the {RENUMERATION} formula field value into the link field (either manually or use an Automation to do it for you)"

could be more detailed for me?

Either:

  • Copy and paste the value from the formula field into the Link field
  • Use an Automation that copies the formula field into the Link field
    • Trigger: When RENUMERATION} field updates
    • Update Record Action: same record ID as trigger, add the Link field, insert the {RENUMERATION} field’s value by clicking the blue plus symbol at the right of the input box

I’m copying the result of the formula into the link column but it doesn’t return the data, a new row is generated in the REMUNERATION TABLE table. any tips ?

Did you do this^?

The purpose of copying the formula into the Link field is to connect to the appropriate record. IF its creating new records, that means the formula output a combination of {Sector} and {Office} that doesn’t exist in the RENUMERATION table yet.

I understood what happened, is that I wasn’t creating the formula in the primary field. I made the correction and now it worked. I will try to automate the process. I’ll talk to you already.

I’m lost in this second part of automation.
I’ve tried in several ways

image

Click the blue plus button next to {SALARIO 2} to pull in the {SECTOR + FUNCAO} field

It worked out! Thank you so much for your help and patience Kamille.