Find Last (or max) row in a related table

[agtjazz]

I am being asked to run a report/list that has all job code=2358 that are active or inactive. If there is more than 1 “2358” job table row, then please pull the last 2358 row.

I have a list… but it’s incorrect

Jane Smith is fine

Bob Smith pulled but the wrong row displays

Joe Bing displays the 1^st row.

And Pat Chauncey is correct by not showing

Thank you for any guidance

FIND2358.fp7.zip

[LaRetta]

Two things I changed: 1) You did not have a JobID and every table should have an auto-enter serial or UUID and 2) I added custom dialog so you did not have to hard-code the Job Code into the script.

Notice that the Job fields now placed on the List (people's layout) is from the LastJob table occurrence which is sorted by JobID because they are always in order (or should be sequential). If by 'last' you mean a date, change the relationship sort on the LastJob side to whatever signifies 'last.' :^)

FIND ANY jobcode.zip

[agtjazz]

Hi... Thank you LaRetta. I have been working on this for a while now and even imported your script into my "play/test" database but can't get it to work. Could anyone help with what I am doing wrong? I just am not getting it.

<I made the job primary key, and added the lastjob table occurence to be sorted descending. I also made the globaljobcode field in the people table> but still no luck.

Any assistance would be great.

FIND2358.fp7 2.zip

[LaRetta]

It works for me! Please tell me step by step what you input into the custom dialog and what happens and what you do NOT get that you think you should!

If I input 2358, I get all records but one ... the Person without a Job 2358. ? We are happy to work through it with you. :^)

[agtjazz]

I go to the script... Find People's with Job Code... I type in 2358 and the list shows different results - job codes with 1851- 2482 etc. I have screenshots to document my step by step. Thank you in advance

PS- just to clarify. It works in your Find Any Job Code database, but when I try to implement the method you showed me into my test database, I don't get the correct results. Maybe the relationship is off or I am missing something obviously. Thanks again.

Archive.zip

[LaRetta]

But the job fields on that list layout are from the 'many' side of the relationship to these people and it always only shows the first job in the table. Search for 2358 and three people are returned. It shows Jane Smith and it shows Job 1851 (but Jane also has a 2358 job code). And THAT job code is JobID 301 which is the latest job code in Jobs table.

Switch to the People layout now and see what Jane shows.

ADDED: So this tells you that Jane has the latest 2358 job code and it will be the first record returned in the list. Looking into the many side of a related table can throw you off because you see the first record but we can't remove 'related' records just because we restrict the parent's search. Make sense?

Edited October 12, 2012 by LaRetta

[LaRetta]

Well egg on my face ... I didn't check the table occurrence of your jobs fields on that People's list layout so that's what isn't quite right in your file. Double-click them and point them to the LastJobs table occurrence instead of the Jobs one. Sorry I didn't catch it sooner. I had forgotten our original goal of the thread. :idot:

[comment]

Here's another way to look at it:

LastJobcode.fp7.zip

[LaRetta]

And a better way in my opinion, with smaller footprint. Thanks for chiming in.

[agtjazz]

Thank you both! I could not get Comment's method to work on my actual database.... and I am almost "there" with LaRetta's method. I could duplicate behavior in test databases, but when I put it in a backup copy of my actual database, I get blank lines--almost blank lines. It shows the name of a person, but not the lastjob fields. Any suggestions?

[LaRetta]

I would suggest that you take another look at Comment's file since it is better method. You create a report layout with a leading group by PersonID (if I recall) . select PersonID and the other fields you want. After the report is created, move the fields up into the leading part then delete the body. Pay attention to how his report is sorted.

His method does not require the additional table occurrence. :-)

[agtjazz]

Will do... thanks

[agtjazz]

I think I almost have it (though not really sure why) using Comment's method. Instead of showing the last job record, it shows the first. I am not completely understanding so it's hard to troubleshoot. Any advice? Thank you for all the patience- I hope it's something simple/obvious.jobtable.fp7.zip

[LaRetta]

Ooops. The report is generated from the Jobs table (in Comment's file). Upon re-read, I didn't remind you about that difference, sorry.

[comment]

I am not completely understanding so it's hard to troubleshoot.

The idea is to summarize the found jobs by person and show only the sub-summary part. In the example, there are 6 job records found, but only three distinct people - therefore only three rows are shown.

Fields placed in a leading sub-summary show data from the first record in the group. Since the records are sorted by person and then by date, descending, the sub-summary by person displays data from the latest found job of this person.

[agtjazz]

Thank you to both of you! You have been extremely helpful and patient. I have it working thanks to both of your advice and explanations on my test database... I am going to move the solution onto another test to make sure I can replicate the list and then onto my live database. I really appreciate all your help and time. :)

[agtjazz]

Hello. I am not sure if I should have started a new topic or not. I can't use the "save as excel" or export this report, even with a constrain. Any advice?

Thanks bunches!

[Lee Smith]

If you need it as excel, have you tried Save it as Excel?