Imported data into linked tables

I create two tables in Bubble: Attorney and Case

In the attorney table, I created a “cases” field which is of type “Case” and I have the “This type is a list of elements” clicked.

Great, internally, when I add a case to a list based on an attorney,it automatically internally link the two. I don’t have to worry about AttorneyID and links and relationships.

But Now I want to import data from my old system into my Bubble Attorney table and my case table.

But how do I link the two? Since this data is imported, I need to tell Bubble which cases belong to each attorney. How do I do that programmatically? It would be great to do that while importing the data.

My current old tables uses the AttorneyID field to link the two, but I don’t want to use that anymore. I was trying to find a function to link the two initially based on AttorneyID and then delete the field on both tables.

With what I understood, first I would import all my Cases & Attorney in bubble without any link.
then for each Attorney I would do a search for all the cases linked to them based on criteria you imported yourself.
and with a button displayed for each Attorney in a repeating group I would link the Attorney with all its Attorney’s Cases (list).

Thanks @NicolasDap. That is what I want to do, but programmatically. In the attorney table alone there are over 500 attorneys. But that is not all, that was just one example. Then I have over 30,000 cases with multiple charges connected to each. Imagine having to do that manually.

That is what I am trying to find out: If there is a function to data bind/link/create relationship for each of the records based on some criteria.

For example, I do have an attorneyID from my previous database that links the attorneys to the cases.
I also Note: created a field of Type “Cases” in the Attorney table in Bubble set as type of “List of elements”

So I need a workflow that would do the following:

Search all cases where case.attorneyid = attorney.attorneyid then… “Link cases to Attorney’s List of Cases”

Currently I cannot find any action to do that.

Import CSV with a list of thing is now allowed.

Have you tried this ?
1 - Import all your Cases and Attorney in Bubble so Bubbe gives them unique ID to all the items
2 - Download the new CSV with their ID (make sure the ID is available, I’m not sure actually) and link them in a spreedsheet with their ID
3 - Import again in Bubble, with Attorney’s List of Cases’s Unique Id as a new field

I’ve never done that but it may work.

It works well, on the import you tell it what field you want to match in the “parent” and you then send in this data on the csv (so think in your case the case ids).

The reverse of this, i.e. having a list of Cases on the Attorney thing … doesn’t work like this. I have implemented it via a button on my admin page.

OK, @ahaller07 from AirDev helped me figure this one out.

The main issue was that I did not create a field of Type “Attorney” in the Case Object. Even though I am very familiar with one to many, many to one, peer to peer, etc… relationships in SQL database, I just didn’t think I had to do that in Bubble. I thought that was only necessary for the one to many! Should figure out that is why there is a selection box for that! Duh.

Anyway, in order to hopefully show others, I am going to lay out the steps of what to do in order to bring your databases into Bubble and link them properly. Just replace my Attorney and Case reference to whatever you are using.

Again, thanks Andrew!

For reference: I have two csv files to upload: Attorneys.csv and Cases.csv. Both have a field called AttorneyID which linked them in previous database. I have also created both objects Attorney and Case in Bubble database with all the necessary fields.

  1. Create the field relationships like mentioned above. In my example:

    Attorney object
    “Cases” field of Type “Case” (points to Case object). Click on “This type is a
    list of elements” checkbox. In other words, One Attorney can have many cases.

    Case Object
    “Attorney” field of Type “Attorney” (points to Attorney object).

  2. In the “App Data” tab in the “Data” section, click on “upload csv”

Upload the Attorney.csv first (my external Attorney.csv file that contains all attorney information) into the Attorney object in Bubble.

Repeat step for cases.csv (my external cases.csv file that contains all or the attorney cases) BUT DO THIS:

  Map the AttorneyID in the cases.csv to the Attorney reference in the Bubble Case 
  object. That will bring another field "Field to Match". Choose the AttorneyID in 
  the Attorney Bubble object.

NOTE: That will cause the Case bubble object to not have an AttorneyID! (You will only be able to map it to the reference field and not to the AttorneyID field in the Case Object). Either add another AttorneyID field in your csv or create a workflow to do that later.

You need the AttorneyID in the case object in order to complete the following step…

  1. So now, the cases know about their attorneys, but we still need to have the attorneys know about their cases!

        Create a new page with a button: "Populate List of Cases for all Attorneys"
        Create a new Workflow
        Add an action - Data - Make changes to a list of things
        Type: Attorney
        List to Change: Search for Attorneys  (Go through all attorneys in database)
        Click on "Add another value" button and add...
        Cases "add list" - search for cases where Case.AttorneyID = This AttorneyID.
        (basically: for each attorney found, add all cases to his list of cases if 
        the AttorneyID in the case object matches this AttorneyID.)
8 Likes

Whilst it is useful to have the attorney having a list of cases, it isn’t required as long as the case has the attorneyId, you can link them programmatically, But it is often very helpful to do it this way.

One word of caution… this iterating through lists of lists…which I did the same thing here…

(My List table is your Attorney, and Location is Case)

…has a few issues at scale. I had 1500 Locations and a few hundred Lists. It gave me a very weird error after a few minutes. May try to re-run and raise with support.

I’ve ran into a problem like this at scale as well. Have you found a solution?