Importing Values to a Numeric Formula Field

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered
Any advise on how to import values from a csv file into a field with a formula or lookup? Any ideas on design change to make this work?
Photo of Steven Shaw

Steven Shaw

  • 180 Points 100 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Lookup fields and formula fields DO NOT lend themselves to be modified by any thing other than what it is intended by. For ex. a formula field is calculated by the formula. You can't modify its value. You can however, modify the fields that contribute to this formula field. Those are the source. Likewise, a lookup field comes from its parent table. If it has to be modified, you would change its value on that table. That is the source.

So the question is: what exactly do you want to accomplish here?
Photo of Steven Shaw

Steven Shaw

  • 180 Points 100 badge 2x thumb
I am trying to do an initial upload of my product catalog. For example, I need to look up in the HTS table to find the duty rate & HTS# of the product. I want to be able to import the values from a csv because I have over 500 styles. 
If you have a table of duty rates in a table called HTS, you would change the Key field of that table to be the HTS #.  But in fact if you are importing from more than one country you would need to create a Key field which is a concatenation of the HTS and the country.

such as

0901.21.0050-Canada

The HTS table has the duty rate.

The on your styles table you would create that same formula to result like

0901.21.0050-Canada

using a formula text field 

Then make a relationship based on that concatenated field and lookup your duty rate.


IMHO, I don't think you can make a formula field a primary key.
Right, 
There are two solutions.

The first is to have a formula to calculate the key field and then a form rule to copy that to the real Key field.

The second is that in fact you can have a formula field be a Key field by typing in the API.  I have found that works fine, except for importing such as imports off excel or table to table saved imports.  it one of those undocumented things that i learned at an EMPOWER one year in a Q & A session.
Oh Cool! What is that secretive way to have a formula field as a Primary Key? API? That would be interesting.
The easiest way is to disable app tokens and then just go to the Table home page and edit the URL like this

https://mycompany.quickbase.com/db/bnw7k1234?act=API_SetKeyField&fid=xx

where xx is the field that you want to be the Key.

Then run "enter" to run that API.  It should come back with an XML success message which includes the words  "no error".

I use this where is need a compound Key field composed of several fields concatenated together.
No problem Coach. I used this technique last year after I saw your post back then. I just forgot all about it. Thanks Coach!
Steven,

Do you have duty rates by country?   Do you get what i mean by the concatenated field?  and see my post above with using a form rule to enter the formula value into a field that will be the Key field to the HTS table.
Photo of Steven Shaw

Steven Shaw

  • 180 Points 100 badge 2x thumb
I wrote a form rule to copy the HTS info into another field, but I have to save the record twice for it to take effect? You ever hear of this? 
Check the form rule and scroll the page all the way to the bottom and uncheck that checkbox

Also, put the two fields in the bottom of the form so the form will “see” those fields.
Photo of Steven Shaw

Steven Shaw

  • 180 Points 100 badge 2x thumb
Did not work. Can I send you screenshots? Email?
I can help you slowly here or contact me via my website QuickBaseCoach.com for one on one assistance.