Check Multiple IF Statement Formula

[Phineas]

Wondering if anyone can looks this over and let me know whether I need to make any correction before building it in my SharePoint list calculated column? The output should be 'Certified', 'Compliant', or 'Noncompliant' based on current date and the date of 'Date of CQ plus renewal frequencies.

It all worked when I built in it Excel (using the column and row numbers instead of the list column headings. I'm concerned most about capturing the 'current date' to compare against the 'Date of CQ'.

=IF(ISBLANK(DateOfCQ),"Noncompliant",

IF(OR(OR(OR(OR(OR(OR(OR(

CERTQUALTITLE="Driver I",CERTQUALTITLE="Driver II"),

CERTQUALTITLE="Officer"),

CERTQUALTITLE="Attendent"),

CERTQUALTITLE="Usher"),"Certified",

IF(AND(CERTQUALTITLE="Maintenance Tech",DateOfCQ+1095>=TODAY()),"Compliant",

IF(AND(CERTQUALTITLE=" Maintenance Supervisor",DateOfCQ+365>=TODAY()),"Compliant",

IF(AND(CERTQUALTITLE="Asst Manager",DateOfCQ+730>=TODAY()),"Compliant",

IF(AND(CERTQUALTITLE="Manager",DateOfCQ+365>=TODAY()),"Compliant","Noncompliant"))))))

[Ramole]

Hi @Phineas 

Please check this post Multiple conditions inside IF statement 

[WarrenBelz · Accepted solution]

Hi @Phineas ,

I will give you an alternative to calculate this in Power Apps - SharePoint Calculated columns are really for SharePoint

If(
   IsBlank(DateOfCQ),
   "Noncompliant",
   CERTQUALTITLE="Driver I" || 
   CERTQUALTITLE="Driver II" || 
   CERTQUALTITLE="Officer" || 
   CERTQUALTITLE="Attendent" || 
   CERTQUALTITLE="Usher",
   "Certified",
   With(
      {
         wCQDate:
         DateDiff(
            DateOfCQ,
            Today(),
            Days
         )
      },
      (
         CERTQUALTITLE="Maintenance Tech" && 
         wCQDate >= 1095
      ) ||
      (
         (
            CERTQUALTITLE="Maintenance Supervisor" || 
            CERTQUALTITLE="Manager"
         ) && 
         wCQDate >= 365
      ) ||   
      (
         CERTQUALTITLE="Asst Manager" && 
         wCQDate >= 730
      ),						   
      "Compliant",
      "Noncompliant"
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Phineas]

Thank you for the reply.

If I may clarify;

1. There is a 'Member List' and 'Member Cert Qual List'. Each list must be current.

         Currency for the cert qual documents ('Member Cert Qual List') is based on 'DateOfCQ'

         of each document and current date.

         Currency for each member (‘Member List’) is based on whether all of their documents are compliant.

2. The Power Apps Gallery displays member cert qual documents (from the 'Member Cert Qual List')

    It also shows member overall compliance through a Toggle control (‘Member List’).

3. The Toggle control on the Power App indicates whether the member overall compliance is 'Compliant' (true)

    or 'Noncompliant' (false) based on whether every document in the Gallery is 'Compliant' (or ‘Certified’, as

    appropriate). The Toggle has an 'UpdateIf' back to the 'Member List' to update compliance whenever a new

    document or update to existing document occurs in the Power App.

4. Document compliance and member compliance are required to be determined on a per action basis and automatically every 24 hours.

5. The formula we are working on is to determine the member overall compliance based on the documents shown in the Gallery.

6. The document compliance displayed in the Gallery is performed in a calculated column of the ‘Member Cert Qual List’ and recalculates whenever a document is added or updated in the list, and updates again with the recurring flow every night so that when the member logs in to the Power Apps all compliance data is current and accurate.

7. Does this seems like an appropriate process?

[WarrenBelz]

Hi @Phineas ,

The calculated column inside the gallery as I posted will do the job providing the logic is correct,

[Phineas]

I guess my question now is, what keeps the SharePoint 'Member Cert Qual List' current?

Any cert / qual data in the Power App is coming from the SharePoint 'Member Cert Qual List' , yes?

If no new documents, or updates to existing, occures in a member's list of documents then the data when retrieved the next time may not be current when the Power Apps is opened, yes?

Which is why the flow is needed, yes?

[WarrenBelz]

@Phineas ,

You obviously have to update the data (DateOfCQ) in SharePoint if it changes. I am not sure why a Flow is needed - you simply need to update the field.

[Phineas]

It has been my understanding there is no way to program 'Today' into a SharePoint column (with a command, calculation, or otherwise), and have the current date populate constantly and continually, in a SharePoint list column.

Consequently, a flow is needed to compare 'DateOfCQ' to current date.

Am I wrong in this assumption?

Also, I understand that SharePoint calculated column data cannot be displayed in Power Apps inputs.

Am I wrong in this assumption, too?

[WarrenBelz]

@Phineas ,

Today() is a built-in function of Power Apps as per the code I posted, so it is comparing DateOfCQ with the current date. SharePoint calculated columns can be displayed, but are not Delegable and really do not work as expected in Power Apps - it is far better to do the calculation in Power Apps.

[Phineas]

I understand how Today () works in Power Apps; how does that keep the entire 'Member Cert Qual List' current?

Seems the we are working on only impacts the member and their document while the user is interacting with Power Apps, and doesn't impact any other document in the 'Member Cert Qual List'.

Let's say I have a running tally in the header (Interger) of the landing page, a column of Labels: National, Region, Local. 

If the update of the 'Member Cert Qual List' is only accomplished through each member engaging Power Apps how does that keep the entire 'Member Cert Qual List' updated for query and view throughout the Power Apps based if only the member's document is impacted through their interaction with Power Apps?