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 r...
Forum Discussion
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
------------------------------
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
------------------------------
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
------------------------------
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
------------------------------
- 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
------------------------------
