Relate 2 tables to send JSON

[JuanIsidoro]

Hi, nice to meet you. I'm Juan Isidoro,
I need your help with the following problem. Hope someone can help me. Thank you very much ^^

I have 2 tables, where each "Product" row has its “name”, “price” and “categories” (Nombre, Precio and Categorias in Spanish). And each category has an associated "ID_EXT" in the "Categories" table.

INTRODUCTION
Product ID=867c1b4e | Name: "Camiseta Woo"
It belongs to the category: “Ropa”. This value, in the "Categories" Table, has an associated external identifier: "CategoriaID_Ext", in this case "12".

EXAMPLE WITH THE PROBLEM
Product ID = 74244351 | Name: “High pink socks” ->  Categorias: Calzado, Tenis -> CategoriaID_EXT: 14, 26

 Currently I send in a JSON where I include the information and the categories

EL JSON, es:

JSON Body

In the TEST it is executed correctly (using Make, old Integromat)

But I want, instead of receiving the categories: "Calzado, Tenis". Receive the IDs corresponding to the table: "Categorias", in the column: “CategoriaID_Ext”.

That is, I want to receive:

this is the answer i need

How could I solve this? And what will be the most efficient way?

Thank you so much beforehand! 😉

[Joseph_Seddik]

Hola Juan, bienvenido en la comunidad!

You can just have another column in the table containing the corresponding CategoriaID_Ext values, and express that column instead in your http template. 

[JuanIsidoro]

Hello, thanks for the welcome and thank you very much jose_seddik for your answer.

I have tried that. But I don't know what I'm doing wrong 😞

The CategoriaID_Ext do not appear correctly.

[Joseph_Seddik]

Please show your columns setup and the expression you are using to calculate the CategoriaID_Ext. 

[JuanIsidoro]

The expression is shown below.

But I think I'm not using the SELECT correctly... I want to show from the Categories table, the CategoryID_Ext column. When Categories = Categories[Name]. but since it is a LIST, it doesn't work 😞

PD.I have tried many options and I do not understand the correct operation.

THANK YOU!

[Joseph_Seddik · Accepted solution]

The correct way to do this is:

1. In the Productos table, your Categorías EnumList column should have a base type Ref pointing towards the Categorías table. The column will contain CategoríaID values instead of Nombre.
2. You can mark the Nombre column in the Categorías table as Label, so that you can see the Nombre values in all app views. 
3. You can then use the following expression in the template directly:
   
   {
      ...  ,
      "Categories":  "<<[Categorías][CategoríaID_EXT]>>",
      ...
   }

Please read these:

• References between tables - AppSheet Help
• Build list dereferences - AppSheet Help

[JuanIsidoro]

Thank you so much Joseph_Seddik!! ^^
It's working, I send capture. Thank you very much!

But now I have a problem... This way I don't have the multiple selection (EnumList) for a product in the category. That is, I can no longer select multiple categories at once.

Is it preferable to create another column for subcategories? (TABLE: "Categorias") or a virtual column?

Fixed problem, shows:

[Joseph_Seddik]

Make sure the type is EnumList, not Enum.

[JuanIsidoro]

Cool!! Yes now! I was wrong.
Attached capture of everything ^^

Thank you very much for your help!! THANK YOU!
have a great day

[Joseph_Seddik]

De nada amigo 🙂