Compare and Match Data

[QIRO]

Hi guys,

Still getting used to Adalo so need a little help getting my head around data processing.

I have two databases. DB1 has NAMES and ID numbers inside. DB2 just has ID numbers.

I would like to get DB2 to query DB1 for ID numbers and pull the correct NAME.

SELECT([DB1 (NAME)], [DB2 (ID)] = [DB1 (ID)])
Select the corresponding name where the ID in DB2 matches DB1, or something along those lines.

[Flawless]

Hey there @QIRO

Can you explain in a little bit more detail what you’re trying to accomplish?

There is absolutely an easy way to do this, but I just need to understand the process so that I can understand what you’re doing in order to provide you with the proper solution.

[QIRO]

Hey, sure can!

So my first database contains lots of data points about multiple people. The data we’re interested in in this example is their NAME and their ID NUMBER.

The second database will be updated by the app’s user and contains anonymised data about those people, showing their ID NUMBER, and not their name.

My aim is to display in the app the data from the second database, but also reference the first database and add the person’s name to the screen.

For example:

Database 1
Name: Test Person
ID: 12345

Database 2
ID: 12345
Score: 100%

The app needs to recognise that ID NUMBER 12345 corresponds to ‘Test Person’ and display:

Name: Test Person (from database 1)
ID: 12345 (from database 2)
Score: 100% (from database 2)

I hope this helps!

Note: The reason this is two databases is that the reports are generated by a third party, so have to be anonymised for data protection. This is not the case once the data is added to the app, as it is to be an internal system.

[Flawless]

When you say “database” are you referring to collections or actual databases? Like, do you mean that the first collection in the database is the “Users” collection and the 2nd collection in the database is the “Reports” collection? Or is the 2nd “database” an external collection?

If they are both internal collections, you can simply add a custom filter on the list of “Database 2” where “Report > ID is equal to Logged in user > ID”

If the 2nd one is an external collection, you’ll need to look at the API reference for your external database to see how to filter by ID.

[QIRO]

My apologies, they are database collections, both internal to the app as I couldn’t find a simple way to integrate the external spreadsheets

The “Report > ID is equal to Logged in user > ID” isn’t referencing the Logged in user, unfortunately. It’s a team of people, only the managers have access to the data. It would need to be something along the lines of Report > ID is equal to Users > ID.

I can’t see how to make that connection though, I’m struggling to grapple with the relationships as they don’t seem to behave how I expect sometimes

[Flawless]

The solution:
After re-reading the initial thread (I’ve been reading a ton of them today, had to refresh my memory), I see that you want to use the transporter ID to filter the data. You can certainly do that instead and you wouldn’t necessarily need the relationship. I’d still recommend going with the relationship that I mentioned.

Grab ALL of the components within your list and “make a list” so that you have a list nested within a list.

This is how the layers should look:

Set this “List 2” to Drivers > All Drivers with a custom filter where Transporter ID is equal to Current Scorecard > Transporter ID and set the maximum number of items to 1.

Last, edit your name text component and switch that magic text to “Current Driver > Associate name”

& there it is:

Also, by doing it this way, when you link to another screen with one of the components, you send both the current scorecard and the current driver data to the next screen. For example: