Discussions

 View Only
Expand all | Collapse all

Eliminating Duplicates with a Formula Query

  • 1.  Eliminating Duplicates with a Formula Query

    Posted 08-27-2021 17:20
      |   view attached
    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
    ------------------------------


  • 2.  RE: Eliminating Duplicates with a Formula Query

    Posted 08-28-2021 07:28
    Thanks for the real world news.  This is going to be very powerful and greatly complicate development.  :) 
    I asked to join the Beta but alas drew the short straw.

    ------------------------------
    Don Larson
    ------------------------------



  • 3.  RE: Eliminating Duplicates with a Formula Query

    Posted 09-11-2021 08:39
    Mark,

    I got into the Beta and spent three hours trying to do a cross table query.  Only when I revisited your post did I see that it was not permitted.

    Is there a list somewhere else of all the Beta limits?  Foolishly trying to do prohibited tasks is not very wise.

    ------------------------------
    Don Larson
    ------------------------------



  • 4.  RE: Eliminating Duplicates with a Formula Query

    Posted 09-11-2021 08:53
    What I posted is the complete documentation.  However, I hear they plan to go to an Open Beta in the September release.  That has way more features than the current Beta, but I don't think it supports Cross App queries.  We will have to wait and see if they are confident enough in the performance to let it out into the Wild.

    I did have an experience in looking for duplicates in a 300,000 record set and it locked up the app.




    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Eliminating Duplicates with a Formula Query

    Posted 09-29-2021 13:17
    Mark - thanks for this awesome post - exactly the info I was looking for.

    One question though: what is the field type for your formula field: GetCellValues(GetRecords("{73.EX." & [NPI] & "}"),3) 

    I have an App that is Beta Approved, and I was expecting to see a new field type that would be "Formula Query" or something like that, but I am only seeing the standard options.  Thanks for any guidance!


    ------------------------------
    Mike Gerrity
    ------------------------------



  • 6.  RE: Eliminating Duplicates with a Formula Query

    Posted 09-29-2021 13:28


    ------------------------------
    Mike Gerrity
    ------------------------------



  • 7.  RE: Eliminating Duplicates with a Formula Query

    Posted 09-29-2021 13:33
    Mike, are you a part of the beta for formula queries? If not, try and get access to that. Check the most recent QB release notes for how to do it.


  • 8.  RE: Eliminating Duplicates with a Formula Query

    Posted 09-29-2021 14:13
    Yes - I am part of the Beta and the app I am trying to use is authorized.  Should I be seeing a new type of Formula field available? Or am I just using a Formula Text or formula Rich Text field and now have new "formula functions" available?   I have tried writing Mark's formula as both a Formula Text and Formula Multi-Select Text field and neither seems to work for me.  Not sure if I need to do something further to make sure my app is fully set up for the beta?

    ------------------------------
    Mike Gerrity
    ------------------------------



  • 9.  RE: Eliminating Duplicates with a Formula Query

    Posted 09-29-2021 14:54
    There is not a new type of formula field, we use the regular kind.  It should be a formula multi select field type.

    https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=212&rl=iu

    https://login.quickbase.com/db/6ewwzuuj?a=td and search on queries

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 10.  RE: Eliminating Duplicates with a Formula Query

    Posted 09-29-2021 15:48
    Thanks Mark - I got it to work.  Apparently the Query did not like the way my MRN was formatted - likely because of the "." character: sample MRN  "1297568.4.3"

    But when I updated to do the same query based on Patient Name it worked like a charm.




    ------------------------------
    Mike Gerrity
    ------------------------------