Solved: Re: Check Multiple IF Statement Formula

Phineas · ‎12-04-2021

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"))))))

WarrenBelz · ‎12-05-2021

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

View solution in original post

WarrenBelz · ‎12-05-2021

@Phineas ,

Are you asking about interaction outside Power Apps (SharePoint) or inside? For the first, you would need a SharePoint calculated column, but the second could be done with the structure of the code provided wherever you wanted to display it in Power Apps.

View solution in original post

Ramole · ‎12-04-2021

Hi @Phineas

Please check this post Multiple conditions inside IF statement

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

WarrenBelz · ‎12-05-2021

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 · ‎12-05-2021

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 · ‎12-05-2021

Hi @Phineas ,

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

Phineas · ‎12-05-2021

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 · ‎12-05-2021

@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 · ‎12-05-2021

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 · ‎12-05-2021

@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 · ‎12-05-2021

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?