cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michellecoslet
Helper I
Helper I

Formula help for Status update

I am trying to create a STATUS field on my Details page that can then be displayed on the Dashboard page as a Subtext which shows ALL entries. 

 

1. Dashboard page would show ALL entries (NCRs). Each NCR would show the NCR number as well as the STATUS of that NCR (In progress, Closed, Past Due).

2. Details Page would show the Status of the NCR based on a couple factors. 1) NCR COMPLETED FIELD says Yes or is Blank. Yes = closed, Blank = In Progress 2) If NCR COMPLETED FIELD is blank and DUE DATE is past due, PAST DUE.

 

I am very new at this and do not know where to start. I created a "LABEL' on the Details page and tried a simple formula such as If('NCR COMPLETE' = "yes", "CLOSED") however the formula did nothing when ran. 

 

End goal is to be able to filter the dashboard by ALL, CLOSED, IN PROCESS, PAST DUE

 

Any help appreciated!

19 REPLIES 19
Anonymous_Hippo
Super User
Super User

Hello @michellecoslet ,

 

You are doing the right thing but referring the wrong field 🙂

 

I am not sure what the excat value of your field is but on your left tree view navigate to the NCR Complete data card and look at the name of the field there

 

It might be DaraCardValue2 or something like that

 

Now update your formula to the following

If(DataCardValue2.Value="Yes","Closed")

Here i am assuming that NCR Complete is a text field if this is a Boolean value then put the following formula

If(DataCardValue2.Value = true, "Closed")

 

Hopefully this helps 🙂

michellecoslet
Helper I
Helper I

Thank you!

 

So it is a dropdown with options Yes or No. does that make a difference?

Thank you!

 

So it is a dropdown with options Yes or No. does that make a difference?

Screenshot 2022-03-18 133304.jpg

Screenshot 2022-03-18 133357.jpg

  

Anonymous_Hippo
Super User
Super User

@michellecoslet 

 

Updated answer please re-read

To answer your question. Yes the type of field a column is makes difference in the formula that we add - also save yourself alot of pain by never using Yes/No columns

 

Sorry i forgot to answer your actual question hehe ! 😅

 

Here we need to do two things

  1. In the Details Page you need to add a new label called status that basically autopopulates its value based on the logic you had provided
  2. Add a new filter field to filter by the status field

 

 

For 1

Start by Label . In the Text property of this field

add this formula

 

 

If(DataCardValue12.Selected.Value="Yes","Closed",If(IsBlank(DataCardValue1.Selected) && Today()>DueDate.SelectedDate,"Past Due",If(IsBlank(DataCardValue12.Selected) && Today()<=DueDate.SelectedDate,"In Progress")))

 

 

Here please note that DataCardValue1 refers to the field used in NCR complete datacard- please replace with the field that you use also for DueDate please substitute with correct field name.

 

While I am working on the second part could you try this

michellecoslet_0-1647631282695.png

so I am still getting an error. The formula makes sense to me though!

 

Anonymous_Hippo
Super User
Super User

@michellecoslet 

 

For Part 2.

 

For filtering your gallery firstly you may add a new Dropdown field to filter

 

And set the items property to the following 

 

 

 

["In Progress","Closed","Past Due"]

 

 

 

You do not need all - to see all just remove the selected value from the dropdown filter

 

then edit the gallery's items property to the following

 

 

 

Filter(
    YOurCollectionName/Connection,
    If(
        NewComboboxField.Selected.Value = "Closed",
        NCRComplete.Value = "Yes",
        If(
            NewComboboxField.Selected.Value = "Past Due",
            IsBlank(NCRComplete) && Today() > 'Submission Target',
            If(
                NewComboboxField.Selected.Value = "In Progress",
                IsBlank(NCRComplete) && Today() <= 'Submission Target'
            )
        )
    )
)

 

 

 

Here please use the correct Collection/Connection name. Also remember to use the name of the Column name for NCR complete.

 

I made a test case and this works for me 🙂 please accept this answer as solution if it solved your question

Thanks

Hello @michellecoslet ,

 

I see that you you forgot to mention ".SelectedDate"

 

Please try this

if(DataCardValue12.Selected.Value="Yes","Closed",If(IsBlank(DataCardValue1.Selected)&&Today()>DataCardValue26.SelectedDate,"Past Due", If(IsBlank(DataCardValue1.Selected)&& Today()<= DataCardValue26.SelectedDate, "In Progress")))

 

Also i have updated the last part of the formula  🙂

 

This should work 😀

hmm still shows an error because of the "type" of Label i am using saying it is incompatible. Just to be sure, i WAS supposed to make sure that all the DataCardValue fields are "DataCardValue12", correct? I notice in your formula some said "DataCardValue1".

 

michellecoslet_1-1647633703471.png

 

Here is the properties of DataCardValue12:

michellecoslet_2-1647633786193.png

 

And here are the properties of DataCardValue26

michellecoslet_3-1647633850269.png

 

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,848)