Forum Discussion

OwenMorgan's avatar
OwenMorgan
Qrew Member
6 years ago

formula to link imported records previously unlinked

We are importing data from an old database and there was an awkward link between ITEMS and EVENTS that we want to maintain and improve on the new system. 

An ITEM would be linked to an EVENT (one event may have many items - this relationship is defined in the new system with a link to a [RELATED EVENT]  (numeric). Each event will have an [EVENT ID], [FILING CODE], [EVENT DATE]

However on the old system while each event had  [EVENT ID], [FILING CODE], [DATE], the relationship was through the [FILING CODE] not [EVENT ID]

So each [ITEM ID] is connected to a [FILING CODE] and if there is an event the date will be stamped in the [FILING LOCATION] field. 

I want to create a formula in my pre-import table that looks at the ITEM [FILING CODE] and [FILING LOCATION] and if the [FILING LOCATION] references a date to stamp the relevant [EVENT ID] in the [RELATED EVENT] field

So

Table: ITEM
Field: Related Event Import
Formula:
IF ITEM[FILING CODE] (is not blank), check EVENT[FILING LOCATION] for EVENT[EVENT ID]
where EVENTS[FILING CODE] & EVENTS[DATE] = ITEM[FILING CODE] & ITEM[FILING LOCATION]
and display [EVENT ID]

I've tried a few things but struggling with syntax and cross app references

Thanks in anticipation

------------------------------
Owen Morgan
------------------------------
Data import and export
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
     If this is a brand new app which you are populating for the first time you might consider this strategy. Set your key field on the events table to be the filing code and import your data for both tables. It should all link up.
    Then change the key field to the record ID of the event and the system will keep the children all attached. 

    The alternative is to import your events first, and then export them again but this time include the record ID. Then using VLOOKUP formulas in Excel   To calculate the correct record ID of the event, import the children items. 


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
  • EricMohlman's avatar
    EricMohlman
    Quickbase Staff
    6 years ago
    Mark is correct that I'd accomplish this using Excel's vlookup formula. For more information please see this Quick Base help article: https://help.quickbase.com/user-assistance/import_from_excel_existing_app.html and this tutorial page from Microsoft Office: https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

    With those in mind, here's the basic steps I follow whenever I do something similar:
    1. Import your parent table data
    2. Create a temporary report on your parent table that contains your matching field and Quick Base's Record ID# field. Make sure it's sorting alphabetically on your matching field column.
    3. Export this report to a spreadsheet
    4. Within the spreadsheet containing your child table data, insert three blank columns at the far left, then paste the data from your export into columns A and B
    5. Situate the Vlookup formula within column C
      1. Example formula would look like this: =VLOOKUP(E2,$A$2:$B$1234,2,false)
    6. Populate this same formula across the reset of Column C
    7. Import your child data, including Column C to link to the correct parent record


    ------------------------------
    Eric Mohlman
    ------------------------------