web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Two formulas in a Labe...
Power Apps
Answered

Two formulas in a Label Text Property

(0) ShareShare
ReportReport
Posted on by isBlank 15

 

 

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

 

 

Categories:
Building Power Apps
I have the same question (0)
  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @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 Profile Picture
    isBlank 15 on at

    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 Profile Picture
    isBlank 15 on at

    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 Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @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 Profile Picture
    isBlank 15 on at

    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!

  • Verified answer
    RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @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 Profile Picture
    isBlank 15 on at

     

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 342 Most Valuable Professional

#2
11manish Profile Picture

11manish 234

#3
Valantis Profile Picture

Valantis 187

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans