Table key from table-to-table import error

Question

We create an equipment ID field in Table A.

The equipment ID is a formula field that creates a unique number:

List("",[ToText: Job ID],[ToText: Sort Order],[ToText: Record ID + 100M],"3")

The equipment ID field is imported from Table A to Table B via a table-to-table import run as part of an automation.

The equipment ID is the key field in Table B.

While the number displays correctly in Table A, when imported to Table B, the last digit of the number changes - but not consistently.

Example:

Table A - equipment ID: 12250001000179553

Table B - equipment ID: 12250001000179552 (i.e. last digit changed to "2")

This leads to attempted duplicate key fields.

When we try to edit the last digit of the equip ID number in Table B (i.e. try to correct "2" to "3") the edit is not accepted. The number continues to display as 12250001000179552.

It appears that this has only occurred as the number of digits in the equipment ID has incremented from 16 to 17.

quickbasecoachd · Answer

Yes this is totally expected.
Here are some notes to myself

A caveat is that QuickBase can only handle numbers up to 13 digits entered as numbers without rounding. If your digits will be larger than 13, you should use Text-type fields for data entry instead

So you need to have a formula which does not need any intermediate calculations or results which is more than a 13 digit number.

andreapahor · Answer

This makes sense.&nbsp;The&nbsp;equipment ID field in Table A was a text field - which is why the number displayed correctly.&nbsp;However, the equipment ID field in Table B must be a numeric field because we&nbsp;use the equipment ID in a summary field with a numeric function ("minimum"). We create variations of this summary field to bring multiple records from Table B into a single record in Table C.&nbsp;For example:&nbsp;Summary field 1: minimum equipment record IDSummary field 2: minimum equipment record ID that is greater than summary field 1Summary field 3: minimum equipment record ID that is greater than summary field 2Summary field 4: minimum equipment record ID that is greater than summary field 3etc. up to 100 . . .&nbsp;The summary field becomes the reference field in 100 relationships between Table B and Table C.&nbsp;This allows us to bring data from up to 100 records in Table B into a single record in Table C. Data must be in a single record in Table C so it can be exported to a digital form (for use in the field).&nbsp;Is there another way to bring data from many records into a single record? Or a function we could use on a text-type field to achieve the same result?13 digits is unfortunately not enough digits for the information we need to track. What do you mean by "without rounding"? Does that allow for decimal places?

quickbasecoachd · Answer

There is a new feature called combined text summary field which will summarize text fields.

Give it a try. Once you have it summarized up to the Parent, convert it to text with

ToText([my Combined Text Summary field])

iangreig · Answer

Thanks&nbsp;your&nbsp;help..&nbsp;Andrea is my colleagueWe don't have decimals in our numbers..&nbsp;so it seems like we can use up to 15 digits as it was working ok up to then. It seems anything larger Quick Base (like Excel) doesn't know what to with it. Would that be correct?

quickbasecoachd · Answer

My long time understanding is that Quick Base can handle up to 13 digits accurately. My suggestion is to convert to use text numbers as they will always be correct. The Combined text summary fields will solve your roll up issues. That is a new feature on the last few months.

Forum Discussion

Table key from table-to-table import error

Related Content

Moving Fields from One Table to Another

Merge Imports

How to relate two tables with the key to a third table?

Way to Duplicate Tables or Make A Template for a Table?

Copy Single Table from One App to Another