If Condition problem

anonymous · September 19, 2020, 10:06pm

Hello Everyone,

So I thought I had solved a problem in a previous post. Turns out not so fast.
See post here for referance. Stumped on Workflow. Condition Based on List Index

Here is my expression.

<<If: Count([Related Products])>0>>

<<Start: [Related Products]>>

<<If: (Or(And([Product Specifications UTD] <> "Green",[Product Specifications UTD] <> "Blue", OR([LR Product Specifications] < (TODAY() - 14),isblank([LR Product Specifications]))),And([Allergen Statements UTD] <> "Green",[Allergen Statements UTD] <> "Blue",OR([LR Allergen Statement] < (TODAY() - 14),isblank([LR Allergen Statement]))),And([GRAS Statement UTD] <> "Green",[GRAS Statement UTD] <> "Blue",OR([LR GRAS Statements] < (TODAY() - 14),isblank([LR GRAS Statements]))),And([Organic Certificate UTD] <> "Green",[Organic Certificate UTD] <> "Blue",OR([LR Organic Certificate] < (TODAY() - 14),isblank([LR Organic Certificate]))),And([Gluten Free Certificate UTD] <> "Green",[Gluten Free Certificate UTD] <> "Blue",OR([LR Gluten Free Statement] < (TODAY() - 14),isblank([LR Gluten Free Statement]))),And([NON GMO Statement UPD] <> "Green",[NON GMO Statement UPD] <> "Blue",OR([LR NON GMO Statement] < (TODAY() - 14),isblank([LR NON GMO Statement]))),And([Kosher Certificate UTD] <> "Green",[Kosher Certificate UTD] <> "Blue",OR([LR Kosher Statement] < (TODAY() - 14),isblank([LR Kosher Statement]))),And([Radiological Statement UTD] <> "Green",[Radiological Statement UTD] <>"Blue",OR([LR Radiological Statement] < (TODAY() - 14),isblank([LR Radiological Statement])))))>>

<<IF:(INDEX([Related Products],1) = [ID])>>

Product Documentation

<<EndIF>>

What Im looking to do, is only write Product documentation if there is a product that needs documentation, and only write it once.

The above works, and only writes it once, as long as the first product from the list and possible others of [products] needs documentation…

How would one go about this?

Thanks,

anonymous · September 20, 2020, 12:16am

Jonathan_S:

as long as the first product from the list and possible others of [products] needs documentation…

This confused me. Now, granted, I am *ahem* not at 100% brain capacity due to the inhalation of not normal air, but everything works, but only if the first one needs docs? It doesn’t work if #2 needs docs but #1 doesn’t? Is that the problem? Or…something else.

Please elaborate further.

anonymous · September 20, 2020, 12:26am

lol,

Correct,

So I am sending a report of each product that needs product documentation

What I am trying to do is:

If there is 1 or more products that need product documentation
Write: Product Documentation
Otherwise do nothing

Currently, If the first product doesn’t need documentation, but other do, this condition wont fire.

Now I don’t want to write “Product Documentation” for each product that needs it, only once if there are products that need it.

See the full Template here, may help:)

<<START:FILTER( Settings , isnotblank([Custom Greeting (Based on 9:00am Sending Time)]))>>

<<[Custom Greeting (Based on 9:00am Sending Time)]>>

<<END>>

<<IF: OR(and([Third Party Certificates UTD] <> "Green",[Third Party Certificates UTD] <> "Blue",OR([LR Third Party Certificate] < (TODAY() - 14),isblank([LR Third Party Certificate]))),and([Proposition 65 UTD] <> "Green",[Proposition 65 UTD] <> "Blue",OR([LR Proposition 65] < (TODAY() - 14),isblank([LR Proposition 65]))))>>

Supplier Documentation

<<IF:and([Third Party Certificates UTD] <> "Green",[Third Party Certificates UTD] <> "Blue",OR([LR Third Party Certificate] < (TODAY() - 14),isblank([LR Third Party Certificate])))>>

* Third Party Audit Documentation

<<EndIF>>

<<IF:and([Proposition 65 UTD] <> "Green",[Proposition 65 UTD] <> "Blue",OR([LR Proposition 65] < (TODAY() - 14),isblank([LR Proposition 65])))>>

* Proposition 65 Documents

<<EndIF>>

<<EndIF>>

<<If: Count([Related Products])>0>>

<<Start: [Related Products]>>

<<If: (Or(And([Product Specifications UTD] <> "Green",[Product Specifications UTD] <> "Blue", OR([LR Product Specifications] < (TODAY() - 14),isblank([LR Product Specifications]))),And([Allergen Statements UTD] <> "Green",[Allergen Statements UTD] <> "Blue",OR([LR Allergen Statement] < (TODAY() - 14),isblank([LR Allergen Statement]))),And([GRAS Statement UTD] <> "Green",[GRAS Statement UTD] <> "Blue",OR([LR GRAS Statements] < (TODAY() - 14),isblank([LR GRAS Statements]))),And([Organic Certificate UTD] <> "Green",[Organic Certificate UTD] <> "Blue",OR([LR Organic Certificate] < (TODAY() - 14),isblank([LR Organic Certificate]))),And([Gluten Free Certificate UTD] <> "Green",[Gluten Free Certificate UTD] <> "Blue",OR([LR Gluten Free Statement] < (TODAY() - 14),isblank([LR Gluten Free Statement]))),And([NON GMO Statement UPD] <> "Green",[NON GMO Statement UPD] <> "Blue",OR([LR NON GMO Statement] < (TODAY() - 14),isblank([LR NON GMO Statement]))),And([Kosher Certificate UTD] <> "Green",[Kosher Certificate UTD] <> "Blue",OR([LR Kosher Statement] < (TODAY() - 14),isblank([LR Kosher Statement]))),And([Radiological Statement UTD] <> "Green",[Radiological Statement UTD] <>"Blue",OR([LR Radiological Statement] < (TODAY() - 14),isblank([LR Radiological Statement])))))>>

<<IF:(INDEX([Related Products],1) = [ID])>>

Product Documentation

<<EndIF>>

Product Name: <<[Product Name]>>

<<IF:(and([Product Specifications UTD] <> "Green",[Product Specifications UTD] <>"Blue",OR([LR Product Specifications] < (TODAY() - 14),isblank([LR Product Specifications]))))>>

* Product Specifications

<<EndIF>>

<<IF:(and([Allergen Statements UTD] <> "Green",[Allergen Statements UTD] <>"Blue",OR([LR Allergen Statement] < (TODAY() - 14),isblank([LR Allergen Statement]))))>>

* Allergen Statement

<<EndIF>>

<<IF:(and([GRAS Statement UTD] <> "Green",[GRAS Statement UTD] <> "Blue",OR([LR GRAS Statements] < (TODAY() - 14),isblank([LR GRAS Statements]))))>>

* GRAS Statement

<<EndIF>>

<<IF:(and([Organic Certificate UTD] <> "Green",[Organic Certificate UTD] <> "Blue",OR([LR Organic Certificate] < (TODAY() - 14),isblank([LR Organic Certificate]))))>>

* Organic Certificate

<<EndIF>>

<<IF:(and([Gluten Free Certificate UTD] <> "Green",[Gluten Free Certificate UTD] <>"Blue",OR([LR Gluten Free Statement] < (TODAY() - 14),isblank([LR Gluten Free Statement]))))>>

* Gluten Free Certificate

<<EndIF>>

<<IF:(and([NON GMO Statement UPD] <> "Green",[NON GMO Statement UPD] <>"Blue",OR([LR NON GMO Statement] < (TODAY() - 14),isblank([LR NON GMO Statement]))))>>

* NON GMO Statement

<<EndIF>>

<<IF:(and([Kosher Certificate UTD] <> "Green",[Kosher Certificate UTD] <> "Blue",OR([LR Kosher Statement] < (TODAY() - 14),isblank([LR Kosher Statement]))))>>

* Kosher Certificate

<<EndIF>>

<<IF:(and([Radiological Statement UTD] <> "Green",[Radiological Statement UTD] <>"Blue",OR([LR Radiological Statement] < (TODAY() - 14),isblank([LR Radiological Statement]))))>>

* Radiological Statement

<<EndIF>>

<<EndIF>>

<<End>>

<<EndIF>>

anonymous · September 20, 2020, 12:48am

<<IF:(INDEX([Related Products],1) = [ID])>>

Product Documentation

<<EndIF>>

Try:

<<IF:(COUNT(INTERSECT({"Red", "Yellow", "Black"}, [Related Products][Product Specifications UTD]) + INTERSECT({"Red", "Yellow", "Black"}, [Related Products][Allergen Statements UTD]) + ...keep going for the rest... )>0>>

Product Documentation

<<EndIF>>

I don’t remember if those were all the bad colors or not. But, basically, count up the number of times bad colors show up in the each of the related columns, and if greater than 0 you know you’ll need to print it. Put this BEFORE you’re Start for [Related Products].

Also, I can’t tell completely, but is that really really long IF statement after the Start needed?

anonymous · September 20, 2020, 1:12am

Hmm, yes but the thing is, this doesn’t take into account the Last requested Date or if the LR Date is blank like my formula. somehow I would need to total mine up somehow.

anonymous · September 20, 2020, 1:38am

Ah, right. Well, for the number of times its needs to get referenced, you might wanna throw a VC in your table that strictly calculates a TRUE/FALSE result on whether or not documentation is needed, in general, for said product. That will make everything easier.

Jonathan_S:

`<

In fact, isn’t that what this expression does?

anonymous · September 20, 2020, 1:40am

Yes but how would I get it to say Product documentation only once?

anonymous · September 20, 2020, 1:44am

If you put that in a VC, then you can do

<<IF: IN(TRUE, [Related Products][Documentation Needed])>>
Product Documentation
<<ENDIF>>

Right before the Start for Related Products. There for it only fires once, and it makes the template easier to read.

(assuming you name the column [Documentation Needed])

anonymous · September 20, 2020, 2:58pm

Ok working on the virtual column solution atm,

The Long If after the start is required so I know If I need to Type: Product Name: <<[Product Name]>> before I go and list all the documents needed

Edit your idea works perfectly

Thanks!!

anonymous · September 20, 2020, 4:58pm

Glad it works.

Topic		Replies	Views
Stumped on Workflow. Condition Based on List Index AppSheet Q&A automation , expressions	1	0	September 15, 2020
Workflow Problem AppSheet Q&A expressions	4	0	September 14, 2020
Workflow if related AppSheet Q&A expressions	7	2	October 31, 2019

If Condition problem

AI Suggested topics