0

Displaying Data in View by Dates

Hello, 

I don't know if anyone can help, but I assume this is quite a simple task for the more advanced users,  I am trying to display data in a table view, whereby the row of data only appears by what tasks are due for the day, week and month.

Please see the image and the code I am trying to use, also please note that I am filtering the data by user only, as it will appear on their user dash when they log in.

The formula that I am trying to use is listed below:

 

switch 'User Task Choice' do
case 1:
    (select Tasks where 'Allocated to' = user() and 'Due Date' = today())
case 2:
    (select Tasks
        where 'Allocated to' = user() and
        'Due Date' < date(year(today()), month(today()), day(today()) + 5))
case 3:
    (select Tasks where 'Allocated to' = user() and 'Due Date' > month(today()))
case 4:
    (select Tasks where 'Allocated to' = user())
default:
    (select Tasks where 'Allocated to' = user())
end

 

any assistance would be much appreciated.

Thank you.

 

11 replies

null
    • Fred
    • 8 mths ago
    • Reported - view

    You can try the following changes:

    let getUserTasks := select Tasks where 'Allocated to' = user();
switch 'User Task Choice' do
case 1:
    getUserTasks['Due Date' = today())]
case 2:
    getUserTasks[yearweek('Due Date') = yearweek(today())]
case 3:
    getUserTasks[yearmonth('Due Date') = yearmonth(today())]
default:
    (select Tasks where 'Allocated to' = user())
end

    I moved the select with the where of user() into a variable at the beginning since that is the one constant. Then we can filter depending on user selection

    Since case 4 is the same as default, you can remove it and just use default.

    You may want to consider adding a formula fields for each of the following date commands to your Tasks table so you can make filtering and summaries easier:

    year('Due Date')

    month()

    week()

    yearweek()

    yearmonth()

    quarter()

    yearquarter()

    • Terry_Prokopchuk.1
    • 7 mths ago
    • Reported - view

      Thank you Fred, Works perfectly.

    Appreciate your help on this.

    • Fred
    • 7 mths ago
    • Reported - view

    oops forgot the default case:

    let getUserTasks := select Tasks where 'Allocated to' = user();
switch 'User Task Choice' do
case 1:
    getUserTasks['Due Date' = today())]
case 2:
    getUserTasks[yearweek('Due Date') = yearweek(today())]
case 3:
    getUserTasks[yearmonth('Due Date') = yearmonth(today())]
default:
    getUserTasks
end

    If everything works then please mark the post answered.

      • Terry_Prokopchuk.1
      • 7 mths ago
      • Reported - view

       Thanks again, just on a quick note, I noticed that even though the table view allows to display the tasks issued to the specific user, if another user is using the filters then it changes everyone's view, is there a way to stop that at all? Thank you.

      • Fred
      • 7 mths ago
      • Reported - view

      If you edit a field, then click on More options you will see a field called Binding. Change it to Per record in memory (browser).

      That way the selections made by users will not be sent back to the server.

      • Terry_Prokopchuk.1
      • 7 mths ago
      • Reported - view

       excellent, you are the oracle! Thank you once again. 

    • Terry_Prokopchuk.1
    • 7 mths ago
    • Reported - view

    Hi Fred, I have tried to expand this further by adding options to sort by user (or all), which is working great on the current lines below, however, I am now looking to sort the data further also by allowing the user to sort further by category I have tried a few different combinations on my limited knowledge but can't get anything to work, would you have any suggestions. Thank you.

    let getUserTasks := (select Tasks where 'Allocated to' = user());
    let getAllTasks := (select Tasks where 'Allocated to');
    let Cat := Sort_Category;
    let Switch1 := switch Select_Task_View do
        case 1:
            getUserTasks['Due Date' = today()]
        case 2:
            getUserTasks[yearweek('Due Date') = yearweek(today())]
        case 3:
            getUserTasks[yearmonth('Due Date') = yearmonth(today())]
        case 5:
            getUserTasks['Due Date' <= today() - 1]
        default:
            (select Tasks where 'Allocated to' = user())
        end;
    let Switch2 := switch Select_Task_View do
        case 1:
            getAllTasks['Due Date' = today()]
        case 2:
            getAllTasks[yearweek('Due Date') = yearweek(today())]
        case 3:
            getAllTasks[yearmonth('Due Date') = yearmonth(today())]
        case 5:
            getAllTasks['Due Date' <= today() - 1]
        default:
            (select Tasks where 'Allocated to')
        end;
    let SortCat := switch Select_User do
        case 1:
            (select Tasks where Cat = 1)
        case 2:
            (select Tasks where Cat = 2)
        end;
    if Select_User = 2 then Switch1 else Switch2 end

    • Fred
    • 7 mths ago
    • Reported - view
     said:
    however, I am now looking to sort the data further also by allowing the user to sort further by category

     When you say "sort" are you talking about filtering the found dataset even further?

    or

    You want to take the found dataset and sort it by a field alphabetically? or whatever?

      • Terry_Prokopchuk.1
      • 7 mths ago
      • Reported - view

       I would like to further filter the dataset.

      Thank you.

    • Fred
    • 7 mths ago
    • Reported - view
     said:
    I would like to further filter the dataset.

    Ok, then you have it setup quite well to add another switch. You currently have Switch1 and Switch2. Then you have the final if statement that shows one or the other.

    One possible solution is to put that final if statement into a variable then you can add a switch based on the category field. It would look something like:

    [previous code]
let firstStep := if Select_User = 2 then Switch1 else Switch2 end;
switch categoryfield do
case 1:
    firstStep[fieldname = 1]
case 2:
    firstStep[fieldname = 2]
etc..
default:
    firstStep
end

    May I recommend you remove all unnecessary selects from your code:

    let getUserTasks := (select Tasks where 'Allocated to' = user());
let getAllTasks := (select Tasks where 'Allocated to');
let Cat := Sort_Category;
let Switch1 := switch Select_Task_View do
    case 1:
        getUserTasks['Due Date' = today()]
    case 2:
        getUserTasks[yearweek('Due Date') = yearweek(today())]
    case 3:
        getUserTasks[yearmonth('Due Date') = yearmonth(today())]
    case 5:
        getUserTasks['Due Date' <= today() - 1]
    default:
        (select Tasks where 'Allocated to' = user())
    end;
let Switch2 := switch Select_Task_View do
    case 1:
        getAllTasks['Due Date' = today()]
    case 2:
        getAllTasks[yearweek('Due Date') = yearweek(today())]
    case 3:
        getAllTasks[yearmonth('Due Date') = yearmonth(today())]
    case 5:
        getAllTasks['Due Date' <= today() - 1]
    default:
        (select Tasks where 'Allocated to')
    end;

    First, my guess is that getAllTasks includes all records in getUserTasks, so you should put getAllTasks first then use that variable in getUserTasks.

    Second, lines 14 and 26 still have select statements that can be removed.

    I'm not sure where line 3 is used. Is the fieldname in the Tasks table also called Cat? Is it also a choice field?

      • Terry_Prokopchuk.1
      • 7 mths ago
      • Reported - view

       Thanks once again, worded like a charm :)

Login to reply

Content aside

  • Status Answered
  • 7 mths agoLast active
  • 11Replies
  • 142Views
  • 2 Following