Best practice to join two datasets

Need help Database
1

Hi,

I would like to pick your brain on how to setup two datasets.

I have one table MASTER with a unique identifier and more columns.
UNIQUE IDENTIFIER
CHARACTERISTICS #1
CHARACTERISTICS #2

Then I have another table SALES with the same identifier, and more characteristics.
IDENTIFIER, not unique this time
CHARACTERISTICS #3
CHARACTERISTICS #4

I’d like to link records in MASTER to records in SALES, displaying them together, maybe on the same line?
It is a relation one-to-many. The unique identifier can link to many records in sales (generally two or three max).

Should I add a field list of sales to datatype MASTER? Or should I keep them separate and match them in a repeating group and match the unique identifier?

Thanks in advance for your help!

2 Likes
2

I think you’ll find it easier for displaying data if you add a SALES field to your MASTER and make it a list field. So whenever a new SALES item is entered into your database, part of that workflow is also modifying the appropriate MASTER’s list field by adding the new SALES item. You’d use the IDENTIFIER to define the correct MASTER.

MASTER

  • unique ID
  • characteristics 1
  • characteristics 2
  • list of Sales (type sales, list)

Create new Item (Sales) > Make Changes to Master (Search for Masters: first item and have the following Constraint for that search… Unique Identifier = Result of Step 1’s Identifier) >Field to modify is List of Sales… List of Sales add Result of Step 1.

So then a repeating group that is a type sales can display all the sales for 1 master this way. “Master’s list of sales” - you’d just have to specify which Master in the same way as above when making a change to a specific master.

Gaby | Coaching Bubble

4 Likes
3

Hi Gaby!

Thank you so much for your reply. I am currently looking into it, it seems like a viable solution to me.
One more question though, what if I already have some record from a preexisting database. How would I go about linking them?

1 Like
4

Hey! From outside of Bubble? Could you export that data as a CSV to then upload into your Bubble database?

5

Gaby, thanks again!
Yes, the data is in the Bubble database already but from what I understand your solution would link the record when a new one is submitted. Can I mass link the old data?

6

Yes! You can use API workflows to do this. Go to your Settings > API tab and turn on POST calls. You’ll see a new API Workflows page available to you in your Page dropdown at the very bottom (upper left menu where you navigate to your pages).

Then, you create a new endpoint, where the workflow is the data change you want to make to a single Master entry. You will then go to your data tab and choose “bulk edit” to run the api workflow you created on all Masters entries.

For example, if your old data is a list of MASTERS and needs to be linked to new SALES, you create the workflow to make a change to a single MASTER entry. You’ll be able to define what constitutes an old Master and which Sales to link via the Identifier.

I can give you a more in-depth step-by-step when I’m back at a computer, but this is how you’d do this. You’ll be able to mass update all old data really quickly.

1 Like
7

Thank you, thank you again! This is really helpful.

One question, though. I am going through the association you explained in the first post, but I do not understand the benefit of linking the sales in a list of sales on a master, and then displaying a repeating group of them

As opposed to just displaying a repeating group, listing sales, with a criteria of IMEI = IMEI of the master