Discussions

Expand all | Collapse all

Importing Values to a Numeric Formula Field

  • 1.  Importing Values to a Numeric Formula Field

    Posted 10-29-2018 20:06
    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?


  • 2.  RE: Importing Values to a Numeric Formula Field

    Posted 10-29-2018 20:11
    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?


  • 3.  RE: Importing Values to a Numeric Formula Field

    Posted 10-29-2018 20:16
    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. 


  • 4.  RE: Importing Values to a Numeric Formula Field

    Posted 10-29-2018 20:56
    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.




  • 5.  RE: Importing Values to a Numeric Formula Field

    Posted 10-29-2018 20:58
    IMHO, I don't think you can make a formula field a primary key.


  • 6.  RE: Importing Values to a Numeric Formula Field

    Posted 10-29-2018 21:02
    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.


  • 7.  RE: Importing Values to a Numeric Formula Field

    Posted 10-29-2018 21:14
    Oh Cool! What is that secretive way to have a formula field as a Primary Key? API? That would be interesting.


  • 8.  RE: Importing Values to a Numeric Formula Field

    Posted 10-29-2018 22:04
    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.


  • 9.  RE: Importing Values to a Numeric Formula Field

    Posted 10-30-2018 14:55
    No problem Coach. I used this technique last year after I saw your post back then. I just forgot all about it. Thanks Coach!


  • 10.  RE: Importing Values to a Numeric Formula Field

    Posted 10-30-2018 16:15
    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.


  • 11.  RE: Importing Values to a Numeric Formula Field

    Posted 10-30-2018 17:11
    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? 


  • 12.  RE: Importing Values to a Numeric Formula Field

    Posted 10-30-2018 17:23
    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.


  • 13.  RE: Importing Values to a Numeric Formula Field

    Posted 10-30-2018 17:56
    Did not work. Can I send you screenshots? Email?


  • 14.  RE: Importing Values to a Numeric Formula Field

    Posted 10-30-2018 18:24
    I can help you slowly here or contact me via my website QuickBaseCoach.com for one on one assistance.