Hi, I'm facing the following issue. Value is...

(Yossi Oulu) #1

Hi,

I’m facing the following issue. Value is not written to the database (MSSQL). I have two tables: Order and Items (multiple Items per Order) In Items I have an Amount value In Order I have a Total field which is a formula to Sum the Amount in all the relating Items.

I start with creating a new Order,

click on Add to add an Item,

Creating an Item,

Click Save on the Item (it goes back to the Order) I see the correct Total on the screen I click Save, it syncs. BUT the database has zero in the Order Total. Any idea what can it be?? Editing the Order at a later stage, (the formula is recalculated) and clicking save it Does appear in the DB. Looks like the order of the sync is wrong or something, the Order is written before the Items so it gets Zero as the total (??) Thanks

P.S. This is the formula in the Order:

=SUM(SELECT([Related dbo.OrderItems][Amount Currency], [PO#]=[_THISROW].[PO#]))

(Aleksi Alkio) #2

Hi, the reason is not the formula itself… well, I believe you could write it like as SUM(SELECT([Related dbo.OrderItems][Amount Currency],TRUE)) because you have used the relationship with the format […][…] already.

The main reason is the saving order. The app will save the parent record first and then child records. When you are using the normal column for this purpose, it won’t write the correct value. Create an action and sum the value with the event action.

(Yossi Oulu) #3

OK. (I assume you you mean to place an event on the save of the child record) . I will try. Thank you!

Do you mean normal column vs. Virtual? For the total of the Item or for the total of the Order? I just looked at the sample app: Order Capture, and indeed they have virtual col for the Order total.

btw: In any case doesn’t it make sense for the Sync to save the records in the same order that the Save was done? How the delayed sync works without keeping the original order?

Thanks again Aleksi for the prompt response

(Aleksi Alkio) #4

If you don’t need to have this SUM as a normal column in your database, you could do that with the virtual column… and no event action is needed.

(Yossi Oulu) #5

Thanks! I managed to do that with Event. But it seems to work slow.

I do the following: In Workflow, when Item is saved, I call an action. In the Action (on table: Order), I would like to check that it picks the correct orderto update

so I condition:

[PO#]=[_THISROW][PO#]

(both Order and Item has a [PO#])

Will the [_THISROW] relates to the Item table? (or generally the table of the calling Workflow?)