MarkShnier__You
4 years agoQrew Legend
Eliminating Duplicates with a Formula Query
I have been able to participate in the Beta for Formula Queries. Best I can figure not all of the features that were shown at EMPOWER are live in Beta, but these three functions are, which were provided by my Beta contact at Quickbase.
Currently Available Formula Query Functions
· GetRecord(record id)
o Returns a single record based on the Record ID provided
· GetRecords(query)
o Returns a set of records which match the query parameters
· GetCellValues(record(s), field id)
o Returns a list of values from the Field ID of the given records
During Beta there are a few safeguards in place protect your applications
The following limits will be reduced/removed as Formula Queries reach GA
· GetRecord(s) only work within the given table, no cross-table queries yet
· When trying to save a Formula you may receive this error:
'Uh-Oh! The way this formula is built will take too long to process. Please try refining your formula.'
o This often means that the table is too large. Try testing on a table with fewer records.
The API_DoQuery guide provides details on how to construct a query.
Try to follow these best practices when forming your query:
· Since filters are processed sequentially, your query should eliminate the most records with the first filters.
· Try to use scalar fields (manual input fields) rather than derived fields like summary or formula fields. Derived fields must do additional queries, permission checks, and calculation before they can be evaluated against your query.
· Wherever you can, use exact matches when building your filter criteria.
The goal I had was to eliminate duplicates from a table so as to be able to mindlessly copy the non duplicates to another table using a saved table to table import (click a URL formula button). As you are probably aware if there are duplicates in the import set for a unique target field then the import will fail. In my case I didn't really care which of the sibling duplicates I copied across so it was good enough for me to preserve (flag) the duplicate with the Minimum Record ID number and eliminate (not flag) the others.
In my case the unique field was called NPI
This formula here called [Record IDs for this NPI] returned a semi colon delimited list of all the [Record IDs] for the siblings of this NPI.
GetCellValues(
GetRecords("{73.EX." & [NPI] & "}"),3)
From the inside out that formula says query for fid 73 (which is the field for NPI) and go off and get the records from the table I'm sitting on where the [NPI] for the record I'm sitting on matches with the same value in any other record. Then bring back the Cell values from that Query for Field ID number 3. Of course field ID number three is the Record ID.
But then how to find the one with the Minimum Record ID# of all the duplicates?
Most conveniently in my use case, the records are returned in Record ID sequence!
As you can see from the screenshot, I am sitting on Record ID# 203432. That is the first one on the returned list, so its the Minimum Record ID# of its sibling NPIs.
Then I created this formula to flag which ones I can import.
ToText([Record ID#]) = [Record IDs for this NPI] // this is probably not necessary
or
ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#]
//The Formula Query returns a list of all of the Record IDs for this NPI. The first one on the list (at the left) will be the minimum.
I thought I would just post this because it is has been classical problem in QuickBase for the past 20 years of how to filter a table report or a saved table to table import to only have unique records without needing have a "helper" Parent table, typically maintained by Automations, of the unique Parents.
I have posted a screen show of the results of the Formula Query.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Currently Available Formula Query Functions
· GetRecord(record id)
o Returns a single record based on the Record ID provided
· GetRecords(query)
o Returns a set of records which match the query parameters
· GetCellValues(record(s), field id)
o Returns a list of values from the Field ID of the given records
During Beta there are a few safeguards in place protect your applications
The following limits will be reduced/removed as Formula Queries reach GA
· GetRecord(s) only work within the given table, no cross-table queries yet
· When trying to save a Formula you may receive this error:
'Uh-Oh! The way this formula is built will take too long to process. Please try refining your formula.'
o This often means that the table is too large. Try testing on a table with fewer records.
The API_DoQuery guide provides details on how to construct a query.
Try to follow these best practices when forming your query:
· Since filters are processed sequentially, your query should eliminate the most records with the first filters.
· Try to use scalar fields (manual input fields) rather than derived fields like summary or formula fields. Derived fields must do additional queries, permission checks, and calculation before they can be evaluated against your query.
· Wherever you can, use exact matches when building your filter criteria.
The goal I had was to eliminate duplicates from a table so as to be able to mindlessly copy the non duplicates to another table using a saved table to table import (click a URL formula button). As you are probably aware if there are duplicates in the import set for a unique target field then the import will fail. In my case I didn't really care which of the sibling duplicates I copied across so it was good enough for me to preserve (flag) the duplicate with the Minimum Record ID number and eliminate (not flag) the others.
In my case the unique field was called NPI
This formula here called [Record IDs for this NPI] returned a semi colon delimited list of all the [Record IDs] for the siblings of this NPI.
GetCellValues(
GetRecords("{73.EX." & [NPI] & "}"),3)
From the inside out that formula says query for fid 73 (which is the field for NPI) and go off and get the records from the table I'm sitting on where the [NPI] for the record I'm sitting on matches with the same value in any other record. Then bring back the Cell values from that Query for Field ID number 3. Of course field ID number three is the Record ID.
But then how to find the one with the Minimum Record ID# of all the duplicates?
Most conveniently in my use case, the records are returned in Record ID sequence!
As you can see from the screenshot, I am sitting on Record ID# 203432. That is the first one on the returned list, so its the Minimum Record ID# of its sibling NPIs.
Then I created this formula to flag which ones I can import.
ToText([Record ID#]) = [Record IDs for this NPI] // this is probably not necessary
or
ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#]
//The Formula Query returns a list of all of the Record IDs for this NPI. The first one on the list (at the left) will be the minimum.
I thought I would just post this because it is has been classical problem in QuickBase for the past 20 years of how to filter a table report or a saved table to table import to only have unique records without needing have a "helper" Parent table, typically maintained by Automations, of the unique Parents.
I have posted a screen show of the results of the Formula Query.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------