How to repair calculation(s)

[Michael Harding]

Hi, somewhere along the way the calculations that I had set in my db have gotten broken. Try as I might, I can’t seem to “repair” them or even to do re-do them. And I can’t see a straightforward step-by-step process as to how to do this. It seems to me that this used to be somewhere on the Tap Forms site, but can’t find it now. I am just doing the easiest of equation, pricing up an item and adding additional costs to come up with a total of (A+B+C+D=Total). This should be easy, but I can’t see it, apologies for asking an obvious question and thanks for any advice.

[Brendan]

Can you post a screenshot of your formula?

[Michael Harding]

Hi Brendan

I feel pretty stupid – I have done this before with the earlier version, but can’t recall how now. A very simple calculation, as you can see, just a running total of costs to allow me to work out the unit cost.

Thanks,

Michael

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Put zeros in the empty fields, I think the null values is what is tripping up the formula. You’ll need to wrap each of the accesses in IFEMPTY() calls to guard against that. Alternatively you can default them to 0 and I think that should work as well. But easier to wrap in IFEMPTY() than to forget about it and get confused later when you null out a field and the calculation breaks again.

I refer to this note on the Calculations page of the documentation:

If you don’t provide a value for every field in your formula, you will receive an empty result for the Calculation field. To resolve this issue, you can either set a Default Value for all the fields used in the formula, or you can use the IFEMPTY() function to return a default value if none is provided. For example IFEMPTY(Price; 0; Price) which will return 0 if the Price field is empty and the actual Price value if it’s not empty.

[Michael Harding]

Hi Sam, thanks so much. I don’t see IFEMPTY() in my list of functions, so I have tried writing it in. I’ve tried using the other IFEMPTY commands which are there and they seem to have no effect on things.

But I have discovered an oddity: the only box which seems linked to the Total Calculation is the last one, entitled “Accessories Purchased.”

If I tab through the boxes (see screenshots enclosed) it goes straight to “Accessories Purchased.”

So it would appear that the calculation mode isn’t seeing the other entries, only “Accessories Purchased.”. I have tried dragging the field order to see if that has any effect (I’m trying everything…) and it didn’t.

SO: any thoughts out there? I have also tried re-writing the calculation and this behaved the same way as previously described.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

In your third screenshot, IFEMPTY is the second function listed? The IFEMPTY() is short hand for the normal form that takes three parameters, one being the field in question, the second being the default value if empty and the third being the value if not empty (generally the field’s value itself but in theory it could be something else). Using square brackets in place of the field bubbles, I’d expect your calculation to look like this:

IFEMPTY([Purchase Price];       0; [Purchase Price]) +
IFEMPTY([Shipping and postage];  0; [Shipping and postage]) +
IFEMPTY([Customs charges];       0; [Customs charges]) +
IFEMPTY([Repair costs];          0; [Repair costs]) +
IFEMPTY([Accessories purchased]; 0; [Accessories purchased])

It looks like you have an extra addition at the start of your formula, I’d aim to remove that first. One other item I do is to use Option+Enter to make new lines in the calculation. I try to group new lines by something that makes sense, sometimes that’s all of the operators are at the start of the line or that there is a new line after each new operator. That helps see if I’m missing an operator and also helps line everything up to visually see if something is missing.

The other debugging technique I go through with calculation fields is editing them one by one to make sure that it updates the way I expect. In this case, I’d make sure each of your fields have a different value so that you can see which ones are working or not and add one field at a time. A simple technique I’ve used is to change the magnitude in each field (e.g. one field has 1, the next has 10, the next has 100, etc) which makes it easy to spot which fields are there or are missing.

Apart from removing the addition operator at the very start, I don’t see anything else I’d say is wrong there. I have a very similar calculation myself with not dissimilar fields in a form and it works reasonable well.

[Brendan]

@Michael, to change the field tab order, click on the Tab Order button at the top of the layout editor. It’s the one that looks like -->|. That’ll display the tab order numbers. You can click on a tab order number, type in a new non-zero value, then press the Return key on your keyboard to set it. Click on it again to disable tab order edit mode. Then click the Layout button to disable Layout edit mode. Now when you tab, Tap Forms will follow the path you’ve defined by the tab order numbers. Dragging fields up and down won’t have any effect on the tab order.

[Michael Harding]

Hi Sam and Brendan,

Thanks so much for your kind attention and support in solving this problem I am having.

SS 1: Shows the entire acquisition and cost of sales involved in my process. I need first of all to establish the item cost (“TOTAL COST”), then the cost of sales (“Gross price achieved”) and finally the net profit (“NET PRICE ACHIEVED”)
SS 2.: Shows the equation I have now written following Sam’s advice. No doubt there is something really stupid in the process I have done, but it’s my stab at comprehending the process; it doesn’t work.
SS 3a. Show the second and third parts of the costing I do. Note that in this case, the formula I have written is working correctly. 3b. Shows the equation for same.
SS 4. Shows the final part, the net profit. Note that in this case, the formula I have written is working correctly.
SS 5. Shows the tab order, as per Brendan’s suggestion yesterday.

So then, where do I look to correct my error? Notice that the formula I wrote in the previous Tap Forms for gross and net profit works sweetly (SS 3 and 4) and yet doesn’t contain any “IF EMPTY” orders, even though I’ve never found it essential to put in placeholder values in the boxes. And of course, copying the same format in the first phase doesn’t work. Hmmm.

Once again, I thank you both so much for your guidance here.

Attachments:

You must be logged in to view attached files.

[Michael Harding]

SS 5. Herein

Attachments:

You must be logged in to view attached files.

[Michael Harding]

Hmmm. Actually, I think I must have scripted those equations initially in Bento, which explains perhaps why I didn’t recall scripting the IFEMPTY commands…

[Sam Moffatt]

Looking at 2, the first two lines are only IF not IFEMPTY. I think this would evaluate the first two as zero, Customs Charges is zero, Repair Costs is 27 and Accessories is 25 making 52.

Looking at 3, it looks like its actually working (Gross Sale Price [100] – eBay Fees [10] – Discount [10] – Stripe Fees [10] – Paypal Fees [10] == 60). You’re missing delivery from that calculation but it appears to be correct otherwise. This is an example where having different orders of magnitude would help:

– Gross Sale [100000] – Discount [10000] – Delivery [1000] – eBay Fees [100] – Stripe Fees [10] – PayPal Fees [1] = 88889
– Gross Sale [100000] – Discount [10000] – eBay Fees [100] – Stripe Fees [10] – PayPal Fees [1] = 89889

You can see we’re missing a field in the middle because there’s an extra 9 in there. This also works to help validate you’ve not doubled up on a field somewhere or similar. Once you’ve got your calculation up, it’s really easy to do (you just copy and paste each level and add a zero along the way) and gives you pretty quick feedback on what you might be missing.

Looking at 4, Gross Price [60] – Total Item Cost [52] == 8, it looks like in 3 the Net Price of 8 is correct.

I would look at the default layout and make sure everything is correct with your calculations there and then go back to your custom layout and make sure everything is hooked up correctly.

[Michael Harding]

Hi Sam. Again: many thanks for your help. It puzzles me that this formula once worked for me and continues to for me in one area of my form (the ‘discounting area’ versus the ‘unit cost’ calculation).

Nevertheless, I’ve spent hours trying to come to grips with it and did succeed in getting one cell to link to the total cost calculation result. Replicating the formula for other cells didn’t and doesn’t work.

I’ve looked everywhere and into everything to divine why this is so. Nothing seems to present itself and my analysis suggests that the problem isn’t so much the equation/scripting but an error somewhere else.

The cells are all correctly set up as number cells. They link in the correct order, as Brendan suggested doing a few days ago. But there must be something incorrect in the way Tap Forms is seeing the cells.

I have been through the various cells checking and checking and just don’t see where the problem lies.

[Michael Harding]

As previous, here are a couple of screenshots which show what’s happening.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Can you delete the total cost field and drag in the calculation field again from the layout inspector fields tab? I’m looking at 6a again and I’m wondering if the field under purchase price and total cost isn’t the same. On the custom layouts, you should drag in each field individually and not copy the layout items.

Playing with the custom layouts a little more, it might actually be helpful to display which field a layout item is hooked up. Clicking on a layout item will focus the layout in the layers tab but that doesn’t inherently have to match the name of the underlying field.

For me I usually leave everything at tab order zero and reorder all of the fields in the order I want and it seems to work properly.

[Brendan]

@Sam, Michael emailed me his file and there was an issue with the Total field on the custom layout. Deleting it and re-dragging it in fixed this issue. On the custom layout, Tap Forms thought the file was a Number field instead of a Calculation field. It was probably created as a Number field first, then dragged into the layout, then changed to be a Calculation field.

[Sam Moffatt]

Good to hear it’s all fixed :)

[Brendan]

Well I’m not sure if it is entirely yet because I haven’t heard back from Michael from my email response to him.

[Michael Harding]

Hi B & S and the rest of the community. Thanks to both of you for helping me out. The issue was one of user error and Brendan pointed out to me that the field type layer was incorrect. I’ve now changed that and Total Cost is now a calculation field, as I had intended, but had entered as a number type.

I can’t thank you both enough. I now understand the side of the programme much more completely.

[Sam Moffatt]

These things happen, we live and learn :) Happy to hear it’s all resolved :)

[Author]

Posts