Formula help for Status update

[michellecoslet]

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!

[Anonymous_Hippo]

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]

Thank you!

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

[michellecoslet]

Thank you!

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

[michellecoslet]

  

[Anonymous_Hippo]

@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]

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

[Anonymous_Hippo]

@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

[Anonymous_Hippo]

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 😀

[michellecoslet]

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

Here is the properties of DataCardValue12:

And here are the properties of DataCardValue26