Topic: Filter a TableGrid

Hi Everyone,

I have a tableGrid that has a bunch of service records for different pieces of equipment.
I would like for the tableGrid to show only the records with the latest Service date.
.
Equip                Service Date
    Alternator                8/1/2020
    Alternator                8/15/2020
    Alternator                8/20/2020
    Engine                    7/1/2021
    Engine                    7/15/2021 

.
So for the Alternator I would like to see only the 8/20/2020 record.
And the same for each piece of equipment.
.
I'm thinking I might be able to put a select statement in the filter section of the tableGrid but I don't know how I might format the statement...if it's possible or adviseable.
.
Thanks
Frank

2 (edited by v_pozidis 2022-08-21 12:07:45)

Re: Filter a TableGrid

Something like that. Hope that it will help you

Post's attachments

Attachment icon date.zip 325.37 kb, 137 downloads since 2022-08-21 

Re: Filter a TableGrid

Hi Frank,
Knowing a bit about your project of old, you could always use the 'equipment' table as lead and use a calculated field (held against the 'equipment' table) so that you only see one row per item of equipment (tablegrid2 in the attachment).
Or you could use the 'services' table as lead (as it looks like you're currently doing) and, again, use a calculated field (this time held against the 'services' table) and then filter on that (tablegrid1 in the attachment).
Pros and cons to both, as always - the approach in tablegrid1 lets you toggle between 'all' and 'most recent' but, unlike tablegrid2,  won't show any items for which there are no service records
Personally, I would not be holding the 'most recent service date' in a table - issues like 'newest', 'oldest', 'lowest', 'highest' (in other words, anything that can change depending on other data in the table) etc are what calculated fields are intended for (but others may have a different view).
As always, it comes down to which (if either!) approach suits your requirement.
Derek.

Post's attachments

Attachment icon most recent service.zip 483.24 kb, 149 downloads since 2022-08-21 

Re: Filter a TableGrid

Hi Derek


In the first option, in the properties of the ALL SERVICES and MOST RECENT buttons,
you need to change the data table for searching from services to equipment.
Otherwise editing works incorrectly. In the second option, if there is a function for
editing or deleting a record, it is also required to change the search table.



But the solution is great.

Re: Filter a TableGrid

Derek, Sparrow,
Thanks for giving me some things to try.
.
I've been trying to work your suggestions into my program and I haven't had any luck. Trying to fold Derek's changes into my code has been a real challenge. I'm sure part of it is because my table structures are different (and probably messy).  My program has grown so much that when I start trying to make significant changes I wind up breaking something,  Then I have to go to my backup copy & try again.
.
Derek - The tableGrid2 is the result I'm looking for.
However I'm afraid your solution is above my skills level.
One thing I don't understand is what this does & how it works? {services.cfmostrecent} = "*"
I know it's the calculated field but I've never seen the braces and the * used like this.
.
Maybe you could explain it for me? 
.
Sorry to be such a pain
Thanks
Frank

Re: Filter a TableGrid

Hi Frank, Sparrow,
I probably didn't help matters by calling my calculated field cfmostrecent - calling it cfmostrecentflag might have been more appropriate.
The proposed solution (tablegrid2 in the previous attachment) shouldn't really cause any issues or require any changes to your existing structures and should just be held against the table that stores your service records. 
1.  create a calculated field on the table where you hold your service records.
2.  copy the syntax from the previous attachment, changing field and table names as appropriate.
The calculated field syntax is a bit unusual because for each record in the services table you are needing to interrogate the services table itself.  To do that you refer to the services table firstly by its usual name (services) and secondly by an alternative name - in my example, I've simply called it 's'.(I could have easily called it 'services2' but 's' is much quicker!).  If you have a look at the 'where clause' in the calculated field
"where (s.sedate > services.sedate etc etc)
all it's doing is taking each row in the 'services' table and referring to all the other rows in the 's' table and setting a flag (I've used an "*") only on the row that has the most recent service date for each item of equipment..
3.  add the filter to tablegrid2.  The braces {services.cfmostrecent} are used when you are filtering on a calculated field rather than on a stored table field.  Also note that the calculated field can be used as a filter even thought it isn't output as a column on the tablegrid.
As always, never sure if this helps or hinders but get back if you need more detail.
Derek.

7 (edited by sparrow 2022-08-22 11:40:05)

Re: Filter a TableGrid

Hi Derek, Hi Frank

Frank, Derek came up with a very elegant solution but it does require a little more knowledge.
But it all comes with experience, the main desire.
I dare to suggest a third option on Derek's uniform. It is less complex, although it requires an additional request button.
I also corrected the first option.
Now you have three options.


I'll try to explain it this way
In Derek's examples:
1. In the calculated field, for each row, the maximum date for this product is calculated and compared with the current date for output.
If it's really the maximum then the calculated field generates "*".
If the date is less than the maximum date, it is empty.
2. Regarding your question {services.cfmostrecent} = "*".
This is just a condition check. If the calculated field returns "*" then the condition is true and the date is displayed (MAX),
if it returns "" (empty) then it is false and nothing is displayed (< MAX).


Frank, lately you've been asking a lot and getting advice on how to solve your problems.
All questions and answers were given to you without regard to your database structure, forms, tables, etc.
It's getting harder and harder for us to tailor solutions and give you advice without knowing how they might fit into your project.
Perhaps it's time to "open the veil" of your project?
Perhaps you will make a simplified version or a version with a fictional base. Well, or show the structure of your base?
google translate.

Post's attachments

Attachment icon most recent service - fix.zip 335.94 kb, 128 downloads since 2022-08-22 

8 (edited by sparrow 2022-08-22 18:18:17)

Re: Filter a TableGrid

If you use some features of the program, automatic generation of a query as SELECT DISTINCT ... .
And at the same time, select the EQUIPMENT table for Grid as the main table.
All actions can be reduced to one simple calculated field. 4 table. Calculated field svc_date.


Unfortunately, this option is only possible under such conditions.
When a table is changed, the ID field of another table is included in the query, and the query works differently.
Frank, these are just examples and it all depends on the structure of your database.

Post's attachments

Attachment icon most recent service - fix2.zip 335.6 kb, 141 downloads since 2022-08-22 

Re: Filter a TableGrid

Sparrow,
Many thanks for your help with this.
.
I've been using tablegrid 4 from your example program and so far it's been working OK.
I do have one issue though.  The MAX(SvcDate) if coming out in the format of
2022-08-12 00:00:00.000. Right now this is the only thing that is preventing me from having this SVC tableGrid working the way I'd like.
.
Most of the other dates in my program are in the format mm/dd/yyyy
I've been trying to modify the select statement below and everything I try seems to break it.
I'm sure most of my problem is that I'm a complete novice in SQL.
Here's the code I'm trying to use and it's OK except for the Date format.
(SELECT MAX(SvcDate)                 
FROM tbl_service
where tbl_Service.id_tbl_Equip = tbl_Equip.id)

.
One thing you mention is SELECT DISTINCT.
I don't know what that means and is it something I should be using?
.
You also mentioned about providing help for me over time. You are correct.
When I started in MVD over 2 yrs ago, I was a complete novice.  Since then, yourself, Derek and others have been guiding and teaching me.  Thanks to your help I'm now able to do quite a few things on my own = PROGRESS.
.
I think I'm close to finished and then I see something I would like.  Much of it is straightforward to me now.  But when I get stuck you folks have always come to my rescue.  And I try to learn from all the work that you do.
I really really appreciate it.
.
I know I should include my code so you folks can see what I've got and I'll go ahead and do it.  However, I want to clean it up first because it's really messy.  I've been adding and experimenting for so long now that there's a lot of stuff that needs to be taken out of my Script file. During my journey I've run into some instances where I had some basic design flaws.  So I've had to make some necessary changes, and of course that breaks everything it touches. So it takes me quite a bit of time to get back on track.
.
If I had to pay you folks for all you've done for me, you'd be rich and I'd be broke.  Not that I could afford it LOL.
.
Thanks again for all of your help.
Frank
.
Thanks again

Re: Filter a TableGrid

Hi Frank

Use

(SELECT strftime('%m/%d/%Y', MAX(SvcDate))                 
FROM tbl_service
where tbl_Service.id_tbl_Equip = tbl_Equip.id)

SELECT DISTINCT you don't need to use.


You don't need to pay. It's nice to see when people are learning.

11 (edited by papafrankc 2022-08-23 22:54:07)

Re: Filter a TableGrid

Sparrow,
Your fix works great. The date format is now correct.
However now the tableGrid is showing ALL of the Service records.  It doesn't seem to be recognizing the MAX function.
.
When I run your sample program, the date comes out in the m/d/Y format.  I wonder why it comes out different in mine?
.
Thanks

Re: Filter a TableGrid

I checked everything is working fine.
I can't answer that without seeing your project.
The only thing that needs to be done after the adjustment is to set the type
of the calculated field to TEXT and not date.

Re: Filter a TableGrid

Sparrow,
I believe I told you that my code was pretty messy.  It got so bad that I had to go back to an older backup to get the basic program working again.  That got rid of a number of the trial & error stuff that I had been putting into the code.
.
Bottom line is that, right now, most things are working as I would like.  Unfortunately I had the bad habit of making some change that doesn't work and not removing that change before I go on to try some other magic solution. 
.
Now the date functions and formatting have straightened out so they look OK.
The bad thing is that I'm not sure what I took out that was screwing things up. I must have had some changes that were messing with dates??
The good thing is that I'm back on track to completing the program so I'm not going to spend a bunch of time trying to figure out what I did wrong. 
.
One of the neat things I learned from you is the comment code /* some code */ inside a SQL statement.  It lets me experiment without just erasing stuff.
.
Thanks for being patient and helping me out.
.
Frank

Re: Filter a TableGrid

Hi Frank,
Another small tip that might be of interest (see screenshot).
Instead of trying something in your script, finding out that it's not quite right but leaving it in situ and commenting it out in case you need to refer back to it, you could cut it and paste it below the final BEGIN....END in your script instead.
The end result is obviously just the same but it reduces the number of lines in the 'active' part of your script and help you to focus more on what's actually going on rather than having to hack your way through the jungle!
Derek.

Post's attachments

Attachment icon frank.jpg 87.44 kb, 56 downloads since 2022-08-26 

Re: Filter a TableGrid

Derek,
That's a great tip - thanks.
.
What I've been doing is putting things of value (or interest) in a Word Document for reference.
It's easy to search so that's a plus. In the 2+ years I've been using MVD, my Word Doc is now up to 41 pages.
Some stuff I've never used but others are very valuable.  It lets me find stuff that I've learned long ago so I don't have to rely on my aging memory. PS - don't get old LOL
.
But keeping things at the bottom of my script file, especially current items I'm working on could be real handy & save me a number of steps and time.
.
Thanks
Frank