Count based on three tables based on ID search

svenschuldt · 2022-01-19T15:19:45.068Z

Hi,

another challenge I am facing

I have three tables:

Table 1 - DB_project: showing all projects based on a projectID
Table 2 DB_milestone: showing all milestones based on a milestoneID and connected to table 1 via the projectID
Table 3 DB_todo: showing all todos based on a taskID and connected to table 2 via the milestoneID

image1475×528 108 KB

I am now trying to show a repeating groups which shows the number of milestones of a project (done!) and the number of total todos of a project (problem )
Basically, I am basically trying to count all todos which are related to milestones, which are related to one project.

I have tried it with setting up a search within a search, such as :

image713×357 44.5 KB

image711×374 41 KB

… but that does not work so far.

I cannot get my head around how to put the Do search and :count algorithm together to achieve that.

Anybody has any idea to help me out?

Thanks!

mikeloc · 2022-01-19T15:56:57.197Z

Hi there, @svenschuldt… if I understand your post correctly (and using a recent post from you that I happened to remember to get a bit more context), I think you should be able to get a count of the to do’s like this… Search for DB_todos:count with a constraint on the search of milestone_id = Search for DB_milestones:first item with a constraint on the search of 1_project_id = Parent group's DB_project.

If you wanted to simplify things a bit, you could have a field on the DB_todo data type that stores the project with which a to do is associated. Also, just a heads up… the id text fields you are showing are not creating the links between the data types. Those links exist because you have tied the fields in DB_milestone and DB_todo to the actual data types, and therefore, the id text fields probably aren’t necessary.

Hope this helps.

Best…
Mike

svenschuldt · 2022-01-19T16:31:54.518Z

Hi Mike,

thanks for the prompt support … and yes

and using a recent post from you that I happened to remember to get a bit more context)

I am struggling quite a bit

When you say:

with a constraint on the search of `milestone_id = Search for DB_milestones:first item

that would not give the total number of todos, would it, as every project has potentially (and realistically) more than one milestone and the way I read it , this limits the search to the first milestone available, right?

If you wanted to simplify things a bit, you could have a field on the DB_todo data type that stores the project with which a to do is associated.

I thought about that - but this is a somewhat different DB design. This is an extract (there is way more to it) of the DB design structure as I have planned it so far:

image1113×801 38.2 KB

The point that you make with regards to:

Also, just a heads up… the id text fields you are showing are not creating the links between the data types.

… is very interesting! Obviously I have a misunderstanding here:
every record in every DB on my side has a unique ID and this ID is being used to connect it with other records. If you say now that

Those links exist because you have tied the fields in DB_milestone and DB_todo to the actual data types, and therefore, the id text fields probably aren’t necessary.

I understand that but they are connecting to the text fields in the first place - the project_ID is a text field and is being referenced within the DB_milestone, right? Same with milestone_id, which is referenced within the DB_todo.
If I would not have them in the first place, how could I tie the tables together?

mikeloc · 2022-01-19T16:57:08.158Z

svenschuldt:

that would not give the total number of todos, would it, as every project has potentially (and realistically) more than one milestone and the way I read it , this limits the search to the first milestone available, right?

Yup, you’re absolutely right… sorry about that… I should have been able to think that one all the way through. I’m guessing you could do it with an advanced filter, but if it was me, I would probably add a field for the project to the todo data type.

svenschuldt:

I understand that but they are connecting to the text fields in the first place - the project_ID is a text field and is being referenced within the DB_milestone, right?

No, that is not correct. The 1_project_id field in the DB_milestone data type has a field type of DB_project, and that field type ties it directly to a project without needing to make any sort of additional link via a text field. So, to be clear, you tie data types together by including a field in a data type that has a field type of another data type (which you are already doing). Make sense?

mikeloc · 2022-01-19T19:10:36.092Z

So, as I mentioned, I think an advanced filter could work, and I think this one might do the trick.

filter875×395 31.3 KB

However, I wouldn’t go that route because as you may or may not know, advanced filters do the filtering on the client side. So, in this case, the system would return all of the todo’s in the database to the browser and then do the filtering there, and that would obviously not be ideal from a performance perspective.

svenschuldt · 2022-01-20T05:11:07.982Z

Thank you - that works. I was not aware of the Advanced option, which provides definitely more flexibility.

Therefore I was also not aware that

advanced filters do the filtering on the client side.

which indeed can turn out to be an issue, with large amounts of data. Is there any documentation describing what is being processed server and what client side?

you tie data types together by including a field in a data type that has a field type of another data type (which you are already doing).

That opens up an interesting aspect - the connection is to the data type and eventually the record as such and not via a specific field. If you look at my DB design picture, this reflects a different concept - sort of old fashion apparently .
I am wondering though what is happening in the background of this process. Some sort of index must be used for tying it up.