Question

Using Lookup to assign Formula for Field

Forms How To

Updated March 12, 2021

asked on March 12, 2021

I have a very unique process that I am struggling with completing. I have a reimbursement process that contains an amount field for each line in a PO. The amount field is calculated differently for each line item. To avoid creating multiple amount fields for each line or creating an extensive formula I decided to try using a table to hold the formulas and use a lookup to assign the formula for each amount column based on the PO number.

In my sample it has partially worked so far but for some reason I am only getting the formula input into the field as text but the formula is not actually be treated as a formula.

First question I have is whether or not my theory is an actual possible thing with Forms (Using a lookup to fill a field and then using that field itself as the formula. ex.
=formulavariable".

If I am barking up a possible tree, what am I doing incorrectly in my formula?

0 0

Answer

SELECTED ANSWER

replied on March 12, 2021

Right, for the formula to work, it must be in the calculations input in the field settings. You can't fill a field with the formula.

You could try something like:

Create a field with each different formula you have

Formula1: =IF(...)
Formula2:=IF(...something else)

and hide them all. Also create a hidden field for WhichFormulaToUse

You can use a lookup with your Fields as inputs and have it fill WhichFormulaToUse with a number 1, 2...

Then use a field rule: Show Formula1 if WhichFormula = 1
Show Formula2 if WhichFormula = 2...

So you are creating a field with each possible formula, then using a lookup to determine which to show. However, you at that point, you probably don't need a lookup at all, you could just use more calculations to determine which formula field to show.

1 0

Replies

replied on March 12, 2021

You can fill Field_A via lookup then use Field_A in a formula

=Field_A*2 will fill another field with double Field A and update as the lookup results return. You cannot fill a field with the formula itself. If your lookup returned "=Field_A*2", that wouldn't work.

0 0

replied on March 12, 2021

Hi Jared, thanks for the heads up. So a more detailed version of my example is below

Field A

Field B

Field C

Field D

Amount

Formula Field (Filled by look-up)

I have formulas stored in my table (ex. =IF((Field A * Field B + Field C)<=Field D),Field A * Field C,0)

This what is filled into the "Formula Field" field. I am then , inside the formula for the "Amount" field.

Are saying that this will not work? Sorry just want to make sure we are both on the same page of what I am trying to do and what your response is.

0 0

SELECTED ANSWER

replied on March 12, 2021

Right, for the formula to work, it must be in the calculations input in the field settings. You can't fill a field with the formula.

You could try something like:

Create a field with each different formula you have

Formula1: =IF(...)
Formula2:=IF(...something else)

and hide them all. Also create a hidden field for WhichFormulaToUse

You can use a lookup with your Fields as inputs and have it fill WhichFormulaToUse with a number 1, 2...

Then use a field rule: Show Formula1 if WhichFormula = 1
Show Formula2 if WhichFormula = 2...

1 0

replied on March 12, 2021 • Show version history

Even updating the formulas in Javascript would be extremely difficult as the formulas are not stored as field attributes or anything like that.

@████████'s recommendation of having a version of the field with each possible formula, and using Field rules to show/hide the fields as needed is going to be your cleanest option. You could do it all in Javascript with a single field, using the code to determine which calculation to process (and doing the calculations in Javascript), but the idea is the same.

1 0

You are not allowed to follow up in this post.

Question

Question

Using Lookup to assign Formula for Field

Answer

Replies

Sign in to reply to this post.