Question

Formula use and potential user entry

Forms

Updated March 21, 2024

asked on March 13, 2024

I have a table where it is possible to select a standard item with standard amounts. If a user selects an item with no receipt, standard Subtotals will populate.

There are also non-standard items in the list, which I would like the user to enter the Subtotal.

I used the following formula to calculate the Subtotal.

=IF(INDEX(Expenses.ExpenseReason,ROW())="Breakfast - No Receipt",14.29,IF(INDEX(Expenses.ExpenseReason,ROW())="Lunch - No Receipt",14.29,IF(INDEX(Expenses.ExpenseReason,ROW())="Dinner - No Receipt",23.81,)))

I would like the user to be able to enter the Subtotal in manually if they select one of the other options which have a receipt (not listed in the formula above). They are currently able to enter the items manually, but the subtotals get removed with the addition of the next row when the formula calculates again. Any way to get the manually entered amount to stay put, other than read only coding options (in case user entered info incorrectly, they need to be able to change it).

There is no way to do a circular reference, so I cannot do that. The only other option I can think of, but would like to avoid, is to add another table for items that have standard amounts (no receipts).

I am not a coder, so I would like to use formulas if possible. Otherwise, I would need additional help.

0 0

Replies

replied on March 13, 2024

Are you working in cloud? We are currently fixing a few issues involving formulas in tables that sounds like the core of your issue.

1 0

replied on March 13, 2024

No. I am not in the Cloud version.

1 0

replied on March 13, 2024

I am currently coordinating the next Forms hotfix, I will round up these bugs and make sure they are scheduled for self-hosted

0 0

replied on March 13, 2024

I would just make two copies of the Subtotal field and set a field rule to hide one and show the other based on the selected option (and set it to ignore the field contents when hidden). One field has the formula you are using (and maybe is read only) and the other field has no formula as is editable by the user. The formula for the Total includes both Subtotal fields.

0 0

View 3 previous replies

replied on March 13, 2024

This solution will probably work, but I may have to add an additional field to combine them for reporting. We pull reports and feed them directly into our financial software, so I cannot pull both fields. Well, I can, but it involves editing. I'd like to avoid that.

From your solution, I think the only option is to add a third additonal field (also hidden) that I could to collect information from the filled field, whether it be from the standard item field or the other field.

This could get cumbersome, if I have to do it to too many calculated fields. So, perhaps it may be easier to do a separate table. I will have to think on it.

I hope that made sense. It sounds a bit confusing.

0 0

replied on March 13, 2024

I understand the feeling!
With field rules to show/hide the stuff, it may feel cumbersome to set-up, but might not be too bad once it's actually set-up.
Maybe make a copy of your form to test it out first and see how it looks and feels?

0 0

replied on March 21, 2024 • Show version history

So, I have tried this and it is not going to work, because it holds the category titles and keeps it columnized. it will confuse end users.

Oh, well. It was worth a shot.

I think I will just make a separate table for items that have no receipts and just reconfigure all my related calculations throughout my form.

0 0

replied on March 21, 2024 • Show version history

Oh, I'm sorry - I forgot tables would do that.

I have done this kind of stuff before in the Classic Designer - but when I checked how I set those up, I have them in collections not in tables - with CSS to make them look more like they would in a table. Sorry.

0 0

replied on March 21, 2024

No problem.

Thanks for the suggestion.

0 0

replied on March 21, 2024

Are you able to use lookups to populate the dropdown/subtotal fields? It may be slightly suboptimal, but then you have the added benefit of using the values in other forms/processes.

0 0

You are not allowed to follow up in this post.

Question

Question

Formula use and potential user entry

Replies

Sign in to reply to this post.