0

Problem in Calc_Dynamic

Hi 

I have two problems with this models. 

  1. The Weeks field is Multiple Choice Dynamics can I groups by weeks ? In the example I have two hours on the same  week 2.
  2. In the Total_Choice,  field Choice the formula in Trigger after update Don,t work 
9replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 7 days ago
    • Reported - view

    In format of year work excelent.

    • Fred
    • Fred
    • 6 days ago
    • Reported - view

    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.

      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 6 days ago
      • Reported - view

      Fred Thanks again, a lot  of work 👋, and excelent explained. Work excelent.

      The only I change in the Multiple Choice Dynamic on Dynamic value name leave the week info 

      Really good work Fred.

    • Fred
    • Fred
    • 6 days ago
    • Reported - view

    I was wondering why you converted all the sums to text then back to a number at the end. I see that at the beginning you did:

    let vInfo := "";
    

    Which, I'm guessing, tells Ninox that vInfo is a text field.

    When I switched it to:

    let vInfo := null;
    

    then I can remove the all the text commands from the subsequent lines and the number command from the last line.

    let vInfo := null;
    let base := (select HOURS_Week);
    switch text(Total_Choice) do
    case "Total":
        (vInfo := sum(base.HOURS))
    case "Current_Week":
        (vInfo := sum(base[WEEK = week(today())].HOURS))
    case "Last_Week":
        (vInfo := sum(base[WEEK = week(today()) - 1].HOURS))
    case "Last_2_Week":
        (vInfo := 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 := sum(last4recs.HOURS)
        )
    default:
        (vInfo := null)
    end;
    '∑ Total_Choice' := vInfo
    
    Like
    • Fred
    • Fred
    • 6 days ago
    • Reported - view

    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.

    Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 6 days ago
      • Reported - view

      Fred 

      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. 

      Thanks for yout explain very clear now.

      sum(base[WEEK = week(today())].HOURS))

      I don't sure understand the base is a command ?

      Like
      • Fred
      • Fred
      • 6 days ago
      • Reported - view

      Rafael 

      Look at line 2, base is a variable.

      Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 6 days ago
      • Reported - view

      Fred OK 😞

      Like
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 6 days ago
    • Reported - view

    Again great job and working great. I hope this help a other people on the Forum.

    Is posible changed the Hours by Budget and works well too.

    Like
Like Follow
  • Status Answered
  • 6 days agoLast active
  • 9Replies
  • 50Views
  • 2 Following