Reporting unique values
I have a simple database of two tables: "Clients" and "Help Received." The "Help Received" table has a reference to the "Clients" table and a "Help Date" field. Every month I have to report the total help received, which I can easily do, I also have to report the number of unique clients helped during that month along with the number of family members of each type (seniors, adults, children). this data is contained in fields in the "clients" table. Which table do I use to create the necessary view, and what expression do I use to filter the records? Alternatively, do I have to change the structure of my database? e.g., have a separate "Date helped" table. In that case, what references are needed?
7 replies
-
Let's assume that "Help Received" is a COMPOSITE table of Clients.. To get the unique clients helped for February .. it would be something like (disclaimer.. I did not run this through ninox.. so I might have a syntax error.. but hopefully you get the point).
let uc := cnt((select Clients)[cnt('Help Received'[month('Help Date')=2]) >0]);
basically .. you are saying select clients that have more than 0 'Help Received' rows wehre the month of the 'Help Date') is Feb (or 2).
Because I am assuming that 'Help Received" is a COMPOSITE.. you do not have to worry about linking by Clients.Id... Ninox does that for you.
Hope that helps.
(again.. I did not put this into Ninox.. so your mileage may vary .. :) )
-
Thank you for your input, but I'm afraid I don't know enough to fully understand your help. As I understand it, the "Clients" table has a composite key: The last name and the first name uniquely identify each line of the table. The "Help Received" table has a reference to the "Clients" table such that the Help received by each client on different dates shows up as a subtable on the Client "Card" view. The "Help Received" table is not a composite table as best I can make out, except that the help date and reference to the Client table kind of makes it so. One can construct a view from the "Help Received" table whereby a table of help received by clients is listed by date. The problem is that I have to report the number of unique adults, children and seniors helped in each month and the resulting table produces a line for each time helped resulting in multiple lines for each client in a month. I might try the expression, if I knew where to put it. Where do you enter it? I'm reluctant to put all this data into a single table because that would mean repeating all the client data for every Help Received date.
-
Hello,
That would be a good question for our webinar and it would be nice if you could bring it up there.
Thank you very much.
At our Ninox webinar tips and tricks will be presented in the first 30 minutes, the remaining 90 minutes will be used for a live support where the participants' concerns will be solved.
You can register here for the webinar, which takes place every Tuesday at 18 o'clock CEST: https://zoom.us/webinar/register/WN_zoSk2qyNSz6vLRFF1zlakg
Best, Jörg
-
@basspt.. Most certainly take Jorg up on his offer.. Ninox offers AMAZING support ..
Here is a link that shows the view of Client's helped as described above.
https://www.dropbox.com/s/y4fbze163fofag6/Client%20Help.ninox?dl=0
-
@basspt ... once you import the above archive.
1. Click on Clients table.. you will see some clients.
2. Click on row 1.. First Name 1.. You will see they have recieved help twice.. the "Help Recieved" table is a "composite" table of Clients.. Simply meaning that if you delete the client row.. The associated Help Recieved rows are automatically deleted.. :)
3. Click on the Dashboard table.
4. Enter a month... 1 .. or 2 for Jan / Feb... I did not get fancy with a choice box.. You can..
5. Notice the View will change..
That should give you all the piece parts ...
-
Thanks to both of you. I'll do the webinar at the first opportunity.
-
@basspt ... You bet.. Happy NinoxIng.. :)
Content aside
- 5 yrs agoLast active
- 7Replies
- 3017Views