Building a query from Monday board

[johnners]

Hi,
I’m looking to query a Monday time tracking column that has multiple start/end times logged.

I need to check on each item on a board, and only show the total time for today.
I’ve started out by just checking one item that I’ve hard linked.

This is the output:

I’m trying to filter by only today - but it seems to be failing.

Any ideas how I can get it to filter just today?
Also, how can I get it to check all records for today and add them?

Thanks

[Bjorn.drivn]

Hey @johnners ,

So right now there are 2 things:

1. You are using an array without iterating over the array. By selecting a field which is an array, it will only pick the first item of it. You can identify this by the field having 2 brackets [ ]
2. The filter you are creating will not work since you are working with date values and selecting a text fiter

Solution

1. Put an iterator behind your monday module, and iterate over the field called additional value. This way it will iterate over each item and you can select the fields within each item after the iterator.
2. For the filter, use the formatDate() function to also formate the date coming out of Monday. Then use the “date” filters and select it to be equal to the current date. Also remove the curly brackets { }

Hope this works. Goodluck !
~Bjorn

[johnners]

@Bjorn.drivn
Hi there,

Thanks for that, I’ve added an iterator, but I’m not sure if I’m meant to run the filter after that or before?

This is my current set up:

I’ve then tried to run the filter using:
formatDate(now;YYYY-MM-DD) and equal to, but its not matching as its pulling 2022-07-01T15:13:00Z - I’m unsure how to get it to format in the way I need it

[JimTheMondayMan]

@johnners

You’re close…

1. The filter needs to come AFTER the iterator.
2. The filter condition should reference the values from the iterator (not the Get Item module)
3. Condition should be something like this: {{substring((START_DATE_FROM_ITERATOR); 0; 10)}} is Equal to (text) {{formatDate(now; “YYYY-MM-DD”)}}

---

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you?
We Create Custom Solutions
Schedule a 1-on-1 Tutorial Session (for monday, Make & Base Riffs)

[johnners]

@JimTheMondayMan

Thank you for your help with this too!
I’m hoping to run an update which will work out how long I’ve spent on a project today and then insert it in to a column on my board - so if there are 4 different start/end times for today it will add them together too - am I heading in the right direction using this so far or am I going to run into some huge issues as I build this query?

I was planning on doing.

Monday get item column value → router 1. → iterator for start time, 2. → iterator for end time, then back to another router to join the results together then a simple end time, minus start time calc? - then insert in to column

[JimTheMondayMan]

@johnners

Monday get item column value → router 1. → iterator for start time, 2. → iterator for end time, then back to another router to join the results together then a simple end time, minus start time calc? - then insert in to column

You don’t need a router or second iterator. After the filter, put a Set multiple variable module and calculate the time there, followed by a Numeric aggregator (in tools)… That’s not necessarily the “best” way, but, it’s the easiest. You answer will be the result of the aggregator… then add what you need to update monday.

---

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you with monday?
We Create Custom Solutions
Schedule a 1-on-1 Tutorial Session (for monday, Make & Golf Tips)

[johnners]

So I set up the filter after the iterator like this

however its throwing up this -

So I’m guessing ive got it formatted wrongly somewhere?

[johnners]

I’ve been fiddling and I’ve managed to get the filter working!

Thanks so much for pointing me in the right direction

[johnners]

So I now have the above - which is now working - up to the numeric aggregator.

So I now see 2 outputs in the aggregator - 7200 (2 hours) and 3600 (1 hours) so how can I add the outputs together?

I’m guessing I can join the operations and then sum the result but cannot see how to do it?
Any ideas?

[D-EFFCON]

Your numeric aggregator source needs to be the iterator, not the Set multiple variables module

[johnners]

Hi @D-EFFCON,

I’ve just updated it but it now seems to be outputting the same thing several times though.
So I thought I’d run a filter which just shows me as the user

I’ve attempted to group it by the user id too.

So I get 5 records from my time tracking column, then to the iterator, which has 5 records too, then the filter above which is supposed to on show the records from today (only 2) but then it shows 25 bundles - although not all of them pass the filter -

Then in to the numeric aggregator which then shows 5 operations but all 5 operations shows the same result.

Any ideas what I’ve done?

[D-EFFCON]

Can you show me what you setup under the “Setup Multiple Variables” module?

[johnners]

@D-EFFCON
I’ve changed a few of the modules - This is my set up:

This is my search items module

This is Get an item

This is the iterator

This is the filter

This is the numeric aggregator

[JimTheMondayMan]

@johnners

The reason that you are getting the same data 5 times is that the first module is returning 5 items. So, everything after that is running for each of those items.

[alex.newpath]

What @JimTheMondayMan says is a key thing people don’t understand about Make. The output bundles drive the rest of the scenario. Make sure you clearly understand the effect of the order of the modules, the filters and how you’re generating bundles. This is a design decision you’re going to have to deal with in every single scenario you build.

[johnners]

ah ok, so the filter wont change the number of bundles then? I was assuming I’d be able to use the filter to run a query. I’ll have to look at the very beginning again and see if I can query it a different way from the start.

[alex.newpath]

The filter will filter which bundles are processed but it won’t change the number of bundles generated by any one module.

[johnners]

ok, so because it finds 5 different results in the first search, even when I run the filter it will show those same results 5 times. That could be a little issue if I have 200 results found in the first search. Seems like I’d be causing a lot of work without it needing to be like that - or am I thinking about this the wrong way?

[johnners]

Maybe it will help by describing what I’m doing.

I have multiple projects, with multiple time trackers running. I want to search for the projects that are scheduled today (so my very first search is to just list all the projects where scheduled date is today).

then I get a column value - which is the column Drew Time Tracker. ( I have 8 different time tracker columns that I need to check)

Then into the iterator which looks and takes away the start time from the end time (giving me the total time tracked today).

Then my plan is to insert that in to a column on a different board.
then it updates every minute mon-fri, 9am- 6pm.

so that way each person can look and see how long they spent on the project today rather than having to check the logs of the combined time tracker.

[alex.newpath]

Sounds like a plan. Show us an example of what you’re doing and we can see if we can help. A picture is worth 1,000 words with Make. You know what I mean?