Forum Discussion

ErikPye1's avatar
ErikPye1
Qrew Member
5 years ago

Customize Record ID

Hey All,

Is there any way to customize the record ID field?  I am combining two tables into a third and don't want the Record IDs to overwrite each other.

I want to keep the record ID so I can run automations.  This is for a calendar report, combining two different calendar tables into one.  

Set Up like this:
Table A: Staff Calendar
Table B: Committee Calendar (imported from another app).
Table C: Combined Calendar.

If someone adds a meeting in Staff Calendar it is automatically created in Combined Calendar.  

I want it to also update if someone adds or deletes something from Staff Calendar.

------------------------------
Erik Pye
------------------------------
Relationships
reports and charts
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    5 years ago
    Erik,

    I suggest that you have only one calendar.  Create a Calendar Type Table



    Initially you will only have two records in the Calendar Types table

    Committee
    Staff

    When someone adds a Calendar record, control the Related Calendar Types based upon their system role or something in an Employee table.

    You are now ready for next month when someone tells you that you need Faculty and Executive calendars as well.   Just add the records to the Calendar Types table and update your Calendar reports.





    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Eric, I suggest that you actually have three calendars.  The two original ones and then a combined one.  When you ask if you can have a Custom Record ID# field I think what you are really asking is if the Key field of a table can be other than the, and the answer is YES.  It can be be any non-formula field type.

    Let's say we create the Combined calendar Key field in the format in the format XXX-Record ID#

    So if the two calendars are called A and B. then the Key field will be written by the automation or saved table to table import to the record in the format A-123 or B-123.

    Then set up an Automation on each of the two calendars, respectively,  to add a record to the Combined calendar when a record is added to the table.  In each of the two Calendar Tables you will calculate the Key field of the combined table by formula as like "A-" & ToText([Record ID#]) . They will never duplicate as they will each have a unique prefix.

    One lazy approach to populating the combined table is to create a saved table to table import to copy across all the records from Table A into the combined table and merging on that Key field.  Calendar are typically super low volume transaction tables, and a saved table to table import runs super fast. 

    You can also set up an Automation to run that import each night just in case you day time import fails.  You will also want to set automation to delete.  A lazy way to do that is to create a relationship where one combined calendar has many calendar A's.  Then a summary field to count the calendar A's.  Then set up an automation to delete Combined records where they have no children, and run that when a record is deleted.   

    Then do that setup again for Table B.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • ErikPye1's avatar
      ErikPye1
      Qrew Member
      5 years ago
      Mark - So super helpful as always!

      I didn't even think of the formula w/ automation!  

      Follow-up question.  Will this affect the ability of the calendars to sync changes?  

      I.e. - If a change is made in "Calendar-A", will it sync to "Calendar-Combined"   My thought is that the Key fields would have to match for that to happen.  Or do i create an automation to change both key fields to "A-123"?   

      Calendar-A will be where employees put in there PTO, OOO statuses, so if they change a vacation, I want them to be able to do it (not me).  Calendar-B is controlled by the Board, so they don't get to change anything there, I will do it but still want it to sync.

      Does this make any sense?

      ------------------------------
      Erik Pye
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        Right, so this is not a Sync table as as Sync table can only have one source to mirror.
        So yes, this is all about using Automations to do the mirroring.

        You will have two relationships where the combines table is the Parent and the children will be each of the two separate calendars.  That will allow you to put in an overnight safety net to maintain the combined table, in case any of the day time live Automations fail. 


        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------