Initial value in form

[Amitai_Hirshman]

Hi all
I have built a form for new orders, it includes the ‘orders’ table which has customer information, and ‘order details’ table which has product information with the order number being the ref between them.
after saving the product details in the form I would like the initial price of ‘order’ table in the form to be the summary of the price in the details. I added initial formula, but it does not seem to work. Any ideas?

[WillowMobileSys]

Initial Value is only activated on a Form the very FIRST time it is opened. For your Order #, there will not be any Order Details yet saved to the Order Details table.

Move your expression into the App formula field. With each addition of an Order Detail record, the Price field will re-execute the App formula keeping the Price updated.

CAUTION: The above will only work adding the Order Details through the Order Form. If you app has a feature where it can add Order Detail records directly - i.e. not going to the Order Form first - the Price expression in the App Formula will not get a chance to re-execute and the Price field on the Order record will not get updated. There are a couple ways to deal with that if needed. Just post back here if you need help with it.

[Amitai_Hirshman]

thanks, but actualy I did try the app formula field before posting, it still didn’t work. In any case, I need to option to change the total in the but it didn’t work. I also need the option to change the price in the orders form so i’m not sure that we be a good solution even if it did. any idea?

[WillowMobileSys]

Amitai_Hirshman:

I did try the app formula field before posting, it still didn’t work.

I was making certain assumptions. The fact that the App Formula approach didn’t work tells me that you are not set up using the parent/child relationship between the Orders and Order Details tables.

Before going into a lengthy description to solve, let’s make sure of your design and what you really want.

So, I’ll start with the question… why do you need to be able to change the Order Price and not simply use the summed Order Details Amounts?

Wanting to Sum Order Details for the Order Price AND ability to manually change that Price doesn’t really make sense. E.g. If you have a list of Order Details, each with a Price of their own, and then manually change the Order Price, then the numbers won’t match. Not good! A better approach is to force the users to manually adjust the Price of the Order Detail items and then ALWAYS rely on the SUM() function to calculate the Order Price.

In the end, I don’t know your use case so obviously it is totally up to you and your app design!!

Considering the above, do you still think you need to be able manually adjust the Order Price?? Just to be clear, it will take a little more time to build out and test and goes against the AppSheet provided mechanism’s but can be done.

[Amitai_Hirshman]

changing the price via the main table is not crucial, that’s why I tried using the app formula.
I think I am using parent/child if you mean “is part of” as in the screen shot

[WillowMobileSys]

Amitai_Hirshman:

changing the price via the main table is not crucial, that’s why I tried using the app formula.

Ok, this will make things much easier.

Placing the SUM expression into the Price column of your parent is the correct way to achieve your desired results BUT…

…there seems to be a discrepancy in your posts.

In your earlier image, you were showing the expression in a price column on the “orders” table. But the parent table for the Order # column of your “order lines” table is reflected as "orders total"

Have you changed the names or are these different table?

---

[Amitai_Hirshman]

Wow you’re sharp
Actually these are 2 similar apps with a slight change in the names of the tables. The first pic was from one the second from the other. That’s the reason for the difference, in the app the table name is consistent…

[WillowMobileSys]

Ok, I’ll assume you can sort that out. I’ll refer to Orders and Order Details.

Your expression looks correct, just needs to be placed into the App Formula field.

A side note: there is an alternative expression you can use. If you have your parent/child relationship set correctly you will automatically be given a Virtual Column named “Related order details” in your “orders” table. You can achieve the same summed results with the following expression:

SUM([Related order details][price])

---

Now, for the critical part…App Formulas are only executed when there are updates made to that row.

So, in order for the parent App Formula(s) to be correctly executed based on child changes you MUST add the child “order details” rows from the Orders Form - not from the Orders Detail view.

When you perform the Add from the parent Form, you are taken to the “order details” Form to fill in those details and when you click Save, the child row is added to the data on the device (but not yet added to the server) AND you are automatically returned to the parent Form where updates based on the new child row are made triggering the App Formulas (all of them) to be fired.

On the other hand, when you enter the parent Detail view, you may also see the child rows presented there in an inline table which will also have an Add action under it. If you add the child rows from there, you will only be interacting with the child Form. The parent Form is never touched to trigger the App Formulas.

The best solution to prevent adds from the Detail view is to modify the child Add and Edit actions so they do not show on the parent or child Detail views. This will force users to go through the Parent Form for any updates or new adds.

You can allow Adds and Edits of the children from the Detail views but it requires additional implementation, just post back here if you need help with that later.

I hope this helps!!

[Amitai_Hirshman]

I hope I understood correctly. Currently I start from the parent form and have an option for ‘new’ order details (the child, in the screen shot, I change the header it is not the name of the table). when I click it, I go in to the child form, enter details click ‘save’ and am sent back to the parent. at this stage the formula is not triggered. what should I change?

[WillowMobileSys]

In your screenshot the child row does not show Order #. Make sure that you are starting from the parent Form (not detail view) AND that the Order # is being populated on that parent Form. That Order # value is required and will be automatically passed to the child Form to “link” the two rows together.

In subsequent screenshots If you can provide an expanded image showing additional fields, I’ll be able to tel if you are indeed in the correct places. The entire view would be best.

[Amitai_Hirshman]

Hi
thanks so much for you help, attached shots of the full process, hope it’s clear:
orders table columns (parent):

order details (child):

the order form:

order form in app:

and entering the child form:

after saving this form back in the parent form:

Thanks!

[WillowMobileSys]

There still seems to be some discrepancies.

1. In one of your Order Details images, it is actually referring to the Order Lines table.
2. In your price column, you have the Initial Value with the expression. Based on our conversation above, I thought the plan was to move it to App Formula. I don’t believe it works in Initial Value, though I don’t think I have ever tried that. I’ll test this to see if it will.

[WillowMobileSys · Accepted solution]

I tested, in one of my long standing apps, moving the SUM expression from App Formula into Initial Value. The expression will NOT re-fire upon return from the child form.

So, for now at least, you MUST place your expression into the App Formula property for the parent Form to properly sum the child records in a new parent Form.

[Amitai_Hirshman]

ok
I switched to the “sum[related…]” formula in the app formula and it works. thanks so much!