Flag Multiple Entries in Quickbase Table

Question

I have a Quickbase table with 3 fields Customer Id, order Id, Book Id. I need to add a Formula field to flag records if the Book Id exists in the table with a multiple combination of Customer ID and Order Id. How Can i Achieve this?
------------------------------Anitha Jeevarathinam------------------------------

markshnier__you · Answer

This should flag is there are duplicates for the same OrderID and BookID

var text QUERY =&nbsp;&nbsp; "{7.EX." &amp; [OrderID] &amp; "}"&amp; " AND "&nbsp;&amp; "{9.EX.'" &amp; [BookID] &amp; "'}";
IF(SizeGetRecords($QUERY)) &gt;1, "Y")

But the formula is not looking at the Customer ID field.
&nbsp;------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

markshnier__you · Answer

No, I don't have a solution using a formula query.&nbsp; To execute that query it needs to compare every record to every other record which is like 300,000 times 300,000 = 9,000,000,000,000 comparisons.&nbsp; I don't even know the name for a number that big.&nbsp; It might be a trillion.

The native solution to this would be to set up a pipeline to detect when new child records are being added and the combination of those two fields concatenated together does not exist.
So to do that you create a parent table of those two fields concatenated together with that as the key field. Then a relationship back down to your child records. Then you can have a regular summary field in that relationship to detect if they are duplicates and then look that up back down to the children.&nbsp;&nbsp;
It will initially populate that parent table by creating a summary report on this trial table and exporting it to the parent table. After that the pipeline would just trigger whenever a child is added and the parent does not exist.&nbsp;&nbsp;

------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

adamkrzyzanek · Answer

Did you try to re-arrange "searched" fields in query?So it first go with field 9 and only later with 7?Maybe you can add some additional parameters which will allow this to calculate. I was doing this workarounds to my formula queries fields :)&nbsp;------------------------------Adam Krzyzanek------------------------------

markshnier__you · Answer

What are the field ID#s for those two fields, respectively?
------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

anithaj · Answer

Customer Id (FId - 6), Order Id (FId- 7), Book Id (FId - 9)------------------------------Anitha Jeevarathinam------------------------------

Forum Discussion

Flag Multiple Entries in Quickbase Table

10 Replies

Related Content

Quickbase Resources

Allowing Duplicate Entries

Search for Records with no new entries?

Are You an Accidental Quickbase Admin?

Getting Familiar with Quickbase Navigation