Error in Condition Statement

[Anonymous]

I'm looking for some fresh eyes to help me de-cypher my error(s) in the below, please.

The error appears to be at the parentheses after the DateAdd in the two conditions. The error returned is: Invalid number of arguments: Received 1, expect 2 - 3.

Thoughts?

Thank you!

CountRows(ECMmbrCQSmryGallery.AllItems.CQTitle) >=5 && DateAdd (Today() - DateValue(ECMmberDteofCQFld.Text) <1 && StartsWith(ECMmbrCQTitleFld.Text, "Firefighter") && IsBlank(LookUp(ECMmbrCQSmryGallery.AllItems, ComplianceStatus="Noncompliant")) || CountRows(ECMmbrCQSmryGallery.AllItems.CQTitle) <=4 && DateAdd (Today() - (DateValue(ECMmberDteofCQFld.Text) <1 && StartsWith(ECMmbrCQTitleFld.Text, "Firefighter") && IsBlank(LookUp(ECMmbrCQSmryGallery.AllItems, ComplianceStatus="Noncompliant"))  

[RandyHayes]

@Anonymous 

There are definitely errors in your formula.  Can you share where this formula is used?  Is it intended to return a true/false condition or a value?

[Anonymous]

It is used to set the default position of a Toggle control.

This image is older. I'm providing it for reference; disregard the condition in it. The latest condition is the one you already have.

My original condition is below; but, I realized it wasn't considering all needs.

     CountRows(ECMmbrCQSmryGallery.AllItems.CQTitle) >=5 && IsBlank(LookUp(ECMmbrCQSmryGallery.AllItems,

     ComplianceStatus="Noncompliant")) 

The goal is to set the Toggle to 'true' for 'Compliant' condition, and 'false' for "Noncompliant".

The condition(s) -

Gallery:

Count the Rows; If there are ONLY four the CQTitle must include a Title that StartsWith "Firefighter" and that document's date of issue (DteofCQ) must be within 720 days of Today; And If none of the ComplianceStatus is "Noncompliant" set the Toggle to 'true'.

Or

Count the Rows; If there are more than four the CQTitle must include a Title that StartsWith "Firefighter", with any DteofCQ, and a Title that StartsWith "Live" (for Live Fire Refresher), with a date of issue (DteofCQ) that must be within 720 days of Today; And if no item in ComplianceStatus is "Noncompliant" set the Toggle to 'true'.

In the example below the Toggle should default to 'true'; it did not.

Also ComplianceStatus is getting content from a calculated field in SP. I understand this could cause problems.

     ECMmberComplianceStatusFld.Text is being populated by ThisItem.ComplianceStatus

[RandyHayes]

@Anonymous 

So as I see if from your logic, there are three conditions that any one would turn the result to "true":

1) All items show "Noncompliant"

2) There are 4 items that start with Firefighter and that are less than 720 days

3) There are more than 4 firefighter ones AND any other item starts with "Live" with less than 720 days

If so, then this is the formula for that:

(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, ComplianceStatus="Noncompliant") = 0) ||
(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Firefighter") && (Value(yourDaysToRenewalLabel.Text)<720))) = 4) ||
(
    (CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Firefighter"))) > 4) &&
    (CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Live") && (Value(yourDaysToRenewalLabel.Text)<720))) > 0) 
)

Now, of special note, rather than redo the date logic in the formula, it appears you have already calculated this in a label in your Gallery...just use that as the value.  No need to calculate again.  IN the above formula, I referred to it as yourDaysToRenewalLabel.  Just replace that with the real name from your gallery.

[Anonymous]

I like the using the Days to Renewal field content already available. Duh; man, how did I miss that?

To your assumptions:

1) If All items show "Noncompliant" Toggle = false - This is true, if ANY item in the Compliance Status returns 'Noncompliant' Toggle default position = false.

2) There are 4 items that start with Firefighter and that are less than 720 days - No. There are only two items that start with Firefighter (Firefighter I or II). There must be a minimum of four CQ Titles (the names of the others don't matter to the existing condition, but here are all the minimum CQ Title, in case they matter now):

Firefighter I or Firefighter II (if there is NO Live Fire Refresher document loaded the Firefighter I or II Date of CQ must be within 720 days of Today).

Live Fire Refresher (If there is a Live Fire Refresher the Date of CQ must be within 720 days of Today, and the date of the FFI or FFII is no longer relevant)

Medical, SCBA Fit Test, Fitness Test

3) There are more than 4 firefighter ones AND any other item starts with "Live" with less than 720 days - No.

Please see the following are examples.

Four Doc Example:

FFI or FFII with Date of CQ within 720 days of Today (and NO Live Fire Refresher); plus Compliant SCBA Fit Test, Medical, and Fitness Test - Toggle 'true'.

Five or More Doc Example:

FFI and/or FFII with Date of CQ within 720 days of Today (with no Live Fire Refresher); plus Compliant SCBA Fit Test, Medical, and Fitness Test - Toggle 'true'.

Five or More Doc Example:

FFI and/or FFII with any Date of CQ; plus Live Fire Refresher within 720 days of Today; plus Compliant SCBA Fit Test, Medical, and Fitness Test - Toggle 'true'.

Five or More Doc Example:

FFI or FFII with any Date of CQ; plus Live Fire Refresher with date beyond 721 days or more from Today; plus Compliant SCBA Fit Test, Medical, and Fitness Test - Toggle 'false'.

[RandyHayes]

@Anonymous 

Yes...always reference what you have already put more than 2 seconds of work into - that's 2 seconds you get back in life 🙂

So I am somewhat lost on all of the scenarios as I am not intimately familiar with your data as you are.

But, I believe I presented an approach in the formula I last provided with is the key to doing what you want - and that is to Filter the AllItems not just reference them and figure it out in the rest of the formula.

The idea is, create a filter on the AllItems with the criteria that meets what you need and then count the number of occurrences and see if it is equal to or less/greater than what you need to meet the criteria.  The result of that (boolean) And'ed or Or'ed with all of the other conditions will give you a final result of true or false for your toggle.

Can you work your logic into the formula I provided?

[Anonymous]

Ok, I got it to this. I was running around the house hi-fiving myself and dislocating my shoulder patting myself on the back. Then, I ran a few more tests...

The new condition and a current screenshot are below. In this example the Toggle should be true (green). Any thoughts on where my error lies?

There are no "Noncompliants". There is at least one CQTitle with "Firefighter" in it. There are more than four (4) Items displayed. The Live Fire Refresher is still valid for 364 more days. The Member should be 'Active'.

(CountRows(ECMmbrCQSmryGallery.AllItems.CQTitle) =4 &&
(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, ComplianceStatus="Noncompliant"))) =0 &&
(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Firefighter"))) >0 && (Value(Today()-ECMmberDteofCQFld.Text)>0))) ||
(CountRows(ECMmbrCQSmryGallery.AllItems.CQTitle) >4 &&
(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, ComplianceStatus="Noncompliant"))) =0 &&
(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Live"))) >0 && (Value(ECMmbrDaystoRenewalLbl.Text)>0)))

[RandyHayes]

@Anonymous 

Now don't go dislocatin' any arms yet!!

Your formula will not give you what you want.  First, you need to move the Value of the Label into the Filter not outside like you have.  If you do that, then your formula will only be evaluating against the first any only label in the gallery.  It needs to be inside the filter for it to be evaluated properly.  And again, you already have the calculation there in that label, so just use that number - don't reevaluate the date span.

I believe I have your logic down - or at least I am closer.  It's hard to keep on top of all the things on the plate and work out the logic exactly as you describe.  But I have read through it several times and I believe the below formula should be spot on.  I might be backward with the true and false, but I have annotated it in the formula below.  What I have not done is set up a test scenario to flush it all out with 100% confidence.  Just drawing on experience and my internal formula editor (i.e. there might be some mismatched parens or syntax issues)

// If All items show "Noncompliant" then false - 
// in this formula the concept is if this countrows returns true (there are 0 rows with NonCompliant) then there are zero items that are listed as 
// noncompliant.  If it is 1 (or more) then this will be false.  This is the base of all the conditions and the result of this MUST be AND'ed with
// any other ctriteria
 
    (CountRows(
        Filter(ECMmbrCQSmryGallery.AllItems, ComplianceStatus="Noncompliant")
      )=0
     ) &&

    (  // note the opening paren - the next conditions will be OR'ed and then AND'ed with the above

// There must be a minimum of four CQ Titles that start with Firefighter AND that are less than 720 days (basing this off of the existing calculation
// in the ECMmberDteofCQFld texdt label value as it already has the number of days
// NOTE not sure if this should make the toggle true or false.  In this formula, it will make it true.  If false is needed, then either change the >=
// around or apply a ! (Not) in front of it.
    // !  //add this if needed.
        (CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Firefighter") && Value(ECMmberDteofCQFld.Text) < 720)) >=4) 


    ||


// There are more than 4 firefighter ones AND any other item starts with "Live" with less than 720 days - No.  Again, if this condition is met, then the
// formula will be true, if the opposite is needed, add a ! (Not) to the below
    // !  //add this if needed.
        (
            (CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Firefighter"))) >=4 ) &&
            (CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Live") && (Value(ECMmbrDaystoRenewalLbl.Text) < 720))) < 720)
        )  
    
    
// Note the closing paren
    )

Looks a lot bigger, but just because I annotated so much in it.  

Without the annotation it is this:

(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, ComplianceStatus="Noncompliant"))=0) &&
(  
    (CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Firefighter") && Value(ECMmberDteofCQFld.Text) < 720)) >=4) ||
    (
        (CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Firefighter"))) >=4 ) &&
        (CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Live") && (Value(ECMmbrDaystoRenewalLbl.Text) < 720))) < 720)
    )  
)

Let's see where that goes!

[Anonymous]

The version I got now sure looks like its working. I've go through a couple dozen Member profiles, they are all manner of CQs and compliance; they all return correct. That said, I'm listening...please see belwo.

Can you please make any required changes / updates to the condition based on my revelations below?

If All items show "Noncompliant" then false - No. If any ONE item shows "Noncompliant" then false; not 'All' items.
In this formula the concept is if this CountRows returns true (if there are 0 rows with NonCompliant that means there are zero items that are listed as noncompliant - Ok, then disregard my comment above. But, it sure doesn't read like it.🤣

If it is 1 (or more) then this will be false. This is the base of all the conditions and the result of this MUST be AND'ed with - Yes, one or more 'Noncompliant' of any time CQ and the toggle default is 'false'

(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, ComplianceStatus="Noncompliant"))=0) &&

( // note the opening paren - the next conditions will be OR'ed and then AND'ed with the above

 There must be a minimum of four CQ Titles that start with Firefighter Nope; There must be a minimum of four AND if there are only four CQTitles then one of them MUST be a Firefighter I or II, and MUST HAVE been inssued within the passed 720 days (to cover the Live Fire Refresher requirement) AND that are less than 720 days (basing this off of the existing calculation.

There must be at least four CQs (if there is NO Live Fire Refresher; five CQs if there IS a Live Fire Refresher). If only four CQs ONE CQ Title must StartsWith "Firefighter". There is no need for four 'Firefighter' certifications; Only that one of the four must be a Firefighter I or Firefighter II [which is why we use the StartsWith]).
/ in the ECMmberDteofCQFld text label value as it already has the number of days
// NOTE not sure if this should make the toggle true or false. In this formula, it will make it true. If false is needed, then either change the >= around or apply a ! (Not) in front of it.
// ! //add this if needed.
(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Firefighter") && Value(ECMmberDteofCQFld.Text) < 720)) >=4)

||

There are more than 4 firefighter (The Gallery only reflects one Member at a time, and each Member is only required to have ONE Firefighter I and or II certification) ones AND any other item starts with "Live" ("Live" and the Live Fire Refresher certs become part of the compliance condition after the first 720 days(Member are required to complete Live Fire training every two years); from then on ALL members must have a current Live Fire Refresher cert along with a valid Firefighter I or II with less than 720 days - No. Again, if this condition is met, then the formula will be true, if the opposite is needed, 
(
(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Firefighter"))) >=4 ) &&
(CountRows(Filter(ECMmbrCQSmryGallery.AllItems, StartsWith(ECMmbrCQTitleFld.Text, "Live") && (Value(ECMmbrDaystoRenewalLbl.Text) < 720))) < 720)

[RandyHayes]

@Anonymous 

I'm wrapping up for the day here, so let me review this tomorrow and get back to you.