Expression Big Brain support

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 :slight_smile:

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,

What’s the other color options besides green and blue?

2 Likes

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 :slight_smile:

Ok, I just wanted to check to see if there was literally one one other color, because it would be easier to check for it.

Try:
INTERSECT(LIST("Red", "Yellow", "Black"), LIST([Product Specifications UTD], [Allergen Statements UTD], ...))>0

Obviously finish filling in the second list with each of your columns to check.

1 Like

wow never used this intersect before, if this works that would be cool.

So than On top of this I would Do all my other check correct? That’s what you mean by:
“Obviously finish filling in the second list with each of your columns to check.”

This would just be your workflow/report condition to see if you even need to run it. I only typed two column names into that second list, you’ll need to finish it with the other 6.

Otherwise all the function does is ask how many of any of the first list show up in the second list. And in the case of firing the email, you don’t care how many are true, or if there are any. You’re email template will take care of the rest in determining exactly what you are asking them for.

1 Like

Im getting this Error

INTERSECT does not accept a list of list.

With this formula

INTERSECT(LIST("Red", "Yellow", "Black"), LIST([Related Products][Product Specifications UTD], [Related Products][Allergen Statements UTD]))>0

What does that mean?

I also Tried

INTERSECT(LIST("Red", "Yellow", "Black"), LIST([Related Products][Product Specifications UTD], LIST ([Related Products][Allergen Statements UTD])))>0

This doesn’t work either

Nor this

INTERSECT(LIST("Red", "Yellow", "Black"), LIST([Related Products][Product Specifications UTD]))

I think I see whats happening,

the Report will be firing from the Suppliers Table… which needs to see if any which one of the child products is in need of documentation for whatever reason.

I would not know how to implement this.

Im sorry if i Failed to specify this in my initial post. May change things.

OOOOOH. Yes, it does change things. Can’t take lists and put them in a list, unfortunately, but also makes sense. Give me a few minutes and I’ll come up with a new solution for from the suppliers. I probably didn’t pay attention when reading.

1 Like

Try:

INTERSECT( LIST("Red", "Yellow", "Black"), 
   [Related Products][Product Specifications UTD] + 
   [Related Products][Allergen Statements UTD] + 
   *next list* + 
   *add more lists as needed* 
)>0

Assuming the lists are compatible with each other, which they should be, we just add them all together and check for any instances of the color options in question. Since, again, we don’t care at this point which list(s) contains the match.

1 Like

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 :slight_smile:

Oh, yeah. I keep forgetting to type the count at the beginning.

Should work. Let me know if not.

1 Like

ok, so this is what I have as right now to see if any documentation is out of date,

OR(Count(INTERSECT( LIST("Red", "Yellow", "Black"), 
   [Related Products][Product Specifications UTD] + 
   [Related Products][Allergen Statements UTD]+
    [Related Products][GRAS Statement UTD]+
     [Related Products][Organic Certificate UTD]+
      [Related Products][Gluten Free Certificate UTD]+
       [Related Products][NON GMO Statement UPD]+
        [Related Products][Kosher Certificate UTD]+
         [Related Products][Radiological Statement UTD]
        
))>0,

and([Third Party Certificates UTD] <> "Green",[Third Party Certificates UTD] <> "Blue"),and([Proposition 65 UTD] <> "Green",[Proposition 65 UTD] <> "Blue")
)

the Third Party Certificates and Proposition 65 are directly tied to supplier so I did those this way. IS this the best way?

Also, now I need top check if a document needs attention, to only go true if that document hasn’t already been requested within the past 2 weeks.

Now If I think about this I kinda think I messed up, because If a document is expired, I need to check if a document hasn’t already been requested before giving the go ahead… Cant think of doing this with the intersect idea… but I didn’t even know about intersect so yeah… Definitely be using intersect mre in the future…

Supplier Table
[LR Third Party Certificate]<Today() + 14
[LR Proposition 65]<Today() + 14
[LR Product Specifications]<Today() + 14
[LR Allergen Statement]<Today() + 14
[GRAS Statements]<Today() + 14
[LR Organic Certificate]<Today() + 14
[LR Gluten Free Statement]<Today() + 14
[LR NON GMO Statement]<Today() + 14
[LR Kosher Statement]<Today() + 14
[LR Radiological Statement]<Today() + 14

In that case, you could add Third Party Certificates and Proposition 65 to the intersect with just a + LIST([Third Party Certificates UTD], [Proposition 65 UTD]).

Create a Yes/No column for if a request has gone out called like [Request Sent]. Set up an action to set the column to TRUE when a request has gone out. You can even attach it to this same report after the first email is sent out. You’ll need to remember to set it back to FALSE after the document(s) do get updated.

  AND(
     Count(INTERSECT( LIST("Red", "Yellow", "Black"), 
     [Related Products][Product Specifications UTD] + 
     [Related Products][Allergen Statements UTD]+
     [Related Products][GRAS Statement UTD]+
     [Related Products][Organic Certificate UTD]+
     [Related Products][Gluten Free Certificate UTD]+
     [Related Products][NON GMO Statement UPD]+
     [Related Products][Kosher Certificate UTD]+
     [Related Products][Radiological Statement UTD]+
     LIST([Third Party Certificates UTD], [Proposition 65 UTD])
     ))>0,
     NOT([Request Sent])
  )

Alternatively, the more appropriate yet more complicated way, do the Yes/No column in the child Products table. Alter your parent Supplier related columns to only grab related items that are NOT([Request Sent]). This would require using actions in the parent to set the child’s [Request Sent] during the workflow/report. But, this would actually keep track of whether or not there was a request for each product, where as the top solution works per supplier. I can’t write out a full how-to on all of this though.

Trust me, I know the difficulties in trying to get the data structure right for what you want. I’ve scraped the same personal project like 4 times. Finished it so I had something working. And then started a revamp. All trying to chase the most optimal structure and because it was impossible to continue to add features to the structure I had. Plus I’m trying to rebuild it so that if I deleted all my data I manually constructed in the spreadsheet, and give the app to someone else, they could quickly and easily recreate that same data or their own.

So to be 100% brutally honest with you, when you ask if this is the best way, I actually want to say no, because the best way would probably be a complete restructure of your data. I don’t know how much time you’ve sunk into this. But I would highly recommend moving to a 3 table (minimum) setup: Supplier, Products, Certificates. Supplier owns Products, and both can have Certificates.

3 Likes

Your absolutely right,

So I’ve Added all the below to my Products table, Should of been this way in the first place so I can see on a per product basis when a document was last requested.
I had in mind exactly what you were saying. so when a document is updated I would need to Clear the LR Date Columns Below and when the report fires I was have a bunch of workflows or reports that would update the LR to Today(), but Im not there yet :).

So the below would need to be factored in to each document.

Supplier Table
[LR Third Party Certificate]<Today() + 14
[LR Proposition 65]<Today() + 14

Products Table Date columns!
[Related Products][LR Product Specifications]<Today() + 14
[Related Products][LR Allergen Statement]<Today() + 14
[Related Products][GRAS Statements]<Today() + 14
[Related Products][LR Organic Certificate]<Today() + 14
[Related Products][LR Gluten Free Statement]<Today() + 14
[Related Products][LR NON GMO Statement]<Today() + 14
[Related Products][LR Kosher Statement]<Today() + 14
[Related Products][LR Radiological Statement]<Today() + 14

Ok. So Ive changed everything and now here is this is what I have, I have no issue checking the supplier documents, I check if they are in need of documents and a request hasnt been sent out already within the past 2 weeks. This formula works, Its the Last Section where I need to check the 8 Product Documents.

Or(and(Count(INTERSECT( LIST("Red", "Yellow", "Black"), 
  LIST([Third Party Certificates UTD])))>0,[LR Third Party Certificate]<Today() + 14),
  
  and(Count(INTERSECT( LIST("Red", "Yellow", "Black"), 
  LIST([Proposition 65 UTD])))>0,[LR Proposition 65]<Today() + 14),
 
 
 and(Count(INTERSECT( LIST("Red", "Yellow", "Black"), 
  [Related Products][Product Specifications UTD]))>0,[Related Products][LR Product Specifications]<Today() + 14))

So Its the Last Clause were I start to check the Product Documents.

and(Count(INTERSECT( LIST(“Red”, “Yellow”, “Black”), [Related Products][Product Specifications UTD]))>0,[Related Products][LR Product Specifications]<Today() + 14))

I cant compare list with Date…, Also with this entire intersect idea, How Do I know I am intersecting the correct Last Request document Date for a Product for the Correct Certificate expiry, because intercept is check each and every product in one big Swoop…

So this above, and what I was thinking may not even work with the intercept.

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… :frowning:

I know this

  1. I need to check Supplier documents and Last requested Dates for each Document.
  2. 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 .

This part is the problem for the last error you mention. It’s not related to the intersects.
[Related Products][LR Product Specifications] is a LIST of something.
Today() + 14 is ONE date in the future.

This is precisely why I mentioned to revamp to 3 tables. If you had a Certificates table with say columns:
[Certificate ID],
[Certificate Type] (a dropdown for whether its a supplier or product cert),
[Certificate Name] (a dropdown with “Product Specifications”, “Allergen Statements”, “GRAS Statement”, etc),
[Certificate] to hold the path to the uploaded document.
[Supplier ID] for if its a supplier certificate, [Product ID] for if its a product certificate,
[Status Color],
[Date Uploaded] a ChangeDate column that watches for the a change to the [Certificate] column.
[Expiration Date] would be [Date Uploaded]+whatever amount of time as determined by [Certificate Name] or a static number if they all expire after the same amount of time,
[Update Request Sent] a Yes/No to know if this particular document has been requested already,

I can’t think of any of Certificate columns at the moment. But if you were to redo into the 3 table setup, as you can see from above, each Certificate becomes infinitely easier to maintain and manage.

1 Like

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 :wink:

I believe so, but the logic to make it work might be slow and muddy.

Maybe keep this kicking through the audit, and then work on moving data to a new format that will be easier to maintain in the future?

1 Like

I’m ok with slow and muddy for now. Its just a report that will try to fire on a daily basis.

Could someone Help me figure out how to get this expression written? or get me in the correct direct?

Willing to Pay a price. $$$$$$$$$$ PM Me.