Problem in Calc_Dynamic

Hi Rafael -

I've poked around and came up with a model that will work with weeks.

You can see it in the uploaded version. Here are the changes to the Trigger on Update of the Choice field:

let vInfo := "";
let base := (select HOURS_Week);
switch text(Total_Choice) do
case "Total":
    (vInfo := text(sum(base.HOURS)))
case "Current_Week":
    (vInfo := text(sum(base[WEEK = week(today())].HOURS)))
case "Last_Week":
    (vInfo := text(sum(base[WEEK = week(today()) - 1].HOURS)))
case "Last_2_Week":
    (vInfo := text(sum(base[WEEK >= week(today()) - 1 and WEEK >= week(MONTH)].HOURS)))
case "Last_4_Week":
    (
        let cWeek := week(today());
        let newbaseRecords := (base[WEEK <= cWeek] order by WEEK);
        let cntNBR := count(newbaseRecords);
        let last4recs := slice(newbaseRecords, cntNBR - 4, cntNBR);
        vInfo := text(sum(last4recs.HOURS))
    )
default:
    (vInfo := "")
end;
'∑ Total_Choice' := number(vInfo)

My first change is on Line 2 where I do only 1 select function for the entire script.

For your cases you had month(WEEK) and year(WEEK) so I took those out and just used WEEK since that is already the week number.

The big change is for Last_4_Week, since we need to know the current week we are in then find the last 4 records. I first started with just using the week number, but then realized that you don't have all weeks represented.

That means I had to find the last 4 "weeks" that are recorded from the current week.

Background on lines 14 - 18.

Line 14 creates a variable that finds the week number of the current week.

Line 15, creates a new variable that filters the records found in the base variable to only include the records where the Week number is less than or equal to the week in Line 14 then orders the records by ascending Week number.

Line 16 creates a variable that counts the number of records in Line 15.

Line 17 since I kept everything in an array, I can then uses the slice command to only find the last 4 records in the variable from Line 15.

Line 18 then sums the four records found in Line 17 and then updates the variable vInfo with the sum.

About your first issue, dynamic choice fields returns records from a table. And since your select statement is to get the whole table then you will see all records. There is no way to do a unique statement on the results since doing so will reduce the results to a text string and then you lose the dynamic ability of the field.

I'm sure someone has figured out a way to filter out just one of a set of records where data is exact and return all other records and keep it in an array, but I haven't. So the only way I can say to solve it is to have a clean table that only has 1 record for each week you need represented.