I can't make the add action for a related table to appear only if a condition is met

[vramirez80]

Hi all, I’m making a sales app where every sale should be capable of add elements to the sale if the state of the sale is ACTIVE, on the contrary one only can be capable of see the content of the sale.

Initially I used the parent field [Estado_Pedido] with the condition only if set with the formula

[OrdenID].[Estado_Pedido] = "ACTIVO" 

but it doesn’t work; this formula makes the Add button dessapear in all cases.

After that I used the formula

ANY(
  SELECT(
    Pedidos[Estado], 
    [ID_Orden] = [_THISROW].[OrdenID]
  )
) = "ACTIVO"

the above formula doesn’t work either.

After that I used the are updates allowed? of the configuration of the table

I also used the formula

LOOKUP([OrdenID], "Pedidos", "ID_Orden", "Estado") = "ACTIVO"

and doesn’t work either.

In the are updates allowed, the editor send a message saying that the formula is invalid with the following formula

Table ‘Orden_Detalles’ has an invalid update mode expression ‘=IF( AND( CONTEXT(“VieW”) = “Pedidos”, LOOKUP(Orden_Detalles[OrdenID], “Pedidos”, “ID_Orden”, “Estado_pedido”) = “ACTIVO” ), “ADDS_ONLY”, “READ_ONLY” )’ Cannot compare Text with List in ([ID_Orden] = Orden_Detalles[OrdenID])

so whath can I do to solve this, thanks in advance for the help.

[WillowMobileSys]

The issue here is that the ADD button is not a row-level action. It operates at the Table level - inserting a row into the table. So, it has no single row in context for which it can test specific column details.

One solution is to divide your data, using Slices, into Active records and Inactive records. Then disable the ADD function for the Inactive records. How you do this depends on the implementation but the easiest is to specify in the Slice itself which actions ARE available. There is a “Slice Actions” property on the Slice.

You can then combine the Active records view and the Inactive records view into a single Dashboard for user simplicity.

I hope this helps!

[vramirez80]

Thanks for the advice, I did it but the problem is not solved as you can see in the images attached, the button add still appears in the field of Related_Orden_Detalles where I need to make it dessapear no matter if I disable the updates in the slice options

The only thing that worked moderately well was to hide the entire Related_Orden_Detalles column if the sale is differente of ACTIVO

[Steve]

You need a slice that prevents adds, an inline table that uses the slice, and logic to choose which inline table (allow adds or not) to show according to whether the order is active or not.

1. Create a slice of the Orden_Detailes:
   • Name (e.g.) Orden_Detailes (no adds).
   • Disallow adds.
2. Add a virtual column in Pedidos:
   • Name (e.g.) Related Orden_Detailes (no adds).
   • Show? [OrdenID].[Estado] <> "ACTIVO". This inline table will only appear if the order is not active.
   • Type List.
   • Base type Ref.
   • Referenced table Orden_Detailes (no adds) (the slice from above).
   • App formula [Related Orden_Detailes].
3. Modify Related Orden_Detailes in Pedidos:
   • Show? [OrdenID].[Estado] = "ACTIVO". This inline table will only appear if the order is active.
4. Make sure the new Related Orden_Detailes (no adds) column occurs in the detail view along with Related Orden_Detailes.

If the order is not active, you may also want to prevent edits and deletes of the details. If so, adjust the slice settings accordingly.

[Steve]

Note that this does not strictly prevent the addition of new detail rows: there are obscure conditions that could allow it. To strictly prevent it under all circumstances, also modify the key column of the Orden_Detailes table:

• Valid if OR(([OrdenID].[Estado] = "ACTIVO"), IN([_RowNumber], Orden_Detailes[_RowNumber]))

[vramirez80]

Its worknig, its working!. I can admit that it would never have occurred to me to do that, so, can you explain me how could yo possibly arrived at the conclusion for make that operation?.

I need to know it for future apps developing, speciallly I don’t understand why you use the app formula in the step 2 with the formula [Related Orden_detalles] if you are using the slice from the step 1.

And of course, its working the way that the Related Orden_detalles column with adds allowed is shown in the orders that are marked as ACTIVO and the Related Orden_detalles with no adds allowed is shown in the orders that are not marked as ACTIVO.

[Steve]

The Add button in an inline view is shown only if the table or slice containing the displayed rows allows adds, so to control whether the button appears, we either have to control whether adds are allowed for the table or slice.

We can control whether adds are allowed for a table in either of two ways:

1. Use Are updates allowed? in the table settings.
2. Use Only if this condition is true in the table’s Add action.

Neither of those approaches allows us to disable adds only when showing rows in an inline view without a lot of difficulty.

Slices allow us to present rows of a table, and to put restrictions on those rows beyond what the table itself does. We want to show rows of the table, but prevent adding new rows. A slice can do that.

In the detail view, we want to prevent users from adding detail rows if the order is not active. The slice prevents adds, but how do we use the slice?

The inline table in the detail view is shown for a column of type List with a base type of Ref.

• The rows shown are pulled from the Referenced table setting.
• In the Related Orden_Detailes column, the Referenced table is Orden_Detailes, which allows adds.
• If you change it to our slice, Orden_Detailes (no adds), which doesn’t allow adds, the Add button in the inline table will go away.
• The Referenced table setting can only have one value, so how to allow adds sometimes but not others? You can’t!
• Instead, leave the original Related … column pointing directly to the table.
• Display this column (and thus its inline table) only when adds are allowed.
• The column’s Show? setting can hide it when the order is not active.

With the add-able inline table hidden, we need to display a non-add-able table only when the order is not active.

• To add an inline table, we need a virtual column, Related Orden_Detailes (no adds)…
• …of type List, base type Ref…
• …and a Referenced table of a table or slice that presents the rows of Orden_Detailes but doesn’t allow adds…
• …which is what our Orden_Detailes (no adds) slice does.
• To identify which rows to include in this inline table, we just re-use the list conveniently already present in the Related Orden_Detailes column by using the App formula, [Related Orden_Detailes].
• This works because that “list of rows” is actually a list of row keys (key column values).
• A table and all of its slices use the same key column.
• Use Show? to display this column only when its order is not active: [OrdenID].[Estado] <> "ACTIVO".

Mind you, I worked all of this out only after using AppSheet for several years (IIRC), so this is not intuitive. Don’t feel at all bad if you hadn’t figured it out, or are still confused even after my explanation.

[vramirez80]

Thanks a lot @Steve, this answer of yours is a very clever answer to me. Now that I see your explanation I can see that the app formula uses the keys of the table, sometimes ins confusing to me to realize the fact that appsheet connect the dots internally because I’m use to code all that is needed in my programs