Struggling to get total for field in form A to show as calculation in form B

[richard maliszewski]

My long term goal is to create a budget/envelope setup to replicate/replace the soon-to-die Mvelopes system. I’ll have a parent form that shows the transaction totals for child envelopes, and can total that in a summary.

First baby step is to get a child/envelope total to appear in the parent budget form. I have created a pair of forms. The transaction field to total is named “Amount”. I have created a link-to-form to the child from the parent, using the parent’s dummy Amount as the link to the child’s Amount field, JOIN. The type of that per-envelope balance field is calc, and editing the formula, double-clicking on the Amount field gets me a TOTAL formula. All looks okay. No matter what I do for records in the child-envelope form, the total shown is $0.00.

I had figured this would be the first simple baby step. Apparently not. Help?

–Richard

[Daniel Leu]

Can you share your form template?

[richard maliszewski]

Here’s the parent template. It’s pretty barebones.

–Richard

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

Thanks for the template. Barebones is perfect as there is not much to get lost in.

In the calculation field, set the return type to text.

[richard maliszewski]

Did that, and it made no difference. Should I delete the field and recreate it with the calculation result as text? TF has shown a tendency at times to become emotionally attached to my mistakes.

Once I get past this, I’ll want to be able to sum the non-link form fields, but presumably this text gets coerced into the currency format for the calculation field? Or does that need to be “text” as well? Seems like an odd result for a calculation.

Sigh.

[Brendan]

Actually the Return Type set to Number is fine. And I’m getting the correct results.

See my attached screenshot.

Because you’re using the Amount field as the Link to Form field’s Join field, then you must have the same Amount value in every child record that matches the parent’s Amount field.

Attachments:

You must be logged in to view attached files.

[richard maliszewski]

OK, past that brain fart. Of course, the only joined records are those that match the join field. So I am getting my hoped-for total. In a perfect world, adding a new record to the child/envelope form would result in an update to the total in the parent form. That seems to require me to do a “refresh records list” for the update to occur. I don’t have “calculate one time only” checked in the calculation formula dialog. Is this expected behavior? It’s not a show-stopper on the Mac, might be more of a pain to deal with on the phone.

–Richard

[Brendan]

You can also click on the refresh button at the bottom of the record itself. The iOS version operates a bit differently where you’re navigating different screens and they get refreshed.

When you add a child record to the parent form, the calculation will be updated. Even when you update a value in the child form, the parent form’s calculation field should update. I just tested that with your form and it updated the result for me.

I simply added a new child record (the default Amount being the amount from the parent record) and the formula was updated. Which version of Tap Forms are you using?

[richard maliszewski]

So I have made a fairly major change to the parent-to-child join…I want to be able to sum the amounts from all the records in an envelope, so I added a dummy field to both parent and child with value 0, so the JOIN will hit all the records. The amount being summed is not that dummy. Adding or modifying child records does not result in an update in the displayed calculation value in the parent. Hitting the refresh at the bottom of the parent record gets everything caught up.

It’s likely I am doing this the hard way…as the relation between the parent record and child records is one-to-many. But posts you have made to the forum in the past have suggested that JOIN + calculation is the way to go.

Attaching the current foolery-in-progress.

–Richard

Attachments:

You must be logged in to view attached files.

[richard maliszewski]

And there may be no joy in Mudville. My hope was that this parent form would have summations for multiple envelopes. Naively, I expected that the “Link to Form” field was a per record field, since it’s, well, a field. But thus far, it appears that changing that field for one record in the parent form changes it for all records…so it’s effectively a form property, and not a field value. I can see why allowing multiple joins would be problematic, but it’s not as clear that having a per record one-to-many would bring down the house.

Is there a workable supported route for what I am trying to do? It’s starting to look like the parent may have to have a per-envelope aggregation script. Javascript is not my language of choice, but it looks like there’s enough functionality to do this without having form-to-form relational doings.

–Richard

[Author]

Posts