Want to check if a specific value exists in the lookup records data

Ask for Help Formulas
#1

Hi,

I have three tables with reference lists. With your help I was able to pull lookuprecords with contain formula. However, now I want to check if the any of the lookuprecords has a specific value.

I have created a public doc; here is the link http://139.180.223.0:8484/o/docs/vpnkxDdUdUFs/Untitled-document

In the status column of the Campaign Requisiton Form table, I want to check if any of the returning data is “Campaign Running”. If any of the data contains Campaign Running, I want the status to retunr “Active”, if not then “Finished”.

Thanks for your help!

#2

Hi Tareq!

Here’s the formula you’ll want:

campaign_status = Campaigns.lookupRecords(Requisition_Form = CONTAINS( $id)).Campaign_Status
if "Campaign Running" in campaign_status:
  return "Active"
else:
  return "Finished"
1 Like
#3

You can also check for running campaigns directly in lookupRecords:

running_campaigns = Campaigns.lookupRecords(
    Requisition_Form=CONTAINS($id),
    Campaign_Status="Campaign Running",
)
if running_campaigns:
  return "Active"
else:
  return "Finished"

You can also replace the if/else both here and in the suggestion above with IF like in another of your formulas:

IF(running_campaigns, "Active", "Finished")

Alternatively you could change the Campaign_Status column to Is_Campaign_Running, change the formula from:

IF(Camp_Details.lookupOne(Campaign_ID=$id),"Campaign Finished","Campaign Running")

to just

Camp_Details.lookupOne(Campaign_ID=$id)

and change the column type to Toggle so it contains True or False. Then in the original formula you could replace Campaign_Status="Campaign Running" with Is_Campaign_Running=True. Not a big difference, but it means things won’t silently break if you make a typo or change the label in the future. You could still have a separate Campaign_Status column for human friendly display with the formula:

IF($Is_Campaign_Running, "Campaign Finished", "Campaign Running")
1 Like