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

anithajeevarath · Answer

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

Forum Discussion

Flag Multiple Entries in Quickbase Table