Joining two lists (one from API request, one from Things)

[SeanB]

Hi,

Would be great if someone could help me with this. Basically, I’m trying to join two lists, one of which is the result of an API request.

List 1: Eventbrite Orders (API Request)

• Name
• Email
• Order ID

List 2: Members (Bubble data)

• Name
• Email
• Employer
• Position
• …several other fields

What I’m trying to do is export a CSV using the 1T - CSV creator plugin. I’m using the Create a Dynamic CSV File action and I’m using the the following setup

The issue is that for my fourth column (the first three are the same API request in the same order), instead of the API data, I want to do a search of my Member data type in Bubble and match the email of the member to the corresponding API order’s email and if it’s a match, get the corresponding “Employer” from my Bubble data field. I guess I’m thinking of this as a join. I’m having trouble figuring out the expression for this.

When I set it up as I did above, I no matches for Employer (from my Bubble database) that match the corresponding order’s email even though the Employer exists in my table for those emails. The Employer column in the CSV export is blank (for all rows).

However, when I set it up this way…

I do get a match of the Member whose email matches the list of the API request and the Employer is displayed – BUT, this happens only for the first match. That is, the first API’s order’s email is found in Bubble, and the corresponding Employer is shown in my CSV export – BUT only for the first row. For all other rows in the CSV, the Employer column is blank (I can confirm that the Bubble database has Employers for each of those emails).

Sorry if this is convoluted, but could really use any help with this as I’ve been struggling to make this work for quite some time now.

Thank you.

[mishav]

Hi @SeanB

It’s a little tricky for sure Here’s one approach, the logic is:

Start with the list of emails from the API
:format as text ==> 
    Delimiter ,
    Search for Members ==>
        Constraint: email = This text
    :first item's Employer's Name
:split by ,

It does a search for every API request email, but hopefully they’re cached and hopefully WU cost isn’t too bad.

Here’s an example editor page, with Books and Categories, the API result being represented by an Arbitrary list of Book Titles. (Toolbox not required for this example.)

Edit - a different delimiter than comma may be required if Employer names can contain commas.

[tylerboodman]

@mishav

a different delimiter than comma may be required

I do the emoji, no way that will conflict with anything

[SeanB]

OMG! Thank you so much @mishav I don’t think I would have ever figured out this expression even if I brute forced random combinations. This gave me exactly what I needed. Thanks a ton once again.

[SeanB]

Thank you. Good idea. Have used this emoji as my delimiter and split by too.