Manejo de datos entre tablas referenciadas

AppSheet AppSheet Q&A
, ,
1

Hola, estoy trabajando en una App y tengo una duda … simplemente me sería de ayuda me pudiesen indicar si lo que intento es posible o no.

La estructura de datos es la siguiente :

Tengo “Productos”, estos tienen “Variantes”, estas variantes tienen “Atributos”, y estos atributos tienen un “Valor”

La estructura de datos la tengo así :

Tabla Productos : Aquí esta toda la información del producto principal
Tabla Variantes : Aquí la información de cada variante
Tabla Atributos : Aquí el atributo que aplica a la variante
Tabla Valores : Aquí el valor que aplica al atributo
y una Tabla Uniones para generar las referencias de muchos a muchos

Ej.
Producto: Gorro, Variante: Estilo A, Atributo: Color, Valor: Rojo
Producto: Gorro, Variante: Estilo B, Atributo: Color, Valor: Verde

Los atributos pueden ser uno o varios por variante.

Las uniones están así :
La tabla “Variantes” indica a que “Producto” pertenece (así sabemos que variantes tiene un producto)
La tabla “Valores” indica a que “Atributo” pertenece (así sabemos que valores le pertenecen a que atributo)
La tabla “Uniones” indica que “Variante” posee que “Atributo” y que “Valor” tiene este atributo (así sabemos que atributos pertenecen a que variantes y que valor tiene)

Hasta aquí la App funciona correctamente …

Bien … ahora duda, estoy generando templates JSON para utilizar por Webhook, también funcionan … pero aquí el problema …

Necesito poder resumir, por cada “Producto”, un recopilado de que “Atributos” únicos tiene cada “Variante” y cada atributo que “Valores” tiene …

Ej. Si tengo una variante que tiene como “atributo : color” y este tiene como “valor : rojo” y tengo otra variante que tiene como “atributo : color” y este tiene como “valor : azul”, el resumen sería que el producto tiene el “atributo : color” con los “valores : rojo, azul”. Y así con el resto de los atributos que posea cada variante

No logro realizarlo ni dentro de la tabla por expresiones como una columna virtual, ni dentro del template empleando el <<Start: >> <<End>>

Lo que me puedan recomendar se los agradezco !

2

Hi!

I’m not sure if I understand correctly but if you need to sum up various values, it might be a good idea to make a table with a list of all of the products. Then, I think you could use expressions in virtual columns to sum up attributes like colors, etc for each product. Those virtual columns would produce “lists” of attributes.

3

Hi Kirk!

Thanks for your answer, excuse my english …

I understand what you are saying, but the attributes are not the same for all products.

For example we can have …

Product Id Attribute Values
1234 Color Red, Blue
1234 Size S, M, L
1235 Color Red. Yellow
1235 Type Open, Closed
1236 Size S, L

and so …

For this reason I had to create tables linked to each other to be able to generate the different dependencies.

→ “Product” has many “Variations”
→ “Variations” has many “Attributes”
→ “Attributes” has many “Values”

But since the “Attributes” can contain different “Values” in each “Variation” I had to generate the table “Joints” to be able to tell it that each “Variation” has an “Attribute” with the “Value”

I think my problem is being able to filter and interact with the data from different tables that are linked to each other.

4

Hi!

I have to respond quickly because I need to get to bed.

I don’t think it is a problem that the attributes are not the same for all products. Each attribute (virtual column) will take a “list” that can either be empty or have several items (the number can depend on the product).

5

I understand … but how would you do so that in turn that virtual column (list) has in turn each item another list with its values?

That is, each value in that list would have another list of values in turn.

Rest Kirk … when you can when you can I await your comments … thanks!

6

Hi! I made a little app for you to illustrate what I mean:

https://www.appsheet.com/templates?appGuidString=f3e4f758-cb34-4729-a23e-411bf5a88428

I may have misunderstood what you are trying to do but actually looking at the app may make it easier to understand what I’m thinking.

By the way, I’m sharing this publicly but I did not put it on my portfolio page. To do this I followed the steps that @Steve has outlined here with one exception:

[Deploying a sample App for free from a free subscription plan](https://community.appsheet.com/t/deploying-a-sample-app-for-free-from-a-free-subscription-plan/30779/3) Questions

[image] [image] [image] [image] [image] [image] [image] [image]

The exception is that I didn’t actually deploy the app. Then, I made the url as follows:

[September 14, 2021](https://community.appsheet.com/t/september-14-2021/54921/12) Feature Release Notes

@Kirk_Masden fix is in, you should be able to reference your sample apps as before using www.appsheet.com/templates?appGuidString=XXXXXXX

The appGuidString can be found here:

3X_4_8_48c41a90e98a34ba3c727ab7d42d071cec72888f.png
3X_4_8_48c41a90e98a34ba3c727ab7d42d071cec72888f.png1276×578 45.1 KB

2 Likes
7

Hello, your example is very good!

It is similar to what I need to do but it does not have the same structure, since the data related to each other is part of another data structure that I cannot vary.

Each “Attribute” and each associated “Value” are also part of other structures that for example calculate product costs, etc. Let’s say they are not used only as aesthetic attributes of the product.

Anyway, after many hours! I think I managed to advance a bit …

As what I need to do is based on a json template that I need to prepare to send by webhook to another application, I managed in part to format this information in there with the << Start: >> <> functions

"attributes": [
{
    "id": "2",
    "name": "Color",
    "visible": true,
    "variation": true,
    "options": ["Blue", "Green", "Green"]
  }, {
{
    "id": "2",
    "name": "Color",
    "visible": true,
    "variation": true,
    "options": ["Blue", "Green", "Green"]
  }, {
    "id": "3",
    "name": "Material",
    "visible": true,
    "variation": true,
    "options": ["Wood", "Steel", "Paper"]
  }
]

Example “name”: “Color” is repeated, and “Green”, “Green” is repeated …

  "attributes": [
    <<Start:
    FILTER("Products Variations Attributes Links",
      ([Product] = [_THISROW].[Key])
    )>>
    {
      "id": "<<[Products Variations Attributes].[Id]>>",
      "name": "<<[Products Variations Attributes].[Name]>>",
      "visible": true,
      "variation": true,
      "options": [
        <<Start:
        ORDERBY(
          FILTER(
            "Products Variations Attributes Links",
            AND(
              ([Product] = [_THISROW].[Key]),
              ([Products Variations Attributes Options].[Products Variations Attributes] = [_THISROW-1].[Products Variations Attributes])
            )
          ),
          [Products Variations Attributes Options]
        )>>
        "<<[Products Variations Attributes Options].[Name]>>",
        <<End>>
      ]
    },
    <<End>>
  ]

Now what I can’t do is to be able to filter only the unique values, I need no duplicates.

Do you know how I can modify those formulas to avoid duplicates?

Thanks !

8

Thank you for the code. Actually, I don’t understand JSON well but I can say that I have a UNIQUE() function in the app I shared. Please look at the expressions in the virtual columns:

1 Like
9

Hi Kirk …

The UNIQUE() function does not work in this case because it is not a list, and the SELECT(Table[Key], TRUE, TRUE) function does not work for me either, because it only skips duplicates in the primary key or in the specific field being searched for.

I really can’t find something so basic I think it is to be able to eliminate duplicates in a row search of a table.

It would be very interesting if the FILTER() function, for example, could have the option of eliminating duplicates in a certain column, or, as it would be done in SQL, to be able to group by a column so as not to have repeated data.

I really appreciate your help and will keep trying to fix it!

10

I’m sorry not to be able to help. My only other thought is to make a slice and then work with that . . . but I understand that may not work in your case. I hope someone else can help.