Filemaker Pro 13 report of max revisions

Hi it_medcomp,

In order to do this you'll need two tables: Employees and Trainings, the first being your Employee list, one record per employee and having a unique number to identify them (it can be their Employee number but it needs to be unique and unchanging. The Trainings table has one record for each training/revision an employee has taken. The Trainings table records need only have information pertaining to the Training itself such as date, revision, etc., PLUS the unique identifier for the employee who took the training. A relationship is then created between the two tables using the unique Employee Identifier in both tables, e.g. Employee::EmployeeID <-> Training::EmployeeID

Now when you have a group of related records - the Employee record being the "parent" record and the Training records being the "children", Filemaker normally will display those records in creation order, meaning oldest first. If in Employees you are displaying fields from the related trainings, Filemaker will display the first (oldest) training rather than the newest which you want to see.

In order to see the most recent you need to go into the Relationship graph and make a small change in the relationship Setup. On the side of the Setup dialog that has the Trainings, click the "Sort records" checkbox and then specify a field to sort on. This could be the auto-entered serial number for the Training record (always have a serial number or UUID - Unique User ID - to uniquely identify each record in each table) or a time stamp as I've done below. The key is to set the order to "Descending" for the sort field.

Now, when you look at one related Training record from the Employee record it will show the most recent first rather than the oldest. Similarly a portal showing all training will Sort by newest first rather than oldest unless you override the relationship sort at the Portal Level.

Thanks for the reply. We already have the data in multiple tables- I have to report based on this (It is tens, if not a hundred thousand records). We already have a report that creates a list of employees... but it lists out each revision of each training. I just want it to show me the line item showing the employee's information fields, then the training identifier, then the highest value for the revision number of that training... then I will apply conditional formatting to highlight any line where the trained-to revision is less than the current training revision number. So what I need is a max rather than a sort. To keep it simple, here is the idea behind the report:
Employee Name
Employee Number
Employee Training Rev Number
Training Number
Training Rev
Showing only the highest training rev
Set conditional Formatting where employee training rev number < Training rev
sort or group by employee name or number
So it generates a list of trainings, and highlights the employees who are behind in their training.

Any ideas?
THanks again- I do appreciate the help!

If you create a new Table Occurrence and relationship between Employees and training with the Descending Sort, you can them simply place the Rev Number field from the Trainings table (using the new relationship) on the Report layout - which presumably is based on the Employee table - and it will display the most recent training Rev.

Alternately, you can create a field in Employees as follows:

MaxRev = Max( Trainings::RevNumber )

This will display the highest value RevNumber. You can then use conditional formatting to compare that to the require level, e.g.

Apply Conditional formatting if:  Employee::MaxRev < EmployeeGroup::CurrentTrainingRev

Will

Thanks again... we are getting much closer. Now what I have is a list that shows the highest rev number.... but still shows all the trianings. For example, if I have a training at rev 41, I get 41 lines showing that the max is 41. How do I get that to show just the line item for the highest training number? So I end up with 1 line instead of 41 lines for that training rev? BTW, the conditional formatting is working as well... just have to remove the duplicate trainings so I only see the highest rev for each.

Thanks again for all your help!

It sounds like you are using a portal to display the 41 lines. Just remove the portal and leave the related fields from the training table. Only the first - and most recent - record will show. Please include a screen shot if I'm not understanding you correctly.

Thanks! Take a look- What do you think?

I'm not sure what I'm looking at. Is this report based the Employees table? I see the same name listed many times so it looks like it's not. You're report needs to be based the Employee table so that each employee is listed only one time. You can then use either the MaxRev calculation or the related Rev field from the Trainings table to display their highest training to date.

If this is not clear, you can make a clone of the file, enter some fake data and send it to me. Rather than post it here, you can use the Message me link on my EE profile to send it to me privately. If you're not comfortable with that, please post or send screen shots of the above screen in Layout mode, of the Layout Setup dialog and of the Relationship graph.

Thanks- I'll have to see which table the layout is based on... it is a 4gb file with 500 layouts and a custom navigation interface- inherited of course. I can't really upload it but will reply to this once I see how the structure of the layout and table is set up.

Back again! I went in and created a fresh report so I would have some real knowledge of how it is set up. I went to an employee database, and created a relationship with the training database, keyed on the employee ID#. The report now lists one document per employee. I think the best way to structure this is to list the most recent training revision using the employee name as a "group by" category.... So it comes out like this:


So we create a list of documents, grouped by the employee, and then employ conditional formatting to highlight the deficiencies.... I have the formatting methods figured out, but not the database yet. perhaps I have it script a find and somehow constrain to the max training revision.... It's just that I am not familiar enough with FMP to set this up (yet).

Thanks again for the help!

I'm not really sure what I'm looking at here...again, screen shots of the layouts in Layout mode showing the field names as well as the Relationship table would be helpful. I'm not sure why there are multiple listings under one name. Using the Group By only makes sense if the Report layout is based on the Trainings table rather than Employee. If it's based on Employee, you will only see one line (record) per Employee. You CAN achieve a similar effect using Sub-Summary parts and Summary fields on a layout based on the Trainings table - and that does have some advantages - but it depends on the data structure and what you are trying to achieve.

From your initial question I assumed that each employee had taken many trainings but that the trainings were all the same - meaning that there were not separate categories of training - and that MaxRev would simply be the highest level Revision they had completed. From your screen shot above however, it seem like each employee has multiple trainings that they must attend (EN-300.1, EN-305.1, etc.) and that you need to know the highest revision for each of the training categories.

If each employee only has 1 type (category, kind, etc) of training that they are taking and you just need to know the highest Rev of all the trainings that they've taken, the report layout can be based the Employee table and you can use MaxRev or the Rev field using the Sorted relationship (as discussed above) to display the highest value and go from there.

If each employee has multiple training categories and you need to know the highest Rev in EACH category of training, then that is a different report. To do that you would have to create a more complex report based on the Trainings table using a Sub-Summary "Part" and probably a Self-Join relationship between the Trainings to determine MaxRev for each category of training for a given employee. Please clarify which of the above is the situation here.

OK, I have some real information that might be helpful....
Fields are as follows:
Last name, first name, Employee Number, department all come from the Employee table.

This is linked to the Training Table by Employee number. The Training table tells us which training document numbers and revisions go with each employee. This table is where the report is based. MaxDocRev is a calculation field that goes to the Document table and does Max(Document Revision). Both the calculation and the Document Revision are the same data type (Number). The same calculation is done on the training table to get the max training revision. I went in and created a portal so I could filter the records, using the following criteria:
(Data_Training Point::CURRENT REV) = Max(Data_Training Point::CURRENT REV) and (Data_Training Point::REV  = Data_Training Point::MaxTrainingRev)

and a sort set up as follows: Document number, then employee number then last name then first name


The results look like all of my criteria are being ignored by FMP:


So the portal is not sorting, and is appears to not be filtering.
I'd send the relationships along, but it is very cryptic and complex- as you are aware, this data can get incredible 3-dimensional, and there is no way it would make sense for me to attach screenshots of each relationship.... I thought using the portal approach would simplify things, but I'm not so sure it solves the problem. Any ideas?

I should clarify- The point of the filter is to only disply the document number whose revision is the maximum revision number- that is the crux of the problem- I just want to show a list that shows each training document once- essentially, the employee's training status for the highest (most current) document revision number... then the conditional formatting takes care of showing which ones are deficient.

Thanks for the details and I can see some potential issue as to why it's not doing what you want it to but before we go any further, there is one important question that has not been answered. I've tried to ask this a couple of ways but let me try again:


Until this question is answered, I can't guide you on the best way to proceed.

Also, if the relationships are "cryptic and complex", it would be wise to spend some time organizing them and making sure that they are named in a logical and consistent fashion. A disorganized Relationship Graph can waste a lot of time and money and result in needless duplication of effort and mistakes.

If you'd like me to take a look at it and also most likely address the issue above. Contact me directly from my profile.