LinkedIn
Twitter
Good Afternoon All,
I’m having a little trouble wrapping my head around something, I know its probably simple but whatever, not for me right now for some reason,
Here’s is the Problem,
I have a Supplier Table & Product Table.
Supplier Table
Products Table
Product Table is a Child to Supplier Table.
I have a Daily Report(s)
They will send Alerts, and emails etc.
Now I only want to send an email if any of these statements are true. These are just some of them, there are allot more, I just need a concept to figure this out and I will do the rest myself 
This is directly from my Email Report/Workflow
<<If: (Or(And([Product Specifications UTD] <> "Green",[Product Specifications UTD] <> "Blue"),And([Allergen Statements UTD] <> "Green",[Allergen Statements UTD] <> "Blue"),And([GRAS Statement UTD] <> "Green",[GRAS Statement UTD] <> "Blue"),And([Organic Certificate UTD] <> "Green",[Organic Certificate UTD] <> "Blue"),And([Gluten Free Certificate UTD] <> "Green",[Gluten Free Certificate UTD] <> "Blue"),And([NON GMO Statement UPD] <> "Green",[NON GMO Statement UPD] <> "Blue"),And([Kosher Certificate UTD] <> "Green",[Kosher Certificate UTD] <> "Blue"),And([Radiological Statement UTD] <> "Green",[Radiological Statement UTD] <>"Blue")))>>
The Above is all in my Products Table. If anyone of these products has any Documentation that is not Green or Blue, I need the Report to Fire.
How Do I Iterate through the products to See if this is the case?
Any Help would be appreciated,
Thanks,
Solved
1 ACCEPTED SOLUTION
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.
Green = Good
Blue = Document not required
Red = expired,
Yellow = Expiry within the next 2 weeks
Black = No Documentation
I also Have columns for each document for [Last Requested on] if a document has already been requested within the past 2 weeks than it should not count towards being true for sending the report. I could easily implement this myself once i know how
wow, such a complex thing in my head with such a simple solution.
So the above outputs a list.
Am I correct to do this?
Count(INTERSECT( LIST("Red", "Yellow", "Black"),
[Related Products][Product Specifications UTD] +
[Related Products][Allergen Statements UTD]
))>0
Thanks
Also @Bahbus I know I should of made it more clear from the start. I’m sorry for that. I’ve learned a ton already. The above has humbled me. Appsheet is insane. I know you’ve put a ton of brain power into the intercepts and all, I’m just not sure how I would do this. I’m not one to ask something where I kinda already know the solution to as I have no idea… 
I know this
- I need to check Supplier documents and Last requested Dates for each Document.
- Supplier document needs to be expired or in need of attention as well as the Last Requested Date needs to be Longer than 2 weeks ago. I think I have that in the formula above.
The Kicker
3. Each document of each related product needs to be checked for the same thing above and the requested date needs to be longer than 2 weeks Ago. But I need to compare each Document for the Color as well as the Last Requested Date (LR) but this must match up. Intersect checks a list of items, Not sure how I would make sure Im checking to make sure Im comparing the Last Requested Date of the same Documentation, as these are also in list format .
Is it possible to do with the layout I have?
I say this as the App has probably been used for about 4-5 months already and Its completely Full of Data, documents etc, and the annual BRC audit is coming up in November.
My Layout is as Follows
Supplier Table
Supplier Email Table - Child of supplier
Supplier Products Table - Child of supplier
Proposition 65 Table - Child of Supplier
Third Party Certificates - Child of supplier
Products Table - Child of supplier
Product Specification Table - Child of Products
Allergen Statements Table - Child of Products
Gras Statements Table - Child of Products
NON GMO Table - Child of Products
Radiology Table - Child of Products
Gluten Free Table - Child of Products
Organic Table - Child of Products
Kosher Table - Child of Products
Lot Number Table - Child of Products
COA Table - child of Lot Numbers
Is it possible to make this work with what you are saying but than I would just need to do it 8 times?
Each of my Documentation Tables also have a Column [Currant]. If a new certificate is put in under organic for a product, the user sets the new one as currant and the old one as not currant so all my formulas ignore these old ones when looking but all documentation must be kept in the system.
Lets Say I had only 1 certificate type and it was a child of products. I’m ok with making a massive formula to make this work long as I get a start ill be hooked 
Thanks Steve, That would be good
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)
-
[Third Party Certificates UTD] is either Red, Yellow or black and [LR Third Party Certificate] is longer than 2 weeks ago.
-
[Proposition 65 UTD] is either Red, Yellow or black and [LR Proposition 65] is longer than 2 weeks ago.
-
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,
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.
Unbelievable. Yes I will start Citing Problems more in Detail going forward
@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 
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
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))
)
)
)
)
)
)
