Search for Records with Multiple Matching Fields

Question

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), and identify them as belonging to the same group. There will be several groups identified through this search, and I plan to have these become parent records with the original raw data as child records (though I'm open to other approaches) to allow me to view and summarize the data from non-matching fields of the records within each group. There are many options for values in the fields to be checked for matches, so I do not want to simply identify all the possible combinations and use a long if statement to assign to a group for obvious reasons.

Is there an efficient way to find all records with equal values in multiple fields, somehow flag them as belonging together, and automatically create a parent record for the group? Subsequently, I may need to move data between groups manually or split off some of the automatically grouped records as their own group, so I don't want to use formulas to define which parent the records belong to. Any thoughts would be appreciated!

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

oanawhalen · Answer

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.&nbsp;
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------------------------------

markshnier__you · Answer

You can create a formula field to concatenated the fields together, separated by, say, a hyphen.

Then use a summary report to summarize on that field and you will have a list if Unique Parent records needing to be created.

then use the More .. button to copy these to another table (which you will previously have set up) and copy them across.

You can then use an Automation to trigger when. A child Record is created with no Parent, to create the parent, and safety net that with an overnight Subscription report of orphans.

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

oanawhalen · Answer

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:

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).
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.&nbsp;
***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)
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.
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 · Answer

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 · Answer

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

Forum Discussion

Search for Records with Multiple Matching Fields

Related Content

Using Grid Edit to Edit Multiple Records at Once

Edit Multiple Records at Once with Grid Edit

Modify multiple rows within the same group using Single Record ?

Custom Record Titles

Managing record access for multiple data levels