Display the desired field

[icos]

Hi.
Why is the id displayed when data is plugged in, when a completely different field is selected?

[Joyce]

Hi @icos

It looks like your properties table is referencing the type table in Supabase. In WeWeb, your Properties collection is not fetching the type name but only the type id.

But you have the type names in the Type collection, so when binding info on an item from the Properties collection, you could use the lookup formula to look for the id of the type in the Type collection.

In the example below, I had a list of vehicles from a vehicles table with a reference to a location_id (2) from another table and used the lookup formula to get the name of that location from my locations collection:

Does that help?

[icos]

Thanks @Joyce everything worked, but why is it so complicated?
In Supabase I have a column Region and id is displayed there, but in WeWeb it is transferred all right. With others I have to dance with tambourine).

[Joyce]

Hi @icos ,

I think you are confusing two things in your screenshot:

1. the region name is coming from your Region collection,
2. the region value you highlighted in red is in an item in another collection

So it’s not any different to the issue we discussed above with the Properties and Type collections.

When working with related fields in a relational database, you have several approaches (I’ll explain with WeWeb and Supabase as an example but it’s not specific to these tools):

Option 1 - create a view in Supabase that has all the information you need, i.e. the values themselves, not the references. Then you can create a collection in WeWeb with the data from that view. The downside is you can’t use the realtime functionality of the table anymore as discussed in this topic.

Option 2 – use the advanced mode when creating a collection to get the names of reference fields in other tables. This works well but, depending on your use case, may be limiting. For example if you need to filter through the related field, as discussed in the same topic mentioned above.

Option 3 - create a collection for each table in WeWeb and use the lookup formula to display the value from a reference field.

Option 3 is my preferred solution for your use case because:

• you already have those collections in your project
• you don’t have to load the same data twice

All three approaches are valid in terms of web development but option 3 will most likely scale better.

Does that clarify things a little bit?

No worries if not, I’ll try to record a video tutorial explaining a bit better how things work and why.

[icos]

Yes, I realized my mistake.
I need to find something out and check it out.
Thank you!

[icos]

Greetings @Joyce
Something is not working with your formula).
I use it for other columns, but in each case different problems

What could be the error?

[luka]

You shouldn’t change the first parameter of lookup, it should remain the unique identifier of that table. In the previous example that Joyce shown, for every field the first parameter in lookup would remain ‘item.data.location.id’ as you use that to find the correct record in other table.

[icos]

Here are three columns, in the columns Region and Rooms I use the formula @Joyce . The first time I applied the formula to the Type column.

I’m not quite sure why the first parameter doesn’t change if the columns are different?

[luka]

There is two parts to the formula:

• Lookup, which is responsible for finding the right row in your table for that corresponding region
• getKeyValue - which is responsible for picking the field that you want to display

Because of this, in lookup you will always want to use the unique field that only relates to the specific item

If you want specific formula help you need to provide more information about how your region table looks

[icos]

@luka
Supabase

Weweb

[luka]

In the Supabase table you need to find a unique identifier for this table which is ‘id’. With it you can find the exact row you’re looking for before taking any of the data. So instead of Item.data.status it should be item.data.id

[icos]

It’s not working.

[luka]

Are the IDs corresponding from one table to another? If not, you need to create IDs that will.

In your first example that worked you had locations ID that matched directly to the IDs in the location table.

[icos]

@luka
I’m totally confused. I have a main table “properties” and other tables that are connected to the main table.

In WeWeb the Type column is made as @Joyce recommended and everything works there.
If you don’t mind, let’s take it apart on the basis of “regon” column.

This approach doesn’t work with “region”

You wrote that you shouldn’t change the search identifier, but it pulls data from the related table. In this case from the “region” table via the Region column.

[luka]

Can you post a screenshot of the Type and Region tables in Supabase?

[icos]

Yes, of course.

[luka]

For the region case, the name of the column you want to display is “name” instead of “region”(last parameter in getKeyValue)

If that still doesn’t work can you try seeing what you get when your formula is only lookup(item.data.region,Region.data) and screenshot what your Region.data collection looks like

[icos]

Unfortunately, it didn’t work

[luka]

Can you post a screenshot of what happens when you only put lookup(item.data.region,Region.data) in the formula

Also a screenshot of the Region.data collection

[icos]

All in one screenshot.