This calc picks a job, but for a student who had two jobs, it's not picking the one with the highest job_id.
An example could be useful here (e.g. the two job id's are ... and it picks ...). Is job_id defined as a Number field?
The database is posted at:
Each students can have several jobs. We need data about their last job. Jobs are in a Job table, and each job has its own job_id. It can now correctly identify a student's last job. It's doing it by using a summary field to identify their maximum job_id. (It could also use the latest start date, but I don't believe that's the problem).
The problem is that the Last Job job_id can be identified correctly, but the related info so far cannot. For each student's last job, I need the agency name, the state where it was located, their hourly salary, etc. The "last job" info for those fields are currently linked to the right student, but not to their last job.
In the posted example, I have used red and dark rose to visibly identify some of the fields that aren't working properly in the Last Job layout, and in Report: Student (using the Last Job button).
The test is what is shows for Sippola, who is the only one who - so far - had multiple jobs. [BTW, these are just sample records, not real data]. His last job was Douglas Co. SS in WI (not Lutheran SS in WI, which was his first job). His info is correct in the portal (in the tabbed section of the Student Overview layout), but not in the Last Job layout, where FMP picks the right job_id for the last job, but shows other job info from the first job.
Related to "no repeating fields"?
I have no repeating fields, so the multiple jobs are each in their own row in their own table.
I had some trouble getting Max (job_id) to identify the last job, and read about the Extend () function. I was playing around with the Extend syntax, when I realized that I could use a summary field to identify the max job_id. Part of my current problem might be that I didn't solve this original problem in the best way possible. (?)
Thanks for looking at this!
If your report is based on the Students table, you can show the details of each student's last job in two ways:
1. Define the relationship to sort the related job records by JobID (or by a date field), descending. Then simply place the related fields from Jobs on the report layout. It will show data from the first related record in Jobs, which due to the sort order will be be the latest job.
2. Place a one-row portal to Jobs on the report layout, and set it to sort the same way as above. Place the fields from Jobs in this portal.
P.S. Do you really need so many sub-summary parts?
I haven't yet used a sorted relationship or a portal in a report, so I will try these things. Thanks!
Your P.S. made me laugh. Thanks for that, too. I have the same question. I think "they" used a shotgun to make sure they requested every possible summary they could imagine. I was able to organize it into the smaller number of sub-summary reports that are in there now.
Neither of these solutions worked for me, although the portal came closer to working.
The portal showed the correct records and fields, but I couldn't get it to group student by last agency (employer) (i.e., it would list each last job separately by each student, whereas I want the break field to be the last agency, and have the report list all students whose last job was agency a, b, c, etc.).
One possible complication is that the report is based on the Student table, the job_id comes from the Job table, AND the agency name comes from the Agency table. These fields are all related. They show correctly in the portal in the tabbed section of the Student Overview layout. The report should display as described above. (The Agency table has an agency_id as the pk, but that doesn't need to show in the report, so I think the agency_id doesn't matter).
Actually, #1 doesn't work for me, even if all I have in the sub-summary field is the job_id (even without the agency name). I have the relationship set to sort by job_id-descending. When running the report, I sort by job_id, both ascending and descending, but I get only the names of the students, not the info from the sub-summary part: Last Job & job_id.
If you want to show the agency's name too, you must use the one-row portal method. This is assuming the relationships are:
Students -< Jobs >- Agencies
Grouping the students by the (last) agency is something else altogether. Are you sure you need this? Because it's not going to be simple.
Yes, the one-row portal system showed the right data for each students last job.
I need to report multiple things in terms of data about "last jobs": with what agency, in what state, at what average salary, etc.
That's how I pictured the sub-summary report looking: not 50 "last jobs," each listed separately, but grouped by the employing agency, with a summary number showing how many students are listed for each agency. What would it involve to do this?
Another last-job statistic desired is the "last job type," where job_type has six values in a list. I'll need to count and/or list the last jobs that are in child welfare (which would be any of the first five types in the value list), vs. not in child welfare (which is the last item in the value list). (This might call for a case-statement).
I mention the last-job-type issue now in case it's similar to the current challenge of group students by last job agency. If I learn to group students by last-job-agency maybe it'll help in grouping and/or counting them by last-job-type. (?)
That's how I pictured the sub-summary report looking: not 50 "last jobs," each listed separately, but grouped by the employing agency, with a summary number showing how many students are listed for each agency.
There are several ways to do this, I will outline one. I am assuming here that the relationship Students -< Jobs is not sorting, and that Jobs are entered in chronological order.
In Students define a calculation field cLastAgencyID =
Last ( Jobs::AgencyID )
Use this field to define a relationship to a new occurrence of Agencies ("LastAgency").
For the report , sort and group the records by the cLastAgencyID field. To display the agency's details, place related fields from the LastAgency TO in the sub-summary part.
Hi, I'm back at this after overdosing on it in a series of super late-night sessions.
I am confused as to how to get all the info that's wanted related to a student's last job. I am trying to make sense of all that's in from earlier attempts, plus understand and follow your suggestion. Although your suggestion seems to produce the right agency name, it may not match the db structure, in that there are three tables involved, not two: student, job and agency.
Jobs are entered in chronological order, but agencies are not. One agency might employ many students in many jobs. (Each job has a start date and an end date). It seems like we should first identify each student's last job, and then get their last agency (i.e., the employer for that student's last job).
If I made an extra occurrance of the agency table, wouldn't I want to do that with the job table, too?
how to get all the info that's wanted related to a student's last job
The details of the last job can come directly from the existing occurrence of Jobs - provided you either sort the related job records in chronologically descending order, or place a similarly sorted one-row portal in the body part (see my second post in this thread).
Actually, I got it working better now, before reading your note. I created, in the Student table, some fields modeled on cLastAgencyID: cLastJobWage and cLastJobType, and cLastJobState.
I have a subsum with the context of Student (i.e., based on the Student table). When I sort by LastAgency::agency_name, the subsum display works, and provides a sum where expected (<<sum>>). When I sort by cLastJobType (from Student), it does not include a sum in the subsum part, so that each subhead has an empty parentheses ().
Since it's at least working better than what I had before, is there a problem with continuing on this path - modeled on cLastAgencyID? So far, cLastJobWage and cLastJobType work. cLastJobState, which doesn't work at this time, presumably because it's in a related table (job_address) rather than in the Job table, itself.
The previous suggestion, creating cLastAgencyID, depended on no sorting, so I removed the sorting. If I now sort again, as suggested most recently, would I be abandoning the cLastAgencyID approach?
If I tried the one row portal, would it go in the body of a sub-summary part? What would the break field(s) and layout context (i.e., table) be?
OK, I think I see where the source of the confusion lies now. Let me suggest a simplified/unified way:
1. Sort the related job records in chronologically descending order;
2. Change the definition of cLastAgencyID to =
Now you can place any field from Jobs in the body of your report, and it will show data from the last job AND the cLastAgencyID will also show the ID of the same last job's agency.
This makes more sense to me, and I'm doing it. I have an observation and three issues/questions.
Observation: I seem to still need the LastAgency table occurrence, so I'm still using that, although I did change the definition of the Last Agency calc.
1. I have "Last Job by Agency" on one layout, and "Last Job by Type" on another. The former correctly shows the sum of the number of entries after each break field (i.e., Agency x (3); Agency y (2); Agency z (4), etc. For Last Job by Type, no sum shows, just empty parentheses (). For any table where I want this count, I have a field called one, and another called sum which is a summary field = count of one. I can't figure out why it won't work for "Last Job by Type." I have tried putting sum in there from each of the related tables, but it still shows empty parentheses ().
2. The Last Job by xxx layouts show the right last wage for each student's last job. I added a trailing grand summary (my first!), and added a new summary field there (sAvLastWage). It's in the Job table, and is defined as the Average of hr_wage. I thought it would average the list of last hourly wages that show on the page, but the trailing grand summary shows only the field name and the box where the average would go. The box is empty.
3. I'd like to do a subsummary report that shows last job by state. The address/state info isn't in the job table; it's in a related Job Contact [Job Supervisor] table. This is because each agency must be unique, but some have multiple locations -- and they could potentially be in different states. (If this is too complicated, the users could figure this out without relying on FMP. There can't be that many students working out of state). If it's readily doable, it'd be nice to include it. // Should I add a table occurrence?
Thank you very much! I really appreciate your patience and your help.