Why can't I copy a value from one table to another using execute an action on a set of rows (I have three assets tables and one transfer table)

[vramirez80]

Hi all, have three tables with inventory for three headquarters, Farma, Piñonal I and Piñonal II, I have another table named TRASLADOS where are registered all the transfers between headquarters, all the transfers are correctly stored in the table TRASLADOS, this is made with an action of the type go to another view in this app directed to a form view with several prefilled values, then in the TRASLADOS table I have a compound action which first writes the registry of the transfer in the table corresponding headquarter, after that another action indide the compound action updates the inventory quantity of the specific asset in the destination table (all of this works correctly).

I have a third type of action inside the compount action of the type execute an action on a set of rows in the TRASLADOS table, this last action calls an action to substract the transfered quantity of an asset, which is stored in the TRASLADOS table, from the original quantity of that asset stored in the table of the inventory of the original headquarter; this last action doesn´t work at all.

Looking in another post I read and found an example app, in that app is explained that it must be an parent table and a child table related between them through Refs, in my case I think it should be the parent table the inventory table of one headquarter and the child table the table TRASLADOS, the problem with this is that it doesn´t works for me because I have at least three tables (one for each headquarter) all of them can´t be parent tables of the TABLE traslados.

The problem I have is that is imposible to me to mantain the context, in this case the context I want to mantain it by store the ID_ACTIVO (code) of the asset to use it to select the row for update it existence quantity of the asset in the table of the original headquarter by its ID_ACTIVO column.

I don´t know what to do next to solve the problem altough now Iḿ thinking about making a TRASLADOS table for each headquarter, this will allow me to have a parent table and a child table for each headquartes but this probably will make things harder.

So, please someone help me to acomplish the objective.

I’ll update some pictures to show the problem

The descontar action never is applied and I don´t know why but I assume that the lookup function is unable to retrieve the ID_ACTIVO value to transfer it to the actions for the destination table.

Note that in the grouped action are what it seems repeated action but the actions names are terminated with the destination table in which they will be applied, right now I’m interested in the FARM actions. For the others can be copied the outcome of this post

What can I do to solve it?. Thanks in advance for the help

grouped action

descontar action

condition to apply the descontar action

formula to select rows in the descontar action

restar action

formula to update the value of the CANTIDAD ACTUAL column in the destination table, using the restar action

[WillowMobileSys]

It seems that the LOOKUP() function is retrieving rows from the “Translados” table but is attempting to send them into the action that is expecting to operate on rows for the “Inventario_General_Farma” table.

This is invalid. AppSheet not catching this table mismatch in the Editor is a bug in the platform for which I opened a ticket a couple years ago.

The table used for the selected rows must match with the table configured in the action being called.

It seems that table row you wish to adjust is the “Translados” table? If so, then the action table needs updated.

@vramirez80

I don´t know what to do next to solve the problem altough now Iḿ thinking about making a TRASLADOS table for each headquarter, this will allow me to have a parent table and a child table for each headquartes but this probably will make things harder.

No, don’t so this. It WILL make things more difficult - especially if you need to scale.

[vramirez80]

I don’t understand very clear what you said but preciselly I want to pass the ID_ACTIVO of the current asset to update preciselly that asset in the parent table

[WillowMobileSys]

Ok, I now understand that [ID_ACTIVO] is a row key in the “Inventario_General_Farma” table - the Parent table. You simply want to pass the reference to that Parent row into the action for updating.

The LOOKUP() function was throwing me. You do not need it.

You can simply use an expression like this:

LIST([ID_ACTIVO])

Make sure that [ID_ACTIVO] in your “Translados” table is defined as a REF column to the “Inventario_General_Farma” table.

In theory, if the [ID_ACTIVO] column is properly defined, I think the LOOKUP() should work. But I rarely use LOOKUP() so I am not certain.

[vramirez80]

Ok, thanks, I’ll try it and I’ll let you know

[Steve]

@WillowMobileSys

The LOOKUP() function was throwing me. You do not need it.

@WillowMobileSys

In theory, if the [ID_ACTIVO] column is properly defined, I think the LOOKUP() should work. But I rarely use LOOKUP() so I am not certain.

LIST(
  LOOKUP(
    [_THISROW].[ID_ACTIVO],
    "TRASLADOS",
    "ID_ACTIVO",
    "ID_ACTIVO"
  )
)

This LOOKUP() expression is valid, but probably isn’t needed. @WillowMobileSys’ alternative, LIST([ID_ACTIVO]) is likely all you need.

Why might you want to use the LOOKUP() expression? To confirm the ID_ACTIVO value in this row actually exists in the TRASLADOS table. If it does not, the LOOKUP() expression will return nothing, so the resulting list will be empty.

[WillowMobileSys]

@Steve

Why might you want to use the LOOKUP() expression? To confirm the ID_ACTIVO value in this row actually exists in the TRASLADOS table. If it does not, the LOOKUP() expression will return nothing, so the resulting list will be empty.

Actually, the same happens with LIST([Row ID]) in the action if the Row ID does not exist. I assume AppSheet attempts to locate the row first. If the row is not found then the action is NOT even attempted to be executed. No errors are logged.

Which is actually a bummer because if you have a strongly coupled parent/child and for some reason a child becomes orphaned, it would be nice if the AppCreator was notified of that, at least through a log entry, so the issue could be identified and corrected.

[vramirez80]

Hi @Steve , how are you. As suggested by other person there is what I did

Hi June, how are you?.

I’ve changed the formula for CANTIDAD_ACTUAL in the action restar for the following formula:

NUMBER([_THISROW].[CANTIDAD ACTUAL])
- LOOKUP(
    MAXROW(
      "TRASLADOS",
      "FECHA_SOLICITUD",
      AND(
        [ID_ACTIVO] = [_THISROW].[ID_ACTIVO],
        [UBICACIÓN GENERAL_CIUDAD] = [_THISROW].[UBICACIÓN GENERAL_CIUDAD]
      )
    ),
    "TRASLADOS",
    "ID_TRASLADO",
    "CANTIDAD_TRASLADADA"
  )

and the referenced rows fromula in the action descontar for the following

LIST([ID_ACTIVO])

and it keeps doing nothing for substract the CANTIDAD_ACTUAL value from the original CANTIDAD_ACTUAL value in the INVENTARIO_GENERAL_FARMA table. Is the only action that i doesn’t executes when is called. In the other hand the condition for availability of the action descontar is the following:

[_THISROW].[UBICACIÓN GENERAL_CIUDAD] = "Centro Logistico Farma"

I know the action descontar is not being executed because the confirmation message is enabled and I have a confirmation message to show when that action is executed.

So I don´t know what to do next

[vramirez80]

Hi all, how are you?.

finally asked to gemini to get help and in this link is the answer from gemin, right now I have the application running the external action of the type "execute an action on a set of rows but the internal action which actually substract the transfered amount from the original inventory is not being executed.

What bothers me a lot is that the action that updates the amount of the asset in the destination inventory is almost the same kind of action and it works well, so I don’t know whats going on. I’ve made everything passed over my head and I couldn’t be make that action work.

I think the between the call of the action of type execute an action of a set of rows and action called of the type set the values of some columns in this row the context is lost but I don’t know.

I need help here, I only need help with that action in that context.

I’m screwd

[WillowMobileSys]

@vramirez80

I need help here, I only need help with that action in that context.

I’m screwd

Your process flow (after the Group Action) is simple and I think what you had initially was perfectly fine.

What you need to evaluate is that:

1. The condition to run is correct for action “Descontar inventario del inventario original FARMA”
2. “LIST([ID_ACTIVO])” is returning the ID you expect
3. The INPUT variable “CantidadARestar” is getting passed correctly.

I typically do this by establishing temporary Virtual Columns and showing them in one of the Form views or Details views when the use case triggers the Grouped Action you have set. This allows a visual confirmation that the conditions and values are what I’m expecting.

If you’d like, I’d be willing to get on a video conference call and walk through the implementation. I’m certain that together we can fix the issue.

[vramirez80]

Hi @WillowMobileSys , thanks for the help.

About your explanation, I’ve been testing the conditions by using test tables and apparently all works (in the test tables) but when implemented simply the “restar” action which is called from the “descontar” action doesn’t work, is not being executed; I know that because in the calling action and in the called action I have enabled the confirmation messages and the only confirmation mesassge that is not shown in screen is the one set in the “restar” action.

Now I will try to explain what I’m doing right now:

Look, no matter what I did the application seems to loose context (doesn´t store the ID_ACTIVO value needed to select the correct asset ) so I decided to look back into my previous working mode of work.

This is all, in the process of initiating the transfer, registering it in the TRASLADOS table and registering in the destination inventory table is done from the beggining, with the difference that in the origin inventory table the “CANTIDAD_ACTUAL” value is substracted from the beggining so when I press the button to receive the transfer it only will be added to the destination inventory table.

If for example there was a mistake sending an amount of assets to another headquarter then in the receiving headquarter they will have to mark the sent assets as received or refused, in case they marked as received then they will have to send it back again by initiating a transfer or, in case the transfer is marked as refused then it automatically will be re-added to the original inventory table (this is in progress). I think this nigth in Venezuela or tomorrow all the described above would be ready.

Right now the app is working including by substractiong the amount of the asset transfered from the opriginal inventory all in the same operation.

About your availabilty to make a videocall, I’m very grateful for that and because of another things that I need to do for work I think I could tomorrow, I need to say too that this application is intended to be presented in an enterprise but I don’t work with them (bassically I’m making the application without being paid for that), what I intend to do is the posibility of selling them the application. @JuneCorpuz has been very helpful and beleive me we had tried a lot of things but the specific step of substracting the transfered amount from CANTIDAD_ACTUAL and then store the result in the same column if done from the TRASLADOS table doesn’t work at all, all the remaining action works without problem. The substraction only works if done from the same iriginal inventory table.

My work email is (PII Removed by Staff)

[WillowMobileSys]

@vramirez80

the “descontar” action doesn’t work, is not being executed; I know that because in the calling action and in the called action I have enabled the confirmation messages and the only confirmation mesassge that is not shown in screen is the one set in the “restar” action.

Based on this, there are only 2 things I see that would prevent the “restar” action from exeucting:

1. ID_ACTIVO is not identifying an existing row.
2. There is a condition set in the “Behavior” property of the “restar” action preventing it from executing.

I will send you a direct message to your email.

[vramirez80]

Yeah, I tought that too but right now the aplication with the condition set in behavior for the “restar” action is working well if executed from the original inventory table, thats what makes me think the ID_ACTIVOis not identifying an existing row, I mean, it looses context (I think) vramirez.magdatec at yahoo-com