Forum Discussion

mycompetitionti's avatar
mycompetitionti
Qrew Member
7 years ago

How to add multiple records to one table from fields in 2 tables, whenever either changes, that can only be joined by non key fields

i am having some difficulty figuring out how to do this. I would like to add multiple records to a table we will call table 3 , when a record is added or modified in a table we will call table 1 , where table 3 record needs fields from  both table 1 and a table we will call table 2 . The problem is table 1 and 2 can only be related   via a non key field.

table 1 - is an eventClassParticipant table
table 2 - is a CategoryClass association table, where a class can be associated with more than one category
table 3 - is a CategoryEventClassParticipant table. It is a child of eventClassParticipant. It may have multiple records for each eventClassParticipant , based on if the Class that the participant is in is associated with more than one category in CategoryClass


if we have these records in table1 eventClassParticipant:

eventid 1, classid 1, participantid 1
eventid 2, classid 1, participantid 2


and these in CategoryClass
categoryid 1, classid 1
categoryid 2, classid 1

then we want to get these records in CategoryEventClassParticipant
categoryid 1, classid 1, participantid 1
categoryid 2, classid 1, participantid 1
categoryid 1, classid 1, participantid 2
categoryid 2, classid 1, participantid 2

the reason i am trying to do this is that i need to report from the CategoryEventClassParticipant perspective

Because table 1 and 2 can not be related via a key field, I can not pull the category information into the detail records I need to report on.

Any ideas are welcome for accomplishing the ability to report on the data from the CategoryEventClassParticipant level.

thanks,

Kirsten

APIs and custom code
Data import and export
Formulas and functions
  • JasonJohnson's avatar
    JasonJohnson
    Qrew Assistant Captain
    7 years ago
    Table 1 and 2 can't be connected but can those tables be connected to Table 3. Using Automations to create the records would allow you to fill out the related field for both those tables in Table 3 thus bringing the data over.
    Make sure to watch the relationships webinar Mick noted and then watch one on Automations.
  • mycompetitionti's avatar
    mycompetitionti
    Qrew Member
    7 years ago
    Thanks for your responses.

    I will take a look at those webinars, although I do feel like I have a fairly good grasp of relationships in quickbase -  their strengths in terms of pulling lookup fields into the child, and their limitations.

    Particularly that you can only join on a single field that is designated as a key field - which must then be generated as a surrogate id.

    Unfortunately, there is no ability to define multiple natural keys as alternate composite keys and join tables via those as well.

    If that capability were added, a multitude of these types of design obstacles would be resolved!

    Just a trade-of of using quickbase for its great rapid UI/overall solution generation vs using a more "real" database that would requiring more coding, but gives far more flexibility in modeling the data.

    I have looked at automations and used them for some other simpler areas of smaller reference tables that are very slowly changing or added.

    The problem/ limitation with automations is that the triggering actions only work on the first record of the action in the triggering data set. For instance, if you are adding multiple records at once via an import, the "add" triggering action(s) will only be run on the first record that is added. This makes them work for UI type interactions , but not for tables where bulk operations may occur more frequently.

    I want to implement a clean solution that is at the core of the data modeling/management so it occurs each and every time the data is manipulated  - from any avenue of manipulation. Otherwise, there are bound to be maintenance issues that can consume many hours.

    I am a developer.  So, I am also researching the various quickbase API calls that can be applied. There are some good youtube tutorials I have found illustrating solutions using the API for similar (although not identical) design situations.

    MCP