Returns the result based on two conditions in lookup columns

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.

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 Like

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
1 Like

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

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

1 Like

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.