Hi Sheila,

Here is the actual formula that I use to ensure nothing happens unless 2 fields are populated:

=IF(
OR(
INDEX(travDatesTable.depDateTime,1)="", 
INDEX(travDatesTable.retDateTime,1)=""
),
 
"",
 
MAX(0,
SUB(
DATEDIF(
DATEVALUE(TRUNC(INDEX(travDatesTable.depDateTime,1))), 
DATEVALUE(TRUNC(INDEX(travDatesTable.retDateTime,1))), 
"D"
),
1)
)

)

Here is more of a theoretical version of that for you:

=IF(

OR(
someField = "", 
someOtherField = ""
),
 
"",
 
doSomethingElse
)

The OR formula is pretty handy here. Hope this helps!!

I used the OR, but it didn't work for the radio button field. I checked that field for "", NULL, null, and a variety of other values. I also checked that field for each of the options (OT,CTE,STR) in the IF statement, but I got the same error message for each. I think the issue has to do with the radio button group not being set to a value. If there's something like a "IS SET" type of comparison operator, then maybe an IF statement will work. Let me know if you find a formula that works.

In the formula that Susan is using, she has a radio button in the table row.

=IF(

INDEX(
Group_OT_Details_Post.OT_Type_Post,ROW()
)="STR",

.....

^It is the "OT_Type_Post" field. Show me your code and I'll take a crack at it.

I believe the radio button is throwing an error if a value isn't selected, causing an error  in the Hours Earned field. There are a few options that I could find to get this to work: 1) You can add another radio button option that says "None" and make it the default and account for it in the calculation. 2) You can use a dropdown list instead of a radio button with a "None" or "?" option as the default and account for it in the calculation, or 3) you can add an extra hidden field to your table that gets the value of the selection and use that field in the calculation.

If you choose options 1 or 2 and set the values of the options to 1,2,3,4, you can use this formula (make sure the default option is first):

=CHOOSE(
INDEX(Group_OT_Details_Post.OT_Type_Post,ROW()),
"",
VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))*1.5,
VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))*1.5,
VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))
)

If you choose option 3 above and set the values of the options to 1,2,3, you can use this formula for the hidden multiplier field:

=CHOOSE(
INDEX(Group_OT_Details_Post.OT_Type_Post,ROW()),
1.5,
1.5,
1
)

and this formula for the Hours Earned field:

=PRODUCT(
VALUE(INDEX(Group_OT_Details_Post.Multiplier,ROW())),
VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))
)

I could use some clarifications on your formulas. See the comment lines in the code below.

=CHOOSE(
INDEX(Group_OT_Details_Post.OT_Type_Post,ROW()), //this is a string and needs to be a number
"", //why is this null? Looks like a leftover from an IF formula
VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))*1.5,  //no need for the VALUE when the result is a number
VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))*1.5,
VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))
)

=CHOOSE(
INDEX(Group_OT_Details_Post.OT_Type_Post,ROW()), //this will return a string, need a number
1.5,  //if the options are 1,2,3 then the below values won't match
1.5,
1
)

=PRODUCT(
VALUE(INDEX(Group_OT_Details_Post.Multiplier,ROW())), //The multiplier is defined by a string in OT_Type_Post
VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))
)

In the mean time, I am thinking that Susan could have made the "OT Type" radio button (which is variable "OT_Type_Post") assigning the values of 1.5, 1, etc as the value.

I may not have the values exactly right, but you should get the idea.

Hi, Chris. I tested all the formulas I posted.

The first formula you referenced is correct if Susan puts a choice like "None" as the first option, meaning the value still needs to be selected by the user. Without using VALUE, I received an error. Maybe there's another way to structure this?

In the second formula you referenced, the value of the radio button chosen dictates which of the multipliers to use. It's the index number of the 3 values that are separated by commas. So, if I choose OT whose value is 1, it will get the first number in the list. And it doesn't need to be converted it to a number. It works as is.

The third formula you referenced uses a hidden text field to store the multiplier in. You can't store it as a value in the radio button because two of the values are identical. Forms will automatically put a "_1" after the second instance of a value. In your example above, the value for CTE would be changed to 1.5_1. 

Got it. Thanks for the clarifications. One way to wait until there is a value in a field before running the formula:

=IF(
OR(
thisField="", 
thatField=""
),
 
"",

runThisFormula
)

^IF thisField is null OR thatField is null do nothing ELSE runThisFormula

Could you use this to test if the radio button in null or not before running your formula?

I actually tried that, but like Susan found, if a choice isn't selected from the radio button first, an error is thrown. If there was a way to capture the error and process based on that, then we'd be in business.

Ah ha. I think I see the issue. Here is what you proposed to Susan at the beginning:

=IF(
INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW())="STR",

INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()),

VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))*1.5
)

The issue is that the formulas can't multiple using the asterisk:

VALUE(INDEX(Group_OT_Details_Post.OT_Hours_Worked_Post,ROW()))*1.5 <-----

It needs to use MULT(for only 2 values) or PRODUCT(for multiple values) to actually to the multiplying. That one big difference between the Forms Formulas and Excel.

Hope this helps!

Chris, the asterisk does work for multiplying in Forms calculation formulas. The only issue with any of the formulas I posted is that if the user enters the number of hours before selecting the type, an error will be shown until the type is selected. Using PRODUCT or MULT doesn't make a difference in the behavior.