Two formulas in a Label Text Property

[isBlank]

Hi Powerapps community,

I am hung up on something that seems like it would be easy..  I need to have two formulas in a Text property of a Label.

I believe I need to use ThisItem for looking at the appropriate row, since the Label is in a gallery.

Would be grateful for any advice on this problem. 

Thanks.

//Check if the date diff is greater than 7, if so reset.
If(DateDiff(DateValue(Text(ThisItem.CLSUnitResetDate)) ,Now()) > 7, ThisItem.CLSUnitResetDate = Now())

//Check if the frequency is Week, Month or Year, then filter and sum the column between the dates for the 'Case Number'.
If(
    ThisItem.CLSUnitFrequency.Value = "Week",
    Sum(
        Filter(
            DataSheet,
            EntryDate >= ThisItem.CLSUnitResetDate && EntryDate <= Now(),
            Title = ThisItem.CaseNumber
        ),
        CLSUnitsConsumed
    )
)

[RandyHayes]

@isBlank 

You are trying to perform an action/behavioral statement in your property.  You cannot do that, nor can you assign a field value with a statement such as ThisItem.CLSUnitResetDate = Now()

You will need to make the assignment of the column as part of your Items property in order to have this work.

What is the Items property of your Gallery?

[isBlank]

Hi Randy, 

Well, right now I only have a search formula in there:

Search(var_ConsumerDatabase, TextInput3.Text, "Consumer")

So yeah, I am in a tough spot with this one.  

The backstory to this issue is as follows...

I have a Number value stored in a column, 'CLSUnitsConsumed...

I am trying to look at a choice column name CLSUnitFrequency and Filter a date range based on its value, Month, Week, Year.. then filter and sum the values in that date range in 'CLSUnitsConsumed' column.

The other IF statement is looking at the DateDiff and I was planning to use that to store a CLSUnitResetDate in Sharepoint, then compare that to the current date, Now(), and use CLSUnitResetDate as the 'From' in the Date Range 'To' now...

If you can suggest a more straight forward approach, that would have better reliability that would be great.

I can see there being an issue if this code is not triggered before the reset date.  That could cause unit allocation to be over or under the actual value assigned.

I was going to start here, test, and hopefully come up with a more solid implementation.

Thanks!

[isBlank]

I have managed to get this part working, but still.. IDK where to add the code for updating the CLSUnitResetDate.  

I have tried putting it in the Items property of the Gallery and that doesn't work.  When I add a ; to the Items Property of the Gallery I get this message: Expected Operator

Here is the code I need to put in a 'behavior' property... 

If(DateDiff(DateValue(Text(ThisItem.CLSUnitResetDate)) ,Now()) > 7, Patch(ConsumerData,ThisItem,{LastIPOSReview: Now()})

This is what I have in the label:

Switch(ThisItem.CLSUnitFrequency.Value,
    "Week",
    Sum(
        Filter(
            DataSheet,
            EntryDate >= ThisItem.CLSUnitResetDate && EntryDate <= Now(),
            Title = ThisItem.CaseNumber
        ),
        CLSUnitsConsumed
    ),
    "Month",
    Sum(
        Filter(
            DataSheet,
            EntryDate >= ThisItem.CLSUnitResetDate && EntryDate <= Now(),
            Title = ThisItem.CaseNumber
        ),
        CLSUnitsConsumed
    ),
    "Year",
    Sum(
        Filter(
            DataSheet,
            EntryDate >= ThisItem.CLSUnitResetDate && EntryDate <= Now(),
            Title = ThisItem.CaseNumber
        ),
        CLSUnitsConsumed
    )

Having worked in Visual Studio with some C# experience I really find this quite frustrating.. can anyone shed some light on what options are available for this situation?

Thanks 🙂

[RandyHayes]

@isBlank 

So, you're not developing, your writing formulas - so there is a vast difference between C# and PowerApps.  PowerApps is modeled around Excel and formulas, so you need to view this from a different perspective.

I am trying to understand your need a little bit more...are you stating that you want the records in the datasource to be updated based on the date formula?  I ask this because these are two different types of behaviors.  Displaying values is all about formulas for doing just that.  Updating records needs to be in a behavioral action.  

So, when I mentioned having the Items property reflect the information that you wanted, it was simply about just that, having the calculations and data be shown as needed.  However, you cannot put behavioral actions (updating records) into the evaluated Items formula.

So, I just wanted to get some clarification as to what you are really trying to aim for before offering suggestions.

[isBlank]

Hi Randy,

@RandyHayes
Yes I would like the records in the database updated using the date formula shown in previous posts.

I have worked on it a bit more and came up with this formula, which seems to work for what I need, except the only behavioral option I see is the OnSelect event in the Gallery.

Switch(ThisItem.CLSUnitFrequency.Value,
    "Week",
    If(DateDiff(DateValue(Text(ThisItem.CLSUnitResetDate)) ,Now()) > 7, Patch(ConsumerData,ThisItem,{CLSUnitResetDate: Now()})),
    "Month",
    If(DateDiff(DateValue(Text(ThisItem.CLSUnitResetDate)) ,Now()) > 30, Patch(ConsumerData,ThisItem,{CLSUnitResetDate: Now()})),
    "Year",
    If(DateDiff(DateValue(Text(ThisItem.CLSUnitResetDate)) ,Now()) > 365, Patch(ConsumerData,ThisItem,{CLSUnitResetDate: Now()}))
);

Switch(ThisItem.SIPUnitFrequency.Value,
    "Week",
    If(DateDiff(DateValue(Text(ThisItem.SIPUnitResetDate)) ,Now()) > 7, Patch(ConsumerData,ThisItem,{SIPUnitResetDate: Now()})),
    "Month",
    If(DateDiff(DateValue(Text(ThisItem.SIPUnitResetDate)) ,Now()) > 30, Patch(ConsumerData,ThisItem,{SIPUnitResetDate: Now()})),
    "Year",
    If(DateDiff(DateValue(Text(ThisItem.SIPUnitResetDate)) ,Now()) > 365, Patch(ConsumerData,ThisItem,{SIPUnitResetDate: Now()}))
)

 That means the records are only updated if the user clicks on the Gallery Row.  There must be another way. I have looked at using a ForAll formula which looks through the entire List and placed the following the formula in the OnVisible event of the Screen.  

Something like:

ForAll(var_ConsumerDatabase, 

If(CLSUnitFrequency.Value = "Week", 

If(DateDiff(DateValue(Text(CLSUnitResetDate)), Now()) >= 7, Patch(ConsumerData,{CLSUnitResetDate: Now()}))));

ForAll(var_ConsumerDatabase, 

If(CLSUnitFrequency.Value = "Month", 

If(DateDiff(DateValue(Text(CLSUnitResetDate)), Now()) >= 30, Patch(ConsumerData,{CLSUnitResetDate: Now()}))));

ForAll(var_ConsumerDatabase, 

If(CLSUnitFrequency.Value = "Year", 

If(DateDiff(DateValue(Text(CLSUnitResetDate)), Now()) >= 365, Patch(ConsumerData,{CLSUnitResetDate: Now()}))));

This is giving me a runtime error at the moment.

Any other suggestions on how this can be managed?

Thanks!

[RandyHayes · Accepted solution]

@isBlank 

Yes, you definitely don't want that last formula as the ForAlls are backward to begin with.

If you have all of this in a Gallery, then your formula to cover all the rows would be this: (and you'd put this on a button/icon/etc outside of your Gallery)

Patch(ConsumerData,
    ForAll(yourGallery.AllItems As _item,
         With({_cdiff: DateDiff(_item.CLSUnitResetDate, Now()),
               _sdiff: DateDiff(_item.SIPUnitResetDate, Now())},
             {ID: _item.ID,
              CLSUnitResetDate:            
                 If(
                     CLSUnitFrequency.Value = "Week" && _cdiff > 7, Now(), 
                     CLSUnitFrequency.Value = "Month" && _cdiff > 30, Now(), 
                     CLSUnitFrequency.Value = "Year" && _cdiff > 365, Now(), 
                     _item.CLSUnitResetDate
                 ),
              SIPUnitResetDate: 
                 If(
                     SIPUnitFrequency.Value = "Week" && _diff > 7, Now(), 
                     SIPUnitFrequency.Value = "Month" && _diff > 30, Now(), 
                     SIPUnitFrequency.Value = "Year" && _diff > 365, Now(), 
                     _item.SIPUnitResetDate
                 )
             }
          )
    )
)

This will create a table of records with adjusted dates based on conditions from the Gallery items and values.  It then sends that table to the Patch command to do the changes.

[isBlank]

Randy,

That's a great piece of code!

I made a couple edits as follows:

Set(var_lastDayOfMonth, DateAdd(DateAdd(Date(Year(Now()),Month(Now()),1),1,Months),-1,Days));
Set(var_firstDayOfMonth, Date(Year(Now()),Month(Now()),0));
Set(var_numDaysInMonth, DateDiff(var_firstDayOfMonth,var_lastDayOfMonth));


Patch(ConsumerData,
    ForAll(galConsumerList.AllItems As _item,
         With({_cdiff: DateDiff(_item.CLSUnitResetDate, Now()),
               _sdiff: DateDiff(_item.SIPUnitResetDate, Now())},
             {ID: _item.ID,
              CLSUnitResetDate:            
                 If(
                     _item.CLSUnitFrequency.Value = "Week" && _cdiff > 7, Now(), 
                     _item.CLSUnitFrequency.Value = "Month" && _cdiff > var_numDaysInMonth, Now(), 
                     _item.CLSUnitFrequency.Value = "Year" && _cdiff > 365, Now(), 
                     _item.CLSUnitResetDate
                 ), 
              SIPUnitResetDate: 
                 If(
                     _item.SIPUnitFrequency.Value = "Week" && _sdiff > 7, Now(), 
                     _item.SIPUnitFrequency.Value = "Month" && _sdiff > var_numDaysInMonth, Now(), 
                     _item.SIPUnitFrequency.Value = "Year" && _sdiff > 365, Now(), 
                     _item.SIPUnitResetDate
                 )
             }
          )
    )
);

I subscribed to your Youtube channel as well.

Those day-in-month related variables are going in my OnStart event, or I will just remove the variables and add them into the formula.  This code is in the Screen OnVisible event and seems to be working well.

I really appreciate your help, like you said, its not developing.  I will continue to learn and refine as I go.

Thanks again,

Mike M.