Question

Per Diem IF Statement in Forms 11 New Designer

Forms

Updated September 12, 2024

asked on September 9, 2024 • Show version history

I'm using Laserfiche 11 new designer. I have a table when a user does a single or combination selection then I would like a certain amount populate my Total field. I'm not sure if what I'm missing to make this work. Can I get some help?

PS. And if ALL three are selected then the total will be 59.

=IF(AND(PER_DIEM.mealType.M_1="Selected", PER_DIEM.mealType.M_2="Selected"), 33,IF(AND(PER_DIEM.mealType.M_1="Selected", PER_DIEM.mealType.M_3="Selected"), 39,IF(AND(PER_DIEM.mealType.M_2="Selected", PER_DIEM.mealType.M_3="Selected"), 46,IF(PER_DIEM.mealType.M_1="Selected", 13,IF(PER_DIEM.mealType.M_2="Selected", 20,IF(PER_DIEM.mealType.M_3="Selected", 26, "No valid choice"))))))

Per Diem.png (3.29 KB)

| Download

0 0

Answer

SELECTED ANSWER

replied on September 12, 2024

Makes sense given 0 is less than 7 and 12. Try this one. Note I am using 0 for defaults when one of each field is empty

IF(
   OR(
      INDEX(PER_DIEM.Departure_Time, ROW()) = "",
      INDEX(PER_DIEM.Return_Time, ROW()) = "",
   ),
   0,
   IF(
      AND(
         INDEX(PER_DIEM.Departure_Time, ROW()) * 24 <= 7,
         INDEX(PER_DIEM.Return_Time, ROW()) * 24 >= 19,
      ) 59,
      IF(
         AND(
            INDEX(PER_DIEM.Departure_Time, ROW()) * 24 <= 7,
            INDEX(PER_DIEM.Return_Time, ROW()) * 24 < 12
         ),
         13,
         IF(
            AND(
               INDEX(PER_DIEM.Departure_Time, ROW()) * 24 = 12,
               INDEX(PER_DIEM.Return_Time, ROW()) * 24 < 19
            ),
            20,
            IF(
               AND(
                  INDEX(PER_DIEM.Departure_Time, ROW()) * 24 > 12,
                  INDEX(PER_DIEM.Return_Time, ROW()) * 24 >= 19
               ),
               26,
               IF(
                  AND(
                     INDEX(PER_DIEM.Departure_Time, ROW()) * 24 <= 7,
                     INDEX(PER_DIEM.Return_Time, ROW()) * 24 = 12
                  ),
                  33,
                  IF(
                     AND(
                        INDEX(PER_DIEM.Departure_Time, ROW()) * 24 = 12,
                        INDEX(PER_DIEM.Return_Time, ROW()) * 24 >= 19
                     ),
                     46,
                     "No valid choice"
                  )
               )
            )
         )
      )
   )
)

0 0

Replies

replied on September 9, 2024

This looks like it is in a table, so you will have to use INDEX and ROW to get the proper formula value for each row of the table "INDEX(Checkbox.Choice_1, ROW())" and the value for a checkbox is TRUE or FALSE so that is wrong in your conditions. I put together what should be the formula but didn't test it (also included the 59 option)

= IF(
  AND(
    INDEX(PER_DIEM.mealType.M_1, ROW()) = TRUE,
    INDEX(PER_DIEM.mealType.M_2, ROW()) = TRUE,
    INDEX(PER_DIEM.mealType.M_3, ROW()) = TRUE
  ),
  59,
  IF(
    AND(
      INDEX(PER_DIEM.mealType.M_1, ROW()) = TRUE,
      INDEX(PER_DIEM.mealType.M_2, ROW()) = TRUE
    ),
    33,
    IF(
      AND(
        INDEX(PER_DIEM.mealType.M_1, ROW()) = TRUE,
        INDEX(PER_DIEM.mealType.M_3, ROW()) = TRUE
      ),
      39,
      IF(
        AND(
          INDEX(PER_DIEM.mealType.M_2, ROW()) = TRUE,
          INDEX(PER_DIEM.mealType.M_3, ROW()) = TRUE
        ),
        46,
        IF(
          INDEX(PER_DIEM.mealType.M_1, ROW()) = TRUE,
          13,
          IF(
            INDEX(PER_DIEM.mealType.M_2, ROW()) = TRUE,
            20,
            IF(
              INDEX(PER_DIEM.mealType.M_3, ROW()) = TRUE,
              26,
              "No valid choice"
            )
          )
        )
      )
    )
  )
)

1 0

replied on September 9, 2024 • Show version history

This works like a charm!!!!! Thanks a Million!!! I have to remember to use INDEX when using TABLES and ROW.

0 0

replied on September 11, 2024 • Show version history

I was thrown a curve ball. How would this be structured? Now this one is using time to figure out the per diem amount. Any help on this one will be greatly appreciated.

IF departure <= 7am AND return >= 7pm THEN

mealTotal = 59

ELSE IF departure <=7am AND return < 12pm THEN

mealTotal = 13

ELSE IF departure = 12pm AND return < 7pm THEN

mealTotal = 20

ELSE IF departure > 12pm AND return is => 7pm THEN

mealTotal = 26

ELSE IF departure <= 7am AND return is = 12pm THEN

mealTotal = 33

ELSE IF departure = 12pm AND return is => 7pm THEN

mealTotal = 46

Time base If statement.png (23.72 KB)

| Download

0 0

replied on September 12, 2024 • Show version history

I didn't really attempt to parse what your conditions are because you had a few that were "is =>" which I assumed to be ">=". Also wasn't sure what the variable names are for departure/return so I assumed they were default names. The key is that time fields return a decimal where multiplied by 24 gives you 24h time. Either way this formula should be easy enough to modify on your own.

IF(
   AND(
      INDEX(PER_DIEM.Departure_Time, ROW()) * 24 <= 7,
      INDEX(PER_DIEM.Return_Time, ROW()) * 24 >= 19,
   ) 59,
   IF(
      AND(
         INDEX(PER_DIEM.Departure_Time, ROW()) * 24 <= 7,
         INDEX(PER_DIEM.Return_Time, ROW()) * 24 < 12
      ),
      13,
      IF(
         AND(
            INDEX(PER_DIEM.Departure_Time, ROW()) * 24 = 12,
            INDEX(PER_DIEM.Return_Time, ROW()) * 24 < 19
         ),
         20,
         IF(
            AND(
               INDEX(PER_DIEM.Departure_Time, ROW()) * 24 > 12,
               INDEX(PER_DIEM.Return_Time, ROW()) * 24 >= 19
            ),
            26,
            IF(
               AND(
                  INDEX(PER_DIEM.Departure_Time, ROW()) * 24 <= 7,
                  INDEX(PER_DIEM.Return_Time, ROW()) * 24 = 12
               ),
               33,
               IF(
                  AND(
                     INDEX(PER_DIEM.Departure_Time, ROW()) * 24 = 12,
                     INDEX(PER_DIEM.Return_Time, ROW()) * 24 >= 19
                  ),
                  46,
                  "No valid choice"
               )
            )
         )
      )
   )
)

0 0

replied on September 12, 2024

First off, thank youuuuuu!!! I'm getting the results as seen on the pic. First row with Time I get blank. However, on the second row with blank Time I get 13. So I dissect the first IF statement but I get the same results.

Date5.png (16.37 KB)

| Download

0 0

replied on September 12, 2024

Thank YOUUUU

0 0

You are not allowed to follow up in this post.

Question

Question

Per Diem IF Statement in Forms 11 New Designer

Answer

Replies

Sign in to reply to this post.