Forum Discussion

GrahamDavies's avatar
GrahamDavies
Qrew Member
5 years ago

Link to a QB record using a unique field not the Record ID?

Is it possible to create a link to a QB record using a field other than the Record ID?

I am trying to connect a different system to QB by using a unique email address for a record in QB. Ideally something very simple so that record could even ben linked from Excel without the RID:

https://target_domain/db/target_dbid?a=dr&dfid=10&query={'30'.EX.'user%40domain'}
Where field 30 is a Unique Email Address field.

When I try API_GetRecordAsHTML it requires the Record ID (Missing "rid" parameter), which I don't have in the other system.
I can use API_DoQuery with the query - but that returns XML not a link to a standard HTML form report.

Thanks!

------------------------------
Graham Davies
------------------------------
APIs and custom code
  • AustinK's avatar
    AustinK
    Qrew Commander
    5 years ago
    https://help.quickbase.com/api-guide/getrecordashtml.html

    As that says if your table uses something other than record id as the key field then you are free to use that. However, you must set your table to use the email as your key field for this to work in the way you are requesting. Since this is grabbing an entire record it must go by whatever your key field is.

    Is it possible for you to change the key field of that table, or will that cause you other issues? Are you 100% sure there will never be another duplicate email? Hypothetically what happens in your company(or wherever you are getting the emails from) when a user leaves? Say John.Smith@company.com is the email, when John leaves and another John Smith is hired later on, do they recycle the email? Or create a brand new one with say JohnW.Smith@company.com where they insert a middle initial?
    • GrahamDavies's avatar
      GrahamDavies
      Qrew Member
      5 years ago
      Thanks Austin

      Unfortunately I can't change the key to be the email. I have lots of records with no email.

      The other system will need to be kept in sync with QB so the email should be available and unique.

      I had hoped that because you can import on Email (as it is unique) that I could also link to a record with it.

      Any other options or do I need to resort to a full integration with the other system to pull in the Record ID and then use that in the link?

      Thanks

      ------------------------------
      Graham Davies
      ------------------------------
      • BlakeHarrison's avatar
        BlakeHarrison
        Qrew Captain
        5 years ago
        Unfortunately, you'll probably have to at least use a connector service to search by the email and update your system with the Record ID#. Once you have that, you're right, it's just a matter of adding it to the end of the URL.

        If you can accept a webhook, you could always setup a webhook to push the Record ID# anytime an email address is added or edited.

        ------------------------------
        Blake Harrison
        bharrison@datablender.io
        DataBlender - Quick Base Solution Provider
        Atlanta GA
        404.800.1702 / http://datablender.io/
        ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    5 years ago
    Graham,

    How much control do you have over system #2?     You can extract the table in QB on a regular schedule and put it into system #2 or even a third standalone DB​​.  Then system #2 can execute a SQL Query to retrieve the record from there.  Depending on your business case, running an extract once a day or less might be sufficient for your new data warehouse to serve up answers to system #2.



    ​

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------