Circular Reference Error (no idea how to solve this)

[Lorem1960]

I have a Text Input control named DataCardValuePercentCreate which has the following formula:

//First need to check if Total hours worked in a workweek is less then 40 hours, if so, then the first 2
//hours need to be charged at 100%. If the running total is more then 40 hours then the OT performed from
//Monday thru Saturday needs to be charged at 150%, on Sunday or Holiday the OT is then charged at 200%.

If(
Value(LabelTotHoursWeek.Text) <= 40 &&
Text(DataCardValueDateOT.SelectedDate,"ddd") in ["Sun","Mon","Tue","Wed","Thu","Fri", "Sat"],
100,
If(Value(LabelTotHoursWeek.Text) > 40 &&
Text(DataCardValueDateOT.SelectedDate,"ddd") = "Sun",
200,
150
)
)

The LabelTotHoursWeek has the following formula:

//Running Total of Hours worked in a Week: Sum of Overtime Hours for selected week + 38 Hours Workweek +
//any additional OT hours entered. Here we use the value that is stored in the DataCardValueWeekNew control.
//DataCardValueWeekNew retrieves the week by using this formula: WeekNum(DataCardValueDateOT.SelectedDate)

With(
{
wList: Filter(
OREPayrollOvertime,
Week = Value(DataCardValueWeekNew.Text) && EmpID = GalleryOTResManagers.Selected.ID
)
},
Sum(
wList,
OvertimeHours
) + 38 + DataCardValueHoursOTWorked
)

The Text Input Control named DataCardValueOTAmount has the following formula (This control calculates the OT earnings):

Text((LookUp(OREPayrollEmployees,ID=varRecordOT.ID,HourlyRate) * DataCardValuePercentCreate)/100 * DataCardValueHoursOTWorked,"###.00")

The problem is that the moment I enter the formula in Text Input control named DataCardValuePercentCreate it gives me
a circular reference error. This is the exact error that is being produced:

This rule creates a circular reference between properties, which is not allowed. A property cannot reference itself or other properties affected by its value.This is the shortest circular reference: DataCardValuePercentCreate.Default -> LabelTotHoursWeek.Text -> DataCardValuePercentCreate.Default

If(
Value(LabelTotHoursWeek.Text) <= 40 &&
Text(DataCardValueDateOT.SelectedDate,"ddd") in ["Sun","Mon","Tue","Wed","Thu","Fri", "Sat"],
100,
If(Value(LabelTotHoursWeek.Text) > 40 &&
Text(DataCardValueDateOT.SelectedDate,"ddd") = "Sun",
200,
150
)
)

Anyone has any idea how I can get around this error? Thanks in advance.

[WarrenBelz]

Hi @Lorem1960 ,

Before I look at all the rest, there are only two things here relevant - please confirm that DataCardValuePercentCreate Default is and what type of control it is.

If(
   Value(LabelTotHoursWeek.Text) <= 40 &&
   Text(DataCardValueDateOT.SelectedDate,"ddd") in ["Sun","Mon","Tue","Wed","Thu","Fri", "Sat"],
   100,
   If(
      Value(LabelTotHoursWeek.Text) > 40 &&
      Text(DataCardValueDateOT.SelectedDate,"ddd") = "Sun",
      200,
      150
   )
)

and that LabelTotHoursWeek Text property is (it looks a bit familiar)

With(
   {
      wList: 
      Filter(
         OREPayrollOvertime,
         Week = Value(DataCardValueWeekNew.Text) && EmpID = GalleryOTResManagers.Selected.ID
      )
   },
   Sum(
      wList,
      OvertimeHours
   ) + 38 + DataCardValueHoursOTWorked
)

[Lorem1960]

Hi Sir,

The default property of the DataCardValuePercentCreate (Text Input control)
has the below formula: Note that it is for the moment commented out since it is given the circular reference error.

//First need to check if total hours worked in a workweek is less then 40 hours, if so, then the first 2
//hours need to be charged at 100%. If the running total is more then 40 hours then the OT perfomed from
//Monday thru Saturday needs to be charged at 150%, on Sunday the OT is then charged at 200%.

/*If(
Value(LabelTotHoursWeek.Text) <= 40 &&
Text(DataCardValueDateOT.SelectedDate,"ddd") in ["Sun","Mon","Tue","Wed","Thu","Fri", "Sat"],
100,
If(Value(LabelTotHoursWeek.Text) > 40 &&
Text(DataCardValueDateOT.SelectedDate,"ddd") = "Sun",
200,
150
)
)*/

Then there is this Label control named LabelTotHoursWeek which I need to get the running total
of the hours worked in a given week. Maybe I am overcomplicating this here?

//Running Total of Hours worked in a week: Sum of Overtime Hours for selected week + 38 Hours Workweek +
//any additional OT hours entered. Here we use the value that is stored in the DataCardValueWeekNew control.
//DataCardValueWeekNew retrieves the week by using this formula: WeekNum(DataCardValueDateOT.SelectedDate)

With(
{
wList: Filter(
OREPayrollOvertime,
Week = Value(DataCardValueWeekNew.Text) && EmpID = GalleryOTResManagers.Selected.ID
)
},
Sum(
wList,
OvertimeHours
) + 38 + DataCardValueHoursOTWorked
)

Thank you very much for checking.

[WarrenBelz]

@Lorem1960 ,

Can you please supply the exact code on both controls that is resulting in the circular reference - also remove any comments or other non-relevant material. 

[Lorem1960]

To get the Percentage:

If(
Value(LabelTotHoursWeek.Text) <= 40 &&
Text(DataCardValueDateOT.SelectedDate,"ddd") in ["Sun","Mon","Tue","Wed","Thu","Fri", "Sat"],
100,
If(Value(LabelTotHoursWeek.Text) > 40 &&
Text(DataCardValueDateOT.SelectedDate,"ddd") = "Sun",
200,
150
)
)

To get the running total of hours within a week:

With(
{
wList: Filter(
OREPayrollOvertime,
Week = Value(DataCardValueWeekNew.Text) && EmpID = GalleryOTResManagers.Selected.ID
)
},
Sum(
wList,
OvertimeHours
) + 38 + DataCardValueHoursOTWorked
)

[WarrenBelz · Accepted solution]

@Lorem1960 ,

This is very difficult without the actual app to test and I cannot see a circular reference in the two items you are getting the error on, however using cascading calculations on labels is never a good idea, so try this on DataCardValuePercentCreate

With(
   {
      wWeek:
      With(
         {
            wList: 
            Filter(
               OREPayrollOvertime,
               Week = Value(DataCardValueWeekNew.Text) && 
               EmpID = GalleryOTResManagers.Selected.ID
            )
         },
         Sum(
            wList,
            OvertimeHours
         ) + 38 + DataCardValueHoursOTWorked
      )
   },
   If(
      wWeek <= 40 &&
      Weekday(DataCardValueDateOT.SelectedDate) > 1,
      100,
      wWeek > 40 &&
      Weekday(DataCardValueDateOT.SelectedDate) = 1,
      200,
      150
   )
)

and forget about LabelTotHoursWeek

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

[Lorem1960]

Ok thanks, just pasted the formula into DataCardValuePercentCreate Control

Just having this small issue with the WeekDay function that I need to try to figure out. Thx.

[Lorem1960]

Ok I changed the formula to this and it works as expected:

With(
{
wWeek: With(
{
wList: Filter(
OREPayrollOvertime,
Week = Value(DataCardValueWeekNew.Text) && EmpID = GalleryOTResManagers.Selected.ID
)
},
Sum(
wList,
OvertimeHours
) + 38 + DataCardValueHoursOTWorked
)
},

If(
wWeek <= 40 && Text(DataCardValueDateOT.SelectedDate,"ddd") in ["Sun","Mon","Tue","Wed","Thu","Fri", "Sat"],
100,
wWeek > 40 && Text(DataCardValueDateOT.SelectedDate,"ddd") = "Sun",
200,
150
)
)

Thanks.

[WarrenBelz · Accepted solution]

@Lorem1960 ,

Type in code (now fixed) - Weekday has small d. So is this now solved?

[Lorem1960]

Exactly, it is solved. Many many thanks.