How to delete orphaned values in enumlist ref column

[dbaum]

I have a table of Lists and another table of Messages. The Messages table includes a [List ID] column, which is an enumlist column and for each row contains a list of zero or more IDs from the Lists table.

When a user deletes a row in the Lists table, I need to remove that row's [List ID] from the Messages table's [List ID] enumlist column.

While I know that not all the following functionality exists and that the syntax is off, conceptually what I need is something like:

• an action for the Lists table that updates the value of the [List ID] column in the Messages table...
• to Messages[List ID] - LIST(Lists[List ID])...
• for rows defined by IN(Lists[List ID], Messages[List ID])

It occurred to me that I could utilize an automation that triggers when a Lists row is deleted (or, maybe marked for deletion so I can reference it before then actually deleting), but I don't know how to pass the Lists table's [List ID] to an action that would then run on the Messages table. I feel like it might be possible to use a "Call a Process" step, perhaps in conjunction with a  "Return a Value" step, to somehow retrieve the ID of the Lists row and then use it to update pertinent rows in the Messages table. However, I haven't been able to figure that out and I find the documentation opaque. So, I'm consulting here before further banging my head against that wall.

Does anyone have advice regarding how to accomplish this? Am I on the right or wrong path with the automation idea? I assume others in the community have already encountered and solved this issue of orphaned references.

[Joseph_Seddik · Accepted solution]

I did it with INPUT().

[dbaum]

¡La pista que me faltaba!, Mil gracias, amigo. I created a successful proof of concept in my sandbox app, and will now go implement in my real app.

As with the documentation about automation process step types, I've likewise been confused by the INPUT() documentation, and appreciate your confirmation that that's the feasible solution path. I hadn't sufficiently experimented with/understood the use of INPUT() to trigger the "With these inputs" action property that enables passing from one table's action to another table's action "Any values to pass as inputs to the action" (as opposed to using INPUT() only as a mechanism to elicit a value from the user). I now comprehend what the screenshotted examples in the Dynamic inputs for data change actions article are intended to represent. In trying to follow that example, I wasn't previously detecting the ambiguous use of "Quantity" as both the global name of the column to be updated in the LineItems table and the local name of the input value received via the action initiated from the Orders table--especially given the INPUT() article's specification that the function's "inputName" argument must be "unique within the context where it is used". I had interpreted that to mean the inputName argument can't be the same as a column name; unless there's an error in the article's example, I guess it instead probably means that multiple INPUT() expressions in the same action can't use the same "inputName" argument.

Gracias de nuevo por el consejo. Me ha ayudado a interpretar mejor el mismo artículo que le había señalado en otro contexto.

[Ricky_Chua]

Hi @dbaum , I stumbled across this post where i'm facing the same problem in your original post. I also found the documentation for INPUT() to be hard to understand.

May i find out specifically how you accomplished your goal? Did you create a bot that is triggered when the List table has a row deleted? Or a grouped sequence of actions without a bot? And how did INPUT() come into use?

[dbaum]

---

@Ricky_Chua wrote:

Did you create a bot that is triggered when the List table has a row deleted?

---

No.

---

@Ricky_Chua wrote:

Or a grouped sequence of actions without a bot?

---

Yes.

---

@Ricky_Chua wrote:

And how did INPUT() come into use?

---

[Ricky_Chua]

Thank you for the detailed reply.

Was the action "Send list to remove from Action name" triggered by physically clicking on a button, or was it triggered by somehow linking the system's "DELETE" (trashcan) action?

I am getting stumped by the system's DELETE action because I can't add a "Behavior" action to it. It seems to only trigger another action when I create a bot and set the event as a row deletion.

[dbaum]

Suppress the system delete action from the UI, and then include it in your composite action.

[Joseph_Seddik]

De nada 🙂 Afortunadamente he hecho exactamente lo mismo con INPUT(). Tengo un cliente que hacía liquidaciones por servicios de manera entera, y me pidió introducir la funcionalidad de liquidaciones parciales por servicio. Dentro de mi desarrollo he añadido un registro de liquidaciones parciales junto a cada servicio, un EnumList. Para acomodar la funcionalidad de eliminación de una liquidación he tenido de eliminarla también de la lista asociada a cada servicio, y para eso utilicé INPUT().  

[dbaum]

Y ahora yo también. Acabo de empezar a implementar la técnica en varios lugares dentro de mi app, y sirve de maravilla. Gracias de nuevo.

Tendré que evaluar si hay otros lugares donde la puedo usar para simplificar acciones que he creado en el pasado que dependen de columnas superfluas que sirven únicamente para almacenar un valor provisionalmente para incorporarlo a otra columna. Sobre todo que ahora también entiendo que no se limita al nivel mayor de las funciones dentro de una expresión sino al de las tablas referidas....

[Joseph_Seddik]

Yo diría más bien limitar el uso de INPUT() salvo donde sea necesario; estando en el estado beta, sobre todo para lo que ya funciona.. sólo mi opinión..