Forum Discussion

OanaWhalen's avatar
OanaWhalen
Qrew Cadet
6 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
Relationships
OanaWhalen's avatar
OanaWhalen
Qrew Cadet
6 years ago
Interesting, I think the concatenation and summarizing of fields is the key I was missing here. Is there a way to automate creation of fields in a child table based on the values of a summary field in the parent table? I will be running this process dozens of times and I would love to avoid the manual copy to the other table.

Essentially what I am doing is collecting data for inspections at a bunch of buildings. Here's the process I'm envisioning:
  1. I will import our csv of raw data for a building into a Raw Data table and have that data be automatically assigned to that building (I've done that part before).
  2. Upon import of the csv file, formulas will automatically run to concatenate the match fields and be summarized into a single field in the Building (parent) record. 
  3. ***For each of the unique concatenated values, create a record in a Conditions (child) table under this Building (parent) table.**** (This is the part I'm not sure how to do)
  4. Automatically assign Raw Data records (child) to the appropriate Condition (parent) record based on the value in that concatenated field. I can probably use the concatenated value as a key field in the Condition table to make this assignment step happen automatically.
  5. Repeat for a different Building with it's own completely separate groupings. Even if the concatenated values match those of another building, they need to be separate Condition record. I think that part I can handle by also concatenating a Building ID value into the string.


------------------------------
Oana Toma
------------------------------
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
6 years ago
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
------------------------------
  • OanaWhalen's avatar
    OanaWhalen
    Qrew Cadet
    6 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's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 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's avatar
        OanaWhalen
        Qrew Cadet
        6 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
        ------------------------------