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
-
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()
-
Thank you Fred, Works perfectly.
Appreciate your help on this.
-
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.
-
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 -
said:
however, I am now looking to sort the data further also by allowing the user to sort further by categoryWhen 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?
-
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?
Content aside
- Status Answered
- 6 mths agoLast active
- 11Replies
- 127Views
-
2
Following