Forums

Hi all,
​
I am loading a table with a bunch of names from a db table,
​
NO PROBS SO FAR.
​
I also currently have a separate table that allows a person to click their name via a view button and they get a count of their attendances based on what they attended, works great. Eg Alex clicks his name and sees he attended 3 training sessions as below, based on the DB read where conditions below.
​

Its based on a read function:
​

​
Gets its name_id from the address bar:
​

​
That part all works awesome. What I am after is for the same/copied and modified data base read to be able to produce those results in a table based on the result in the name column of that table (both name and count are coming from 2 seperate DB tables, name is identical field though):
​

​
So where is says meeting '0' it would produce the result say '3' based on the 'where conditions'
​
So can I have that where condition look at the result in the name column of that table, eg {data:table_name.Article.field_name}, instead of the current {data:name_id} and it would look at the name in the table column and go away to its db table get the results and provide the calculated answer?
​
Hope that makes sense!
​
Am using Latest Joomla and latest update of CC.

In your data read, just add another model, say "meetings". Set up the relation using one matching, foreign key in (whichever), related to (the first model), relation set up like

table1.field_name:table2.field_name

​
In fields to retrieve for the first model, retrieve all the fields you need as well as COUNT(table2.meetings):model1.count_of_meetings

Thanks for the reply, I know for sure that I am lost!
​
The end result I want will be like this but with the meetings field populated:
​

​
The db table that gets the names into the CC table (Mem-Cnt-Stn-Table) looks like:
​

​
The db table that gets the meeting in looks like below, however this is multiple name with multiple events, I group the names based on the DName column and return a count based on a filter of the event type (DEvent) and count the results through the read. The one below is for where the read only has to return based on a single name from a list, the user clicks the view button next to their name:
​

​

​
The names for the CC table are called from this read:
​

​
This is where I tried to follow your instructions but dismally failed Im sure.
​

​
This is the View within CC.
​

​
Im going to keep playing to see what I can manage, but any help is greatly appreciated.

In related to and the relation field use model and model.fieldname NOT database_table_name

Im lost as to the sections in which to place the text your providing!
​
Do I put that into the Data read that is getting the names that I want to use to filter (Read_Mem_Cnt_Stn) or the data read that gets and filters the attendances (Read_Names_Chart_Meeting_All)? Do I add the additional Model in to which one?
​
Do I need separate reads at all?
​
You gave me this:
​

table1.field_name:table2.field_name

​
Do I put the 'model' name in where you have table1/2? I only have one table (Mem-Cnt-Stn-Table). Both reads are being called into that one table. The field name I am also not sure of. Field name being called?
​
For the name field is easy, I think. Into the table it is Article.name, coming from the DB that is in that read. The meetings count is tricky, the ready is set up to do a 'Return the count of records matching the filtering conditions.' based on the 'Where Conditions' below. It returns a result not from a field. The data is counted from a field single field in the DB called 'DEvent'

Read_Meeting.DEvent:Monthly Meeting
OR
Read_Meeting.DEvent:Officer Meeting

This other code you gave me:

COUNT(table2.meetings):model1.count_of_meetings

Im not sure where to put this, which 'read'? The .count_of_meetings field name has me lost, where does this come from?

Ive got it returning all the right numbers, but it's total for all, not breaking down by the name in the Name column.
​

table1.field_name:table2.field_name

This goes in "Relation conditions" in the second model. table1 should be the first model name, table2 the second model name. Replace field_name with the name of the related fields in each model. When I say "second model" I mean in your first data_read action, there's a button that lets you add another model to read additional data from a second table that is related to the first.
​
You say you're getting the information only from one table, but from this:

both name and count are coming from 2 seperate DB tables, name is identical field though)

you're saying there are two tables. So the relation condition SOUNDS like it should be model1.name:model2.name or something like that. But really, using a name as a primary key is a bad idea - what if two people have the same name? You should be using an ID. But that's a discussion for another time.
​
​

COUNT(table2.meetings):model1.count_of_meetings

This one goes in model1 under "fields to retrieve". You may need to tick the "these are additional fields" checkbox. It says "count the number of meetings in table2, and call the result model1.count_of_meetings". You may also need to add "model1.primary_key_field" to your "group by" in the first model.

Yep you need to use the group by field.

Do I do this in the DB read that calls the names to the table from one DB table or the DB read that reads the records I am trying to count that comes from a second DB table?

Is there anyway I can make this easier for you to understand, I think its mainly my description of the issue and how I have it set up that is causing a problem. Im really struggling with this. It really is above my paygrade.

If you want it all displayed in one table view, you should read all this in one data read. If you're really struggling I offer paid professional help and I'll just do it for you.

I am going to try your suggestions, if I get stuck I may need to get your help. Its for my volunteer bushfire brigade so would need clearance to spend money.
​
If I do this in a single db read how would I do the filtering for different activities?
​
At present I can do it in a where conditions section, but that is only for the main model, the extra models dont have that. Would that be done in the grouping?
​
In the where conditions I have it like:
​

Read_Meeting.DEvent:Monthly Meeting
OR
Read_Meeting.DEvent:Officer Meeting

or fir the training count like:
​

Read_Training.DEvent:Hazard Reduction
OR
Read_Training.DEvent:Training Event - Station
OR
Read_Training.DEvent:Training Event - District or State

OK look we're getting confused here. Do this please:
​
Give me two table schemas like this
​
#__TABLE1
ID--NAME---FIELD3---FIELD4
1---Pete---------x------------y
2---Lassie ------w -----------z
​
#__TABLE2
NAME-----MEETING-----DATE
Pete--------blahblah----23/7/2018
Pete--------blahblah----24/7/2018
Lassie------blahblah----18/7/2018
​
And then give me exactly what you're trying to have the table display
COLUMN COLUMN2 COLUMN3
----x------------y--------------z

I have done some digging and looked at the DB Tables and would probably be best to do from the one DB, that way I can break down Year by Year in the future and have historic versions for prosperity reasons. Also gives a full display of every name that has attended our station in that year and what they attended. I have attached a txt export of the DB file below just in case you need it. But it looks like this:[file=11497]iyfgmm5ci_chronoforms_data_diary_names.txt[/file]
​
#Table 1 (Diary_Names)

​

​
​
The DRandom Column is to correspond it to the event details , notes etc. Kept in a different DB Table, not relevant for this.
​
Need to filter by DMembership, as some sign on as visitor, I would just do an /asc to put them at the end, and sort the Names Alphabetically also.
​
DCount field is left over from before I discover the ability to return a count function based on records. Excel hangover.
​
​
​
Result would be in the displayed Table, like this
​
Name Meeting Training Other
Connie 0 2 3
Melina 2 3 3
Jordan
Gavin
etc
etc
etc
​
Meeting is a count of all the events entered in the DEvent Column that the member attended that meets the condition of:
Monthly Meeting
OR
Officer Meeting
​
For training the conditions are:
Hazard Reduction
OR
Training Event - Station
OR
Training Event - District or State
​
Other is everything else.
​
The reason for this is for voting rights, members need to meet certain attendance criteria and we also need to keep an actual diary of attendance.
​
Effectively I am trying to do a pivot table, with each member getting a counts of the types of attendances they have attended that fall into each of the 3 required categories.

If data can be stored just once it should be. You might need to do a bit of a read up on data table design. I will look atwhat you have tomorrow, bed time.

Spoke to the executive at the station, we are able to make a donation for all your help.
​
Thanks for all your help.

Can you PM me access details, easier for me to work through it with the database.