Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
7 years ago

Importing to a table with relationships

I?m hoping this isn?t going to be a ?I missed it in the documentation? post, I did have a good search around before posting!  New to QB and I'm putting together a PM app, currently with 9 tables, the relationship between 4 of these is causing me a bit of head scratching so I'd like to see if what I'm looking for is feasible.

The key four tables and their relationships are:

customer --< programmes --< projects --< sites

      |_______________________v

So customers can have many programmes can have many projects can have many sites (and it's possible to skip the programme level and have customers with many projects).

I've tested an Excel import to the sites table with no problems, however during that import I can't (or I don't think I can) identify which sites belong to which project/programme/customer.  Since the data import could routinely be >200 plus sites, it's a bit inconvenient to manually go back to each record and tag the appropriate project/programme/customer information.

It's easy enough to add these columns to the Excel file (i.e. three columns at the start for customer/programme/project), but is a way to tag this information against each site record during import?

Ask about Quickbase functionality

4 Replies

Sort By
  • NoahMcBride's avatar
    NoahMcBride
    Qrew Cadet
    7 years ago
    If the SITES table is related to the PROJECTS table, and each PROJECT belongs to only one PROGRAM and only one CUSTOMER, than you should be able to related the SITE record to its parent PROJECT and then "Look-up" the Parent Programme and Project.

    Just make sure that the Project Primary Key appears in the Excel data that you want to import, and then when you import a SITE sheet, you import this Primary Key as the RELATED PROJECT.

    Hope this helps!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Mark,
    I suspect if there�s a problem in the design of the relationships in your application.

    The reason I say that is that I�m sure in fact one site as many projects going on at one time.

    So that means that you will have a list of sites for example a bunch of building addresses and they will have many project site assignments.

    So in fact what you were importing are not sites but project site assignments.

    In order to relate those records then you will need to know the value for related site and related project.

    So that means you either need to know the record ID of those two records when you do the import, or else the key field of those records will be have to be changed to something that you could know when advance when you did the import.

    For example some kind of site code that makes sense to you and some kind of project code that makes sense to you that the Excel sheet would hence know when you do the import.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    7 years ago

    Thanks to both, first of all apologies for the delay (I should not post a question before a holiday) and secondly, I think the answer here was a mix of RTFM and me not paying attention.

    I was able to get the import to do what I wanted (in this particular case import sites and allocate these sites to a customer and a project) using the RELATED CUSTOMER and RELATED PROJECT fields during import.  The RTFM moment came about because I wasn't paying attention to the fact that I needed to use the record ID numeric value assigned to the customer and project already created.

    A follow on question, if I changed the record ID field to the customer name and project name fields in those tables, am I able to use those fields to do the same import?  Simpler than looking up the record ID for the appropriate customer or project in order to be able to import.

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    If you change the Key field of those two tables to be the Customer name and the Project name, then yes, you will be able to do the import without needing work out the [Record ID#]'s in advance.

    Note that if you have a Key field set as the name and change the name even slightly, then the children will not longer be Attached to their Parent records.

    That is why in most cases it is better to stick with [Record ID#] or perhaps a Customer number as opposed to a name.  But if you need to be doing imports on an ongoing basis, there is there merit to changing the key field for those two tables.

    To change the key field you go to the field list and check the checkbox beside the name field and select set Key field at the top.  it will give you a couple of scary warnings, but will keep everything attached.  Be sure to check the data entry on the child records to be sure that you can still properly select the Parent record.  You may need to adjust the form slightly.

    You may want to consider making a form rule to make the customer name and the project name non editable, if, respectively, those two parent tables have any children.