Forum Discussion

Qrew Cadet

5 years ago

Search for Records with Multiple Matching Fields

I have a set of raw data being imported from a spreadsheet that needs to be aggregated into an arbitrary set of groups. I would like to find any records with matches in certain fields (3-5 fields), an...

Data import and export

MarkShnier__You

Icon for Qrew Legend rank

Qrew Legend

If you include the building onto the concatenated string, why will you need to do this multiple times? Why not just import all your data into the details table and then make the formula for the concatenated string. If different buildings require different strings formulas, then build that logic into the string formula.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Qrew Cadet

5 years ago

Sorry, left out an important detail. The data will be imported at different times for each building. We need to do an inspection, process the data, and issue a report while still inspecting other buildings. So it's more of a workflow issue to not have to do that manual copy 70+ times.

------------------------------
Oana Toma
------------------------------

MarkShnier__You
Qrew Legend
5 years ago
Ya, I wish I had a solution to that, for my own selfish reasons too.
I do have a script that I could install to create the parent records, but you would need to engage with me for a good couple of hours or work to get that to work. The script would walk down a report of all buildings which do not yet have a parent and create it. It runs at thew rate of about 4 buildings per second, but you could then push a button and go on to other work in another window while the script runs and the progress bar moves across to completion.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
- OanaWhalen
  Qrew Cadet
  5 years ago
  Alright, thanks. When I get to that place I'll let you know if we need to go that route, or if I figure something else out that's close I'll post it back here!
  
  ------------------------------
  Oana Toma
  ------------------------------
  - OanaWhalen
    Qrew Cadet
    5 years ago
    I was able to resolve this challenge to avoid the manual copy process and wanted to post in case anyone runs across this in the future.
    
    As Mark suggested, the concatenation of fields to check for matches was the key here.
    
    I have a formula field called Group ID in my Raw Data table that concatenates a handful of fields.
    
    My Conditions table uses Group ID as its key field and I set up a relationship with Conditions as the parent and Raw Data as the child.
    
    In Conditions, I set up a checkbox formula field called Group Exists? with the simple formula of "true". This puts a checkbox on every record in the Conditions table.
    
    Whenever a Raw Data record is added, the formula calculates a Group ID. Since Group ID is the reference field to Conditions, I set up a lookup to the Conditions table to pull down the Group Exists? value. If the Conditions table contains a record with that Group ID, a true value is passed down. If that Group ID does not yet exist in Conditions, the checkbox value is false.
    
    I created an automation such that every time a Raw Data record is added and the Group Exists? checkbox is unchecked (false), a new record is added to Conditions with the Group ID key field automatically populated from the calculated value in Raw Data.
    
    Since Group ID is populated by formula for every Raw Data record, any record in that table with the same Group ID (and therefore matching fields) automatically becomes associated with the new Condition with that Group ID.
    
    ------------------------------
    Oana Toma
    ------------------------------