Expression Big Brain support

You need to completely and clearly articulate the problem as it stands now. Don’t speculate on what might be needed. Don’t bother explaining what hasn’t worked. Don’t refer to the previous posts. What do you need done? How is your data structured?

2 Likes

Thanks Steve, That would be good :slight_smile:

Im looking for some help resolving an expression Problem.

First my Structure.

Relevant Tables & Relationships

Supplier Table - Parent
Products Table - Child of supplier

Supplier Table Relevant Columns

id (Key)

Related Products (Virtual)

LR Third Party Certificate (Date)
LR Proposition 65 (Date)

Third Party Certificates UTD (Color)
Proposition 65 UTD (Color)

Products Table Relevant Columns

id (Key)

LR Product Specifications (Date)
LR Allergen Statement (Date)
LR GRAS Statements (Date)
LR Organic Certificate (Date)
LR Gluten Free Statement (Date)
LR NON GMO Statement(Date)
LR Kosher Statement (Date)
LR Radiological Statement(Date)

Product Specifications UTD (Color)
Allergen Statements UTD (Color)
GRAS Statement UTD (Color)
Organic Certificate UTD (Color)
Gluten Free Certificate UTD (Color)
NON GMO Statement UPD (Color)
Kosher Certificate UTD (Color)
Radiological Statement UTD (Color)

Date and Color Column (Type) Details

Date columns

Date Columns contain the Date a report was last triggered for this particular item.
Date Columns may be Blank, if so a report has never been requested

Color Columns

Color Columns contain 1 of the following Colors

Blue (Document not Necessary)
Green (Document Up To Date)
Yellow (Document Expiring within the next 14 Days
Red (Document Expired)
Black (No Document)

Now the Expression Problem Part 1.

Summary

Every Day I want to trigger a Report to fire for every row of the Supplier table.

I would want it to fire if ANY of the following conditions is true.
(Note if Date is Blank it is longer than 2 weeks ago)

  1. [Third Party Certificates UTD] is either Red, Yellow or black and [LR Third Party Certificate] is longer than 2 weeks ago.

  2. [Proposition 65 UTD] is either Red, Yellow or black and [LR Proposition 65] is longer than 2 weeks ago.

  3. If any of the following is True for any Product Related to the Supplier.

    A). [Product Specifications UTD] is either Red, Yellow or black and [LR Product Specifications] is longer than 2 weeks ago.

    B). [Allergen Statements UTD] is either Red, Yellow or black and [LR Allergen Statement] is longer than 2 weeks ago.

    C). [GRAS Statement UTD] is either Red, Yellow or black and [LR GRAS Statements] is longer than 2 weeks ago.

    D). [Organic Certificate UTD] is either Red, Yellow or black and [LR Organic Certificate] is longer than 2 weeks ago.

    E). [Gluten Free Certificate UTD] is either Red, Yellow or black and [LR Gluten Free Statement] is longer than 2 weeks ago.

    F). [NON GMO Statement UPD] is either Red, Yellow or black and [LR NON GMO Statement] is longer than 2 weeks ago.

    G). [Kosher Certificate UTD] is either Red, Yellow or black and [LR Kosher Statement] is longer than 2 weeks ago.

    H). [Radiological Statement UTD] is either Red, Yellow or black and [LR Radiological Statement] is longer than 2 weeks ago.

Expression Problem Part 2

May be able to get from Part 1.

Summary

If a Report was Triggered due to a Product or Supplier Issue, The Last Requested Date for the document must be set in the Products/Suppliers Table under the Correlating (Last Requested Document Type (Date)). (If 2 documents Triggered Report, both LR Dates need to be set. )

[LR Product Specifications] = Today() If report was triggered due to this
[LR Allergen Statement] = Today() If report was triggered due to this.

For This I’m thinking I will need to have an action tied to the report that Fires Multiple Actions. 10 in total, 1 for each possible reason. Each action will set the Date of the Last Requested column for the correlating document. The Action Will only be available for rows that meet the same criteria that caused the report to fire in the first place.

I’m thinking I should be able to get part 2 solution from Part 1 solution.

Thanks for you time,

1 Like

Meaning you want one report per supplier that matches the condition? Or that you want one report that includes only those suppliers that match? Or that you want a report that includes all suppliers if at least one supplier matches?

1 Like

It would be one report per Supplier. My Report Will be sending an email to the specified supplier requesting the documentation that needs attention for each product. My Template will take care of that :smiley:

1 Like

Thank you for this outstanding problem description! Exactly what I was hoping for!

Suggestion forthcoming.

1 Like

Problem 1:

Condition expression:

OR(
  AND(
    IN([Third Party Certificates UTD], {"Red", "Yellow", "Black"}),
    OR(
      ISBLANK([LR Third Party Certificate]),
      ([LR Third Party Certificate] < (TODAY() - 14))
    )
  ),
  AND(
    IN([Proposition 65 UTD], {"Red", "Yellow", "Black"}),
    OR(
      ISBLANK([LR Proposition 65]),
      ([LR Proposition 65] <= (TODAY() - 14))
    )
  ),
  OR(
    ISNOTBLANK(
      SELECT(
        [Related Products][_ROWNUMBER],
        OR(
          AND(
            IN([Product Specifications UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Product Specifications]),
              ([LR Product Specifications] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Allergen Statements UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Allergen Statement]),
              ([LR Allergen Statement] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([GRAS Statement UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR GRAS Statements]),
              ([LR GRAS Statements] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Organic Certificate UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Organic Certificate]),
              ([LR Organic Certificate] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Gluten Free Certificate UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Gluten Free Statement]),
              ([LR Gluten Free Statement] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([NON GMO Statement UPD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR NON GMO Statement]),
              ([LR NON GMO Statement] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Kosher Certificate UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Kosher Statement]),
              ([LR Kosher Statement] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Radiological Statement UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Radiological Statement]),
              ([LR Radiological Statement] <= (TODAY() - 14))
            )
          )
        )
      )
    )
  )
)

Note how closely the expression matches the problem statement you put together for me.

Let’s see if that works before working on problem 2. Lemme know.

2 Likes

Unbelievable. Yes I will start Citing Problems more in Detail going forward :wink:

@Steve This is a huge formula.

Im getting the following Error.

Condition OR(NOT(ISBLANK(SELECT([Related Products][_RowNumber],OR(AND(IN([Product Specifications UTD],LIST(“Red”,“Yellow”,“Black”)), OR(ISBLANK([LR Product Specifications]), ([LR Product Specifications] <= (TODAY()-14)))), AND(IN([Allergen Statements UTD],LIST(“Red”,“Yellow”,“Black”)), OR(ISBLANK([LR Allergen Statement]), ([LR Allergen Statement] <= (TODAY()-14)))), AND(IN([GRAS Statement UTD],LIST(“Red”,“Yellow”,“Black”)), OR(ISBLANK([LR GRAS Statements]), ([LR GRAS Statements] <= (TODAY()-14)))), AND(IN([Organic Certificate UTD],LIST(“Red”,“Yellow”,“Black”)), OR(ISBLANK([LR Organic Certificate]), ([LR Organic Certificate] <= (TODAY()-14)))), AND(IN([Gluten Free Certificate UTD],LIST(“Red”,“Yellow”,“Black”)), OR(ISBLANK([LR Gluten Free Statement]), ([LR Gluten Free Statement] <= (TODAY()-14)))), AND(IN([NON GMO Statement UPD],LIST(“Red”,“Yellow”,“Black”)), OR(ISBLANK([LR NON GMO Statement]), ([LR NON GMO Statement] <= (TODAY()-14)))), AND(IN([Kosher Certificate UTD],LIST(“Red”,“Yellow”,“Black”)), OR(ISBLANK([LR Kosher Statement]), ([LR Kosher Statement] <= (TODAY()-14)))), AND(IN([Radiological Statement UTD],LIST(“Red”,“Yellow”,“Black”)), OR(ISBLANK([LR Radiological Statement]), ([LR Radiological Statement] <= (TODAY()-14))))))))) has an invalid structure: at least 2 subexpression(s) required

Thanks Steve :smiley:

So I’ve Removed an Or() Expression to make the expression Valid.

Will the expression still perform as intended?

See below, Going to do test now :slight_smile:

OR(
  AND(
    IN([Third Party Certificates UTD], {"Red", "Yellow", "Black"}),
    OR(
      ISBLANK([LR Third Party Certificate]),
      ([LR Third Party Certificate] < (TODAY() - 14))
    )
  ),
  AND(
    IN([Proposition 65 UTD], {"Red", "Yellow", "Black"}),
    OR(
      ISBLANK([LR Proposition 65]),
      ([LR Proposition 65] <= (TODAY() - 14))
    )
  ),
  
    ISNOTBLANK(
      SELECT(
        [Related Products][_ROWNUMBER],
        OR(
          AND(
            IN([Product Specifications UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Product Specifications]),
              ([LR Product Specifications] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Allergen Statements UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Allergen Statement]),
              ([LR Allergen Statement] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([GRAS Statement UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR GRAS Statements]),
              ([LR GRAS Statements] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Organic Certificate UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Organic Certificate]),
              ([LR Organic Certificate] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Gluten Free Certificate UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Gluten Free Statement]),
              ([LR Gluten Free Statement] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([NON GMO Statement UPD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR NON GMO Statement]),
              ([LR NON GMO Statement] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Kosher Certificate UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Kosher Statement]),
              ([LR Kosher Statement] <= (TODAY() - 14))
            )
          ),
          AND(
            IN([Radiological Statement UTD], {"Red", "Yellow", "Black"}),
            OR(
              ISBLANK([LR Radiological Statement]),
              ([LR Radiological Statement] <= (TODAY() - 14))
            )
          )
        )
      )
    )
)
1 Like

@Steve

I’ve Just Testing it with different Product Certificates, Expired, not expired, expired but already requested within the past 2 weeks etc,

Everything Seems to be working perfectly…

I can honestly say I don’t think I would of been able to figure this out on my own…
Humbling.

Great Appsheet support

2 Likes

Yep, my oversight. Sorry!

Don’t sell yourself short. They key was the well-structured problem description you provided. Just about every part of the final expression maps directly to the exact text of the description. The only part I’d consider “advanced” is the use of ISNOTBLANK(SELECT([Related Products][_ROWNUMBER], ...)).

3 Likes

Both steps to the problem are Fully solved and the entire system is officially Functioning.

Thanks to @Steve and @Bahbus for all the help :smiling_face_with_three_hearts:

Talk soon :slight_smile:

3 Likes