You are viewing limited content. For full access, please sign in.

Question

Question

MAX Function in formula

Updated June 21, 2017
asked on June 20, 2017

I have a form that we developed for submitting PTO Requests.  It has worked well for months.  Now I have an issue that cropped up since I upgraded to Forms 10.2.1.  

I have a field, that is part of a table that adds up the upcoming PTO days that are pulled from a table that is populated from a lookup.  If there is populated data from the lookup there are no issues.  

However, if there is no data populated from the lookup then no data is placed in that field.  This causes another an error on submission.  A Laserfiche employee at the help center at the LF conference in February helped me do a formula that accounted for missing data and put a 0 in if there was no data from the lookup.  Here is the formula: =MAX(SUM(OpenPTORequests.Hours_Requested_1),0)  

This formula worked fine until the upgrade.  

Thoughts?

1 0

Replies

replied on June 20, 2017

Try this

=MAX(SUM(INDEX(OpenPTORequests.Hours_Requested_1,ROW())),0)  

0 0
replied on June 20, 2017

Thanks Steve,  sadly that formula does the same thing as the formula I was using.  If there is data in the table called Open PTO Requests (See first image) then it puts the total number of hours from all the lines into PTO Scheduled box.  If there is no data it leaves the PTO Scheduled box blank rather than putting in a 0.  It needs to be a 0 so I don't get a calculation error (second image).  Once that info is populated, the final number for PTO Available is calculated by subtracting the PTO Scheduled from PTO Unused so that the employee knows how much PTO they have left for the year after they take what is already scheduled.  This formula works great to calculate that AND will put in the 0 when the PTO Scheduled is blank.

 

=INDEX(Employee_Info.PTO_Unused,1)-MAX(INDEX(Employee_Info.Scheduled_PTO_Hours,1),0

I really don't need a zero in PTO Scheduled as much as I need it to ignore the calculation error when submitting the form.  However, I would like to get the calculation error fixed :).

 

I have been waiting for LF Chat support to pick up for 1.5 hours to try to get some help there, but no dice yet. 

0 0
View 3 previous replies
replied on June 20, 2017

In the meantime, have you thought about setting the Default value of the field to "0"

0 0
replied on June 20, 2017

I have tried that as well.  But the formula seems to override that 0.  As soon as the preview loads the field is blank.  If the lookup finds data it fills the field.  If I change the person's name to someone where there are no PTO days for lookup, the field just stays blank.

0 0
replied on June 21, 2017

Steve, Using your original formula I started working through some different options.  

 

Original Formula:

=MAX(SUM(INDEX(OpenPTORequests.Hours_Requested_1,ROW())),0)  

 

First try:

Removing the index reference

=MAX(SUM(OpenPTORequests.Hours_Requested_1,ROW()),0)

 

This returned a 1 instead of a 0 when there were no results from the data lookup.  It also adds a 1 to whatever was returned from the lookup.  8 hours of PTO became 9.  I am guessing this is because this is row one of the table.

 

Second Try:

Tried adding a -1 to compensate for the first-row number 1 being added above.

=MAX(SUM(OpenPTORequests.Hours_Requested_1,ROW())-1,0)

This worked fine on the screen, changing users the number and math worked.  That was until I submitted the form and got an error that the formula was invalid.  

 

Third & Final Try that worked:

I removed the Row() reference and the -1 and just explicitly placed a 0 where Row() was located.  

=MAX(SUM(OpenPTORequests.Hours_Requested_1,0),0)

 

Thanks for pointing me down a different path.  Much appreciated.

 

0 0
replied on June 21, 2017

Nevermind, it worked once, now it gives me the formula error.  Although the preview submission works correctly.  

Laserfiche Forms has encountered a problem.

'=MAX(SUM(OpenPTORequests.Hours_Requested_1,0),0)' is an invalid formula expression. [LFF1000-InvalidExpression]

 

I tried returning to the Row() verbiage and subtracting 1 from the row variable instead of the SUM.  I get the same behavior.  Preview submission works great, the numbers add up correctly when using the form.  Then on Submission, I get the same error with the new formula.

Laserfiche Forms has encountered a problem.

'=MAX(SUM(OpenPTORequests.Hours_Requested_1,ROW()-1),0)' is an invalid formula expression. [LFF1000-InvalidExpression]

 

0 0
replied on June 21, 2017

I was wondering how you had got 0 to work as the Row number. You can only use ROW() with the INDEX Command which basically means to use the value on the row being called. Otherwise, you just put the Row number after the Comma. ie: =MAX(SUM(OpenPTORequests.Hours_Requested_1,1),0)

So if you read the original formula they gave you: 

=MAX(SUM(OpenPTORequests.Hours_Requested_1),0)

It says to SUM all the Hour_Requested fields in the Table, and then choose between the result and 0, whichever is greater. Wondering if you could try an IF statement instead of MAX. If won't be as short, but maybe easier to control.

1 0
replied on June 21, 2017

Yeah, that is my issue, I can't get it to put in the 0 when the sum is null.  However, if I use your formula in a single line field down below, it works great (I guess since there is no row it returns 0).  I tried using the index function to put that field data into my table and it didn't do anything. 

 

I wonder what changed in 10.2.1 to make that original formula stop working.

 

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...