I'm trying to merge information from two separate tables in different applications. Each table has a key field, but can I merge on a different lookup value?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm trying to merge information from two separate tables in different applications.  the locations table in the first application (App1) has a list of students identified by student ID with the classroom, program, and grade band each student is projected into (3 source fields).  The key field for the locations table (source table) must be record id for various other relationships within App1. App2 has a table called student demographics (master table) that also has the key field as record ID which also must remain.  We want to pull in information from the 3 source fields from the Locations table into the student demographics table.  Both tables have another unique field- Student ID.  If I were in excel I would set Student ID as the lookup value.  However, when i go to set up the merge, I am only given the option to merge based on the record ID (key field) and thus cannot merge based on Student ID.  How do I merge on a unique field in both tables that isn't the key field?  We are hoping to have the information populate automatically and will set it to import everyday or every week.  thanks so much!

Photo of Amanda

Amanda

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Are you sure that changing the Key field of the student demographics table to be the Key field is not possible and if you think not, can you explain why?  I can think of some solutions if making the change might be possible. Of course, such a change would need to planned out or tested first.


But, OK, so let's say that you have a wonderful reason the student demographics table can't have its key field change. But the Student ID is unique.

Plan B could be to set up a aquickBase Synch to run every hour to merge records from the student demo table to a new table where the student ID is the key field.

Then we have more options, but before I go further with ideas, can you explain about that locations table. I suspect the Student ID is not unique, as a Student may be in many classes?  If so, how will you decide which locations record data to merge in if the student ID is not unique in that Table?
Photo of Amanda

Amanda

  • 0 Points
Thanks so much for your speedy response!  Full disclosure- I'm quite new to quickbase and have learned through googling so there are some areas that I'm still pretty new at. Additionally, I didn't actually create the Locations Table, but the creator assured me that changing the key field would break some of the relationships previously established. From the brief explanation I got, the student location tables must remain having the record ID as the key field because they actually feed into other tables where one student may have multiple records.  Thus they need the Record ID as the unique identifier rather than student ID. Additionally, to answer your question about the "classes" for students in the location table.  Each student is assigned only one location classroom, it essentially is a homeroom program, gradeband, and school.  Multiple students may have the same homeroom program, gradeband or school, but each student is connected to one homeroom program, gradeband or school.  Each student appears only once in the table, and therefore their ID number can be considered as unique.
Thanks for the idea of plan b though.  I played around with setting up a different table in my master application to receive the location information from the other application, then will merge that table to the student demo table.  Although that means one more table (and I was hoping to be more efficient on this one), I think that's the only way to go forward.  Thanks for all your help though!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
My suggestion is to make a "Connected table" so that it will synch automatically. I suggest that you try that too.
Photo of Amanda

Amanda

  • 0 Points
Will do.  Thanks!