Return maximum value recorded (across all records) in a field

[Rramjet]

I am trying to define a field that will display the maximum (highest) value recorded in another field - accounting across all records (ie; conceptually it will need to be be a "global" field BUT it must also be "unstored"...)

To explain:

I have an Activity_ID field (1 to n).

Each time a new participant is added, they are assigned an Activity_ID.

This could be an existing ID, OR it could be a new activity - in which case the ID assigned will be the next sequential number above the highest existing.

Unfortunately the participants can also be in the database already, so we first must find a participant (by name and ID, I use a "find" script with a custom dialogue - attached to an "Add Record" button), then assign an Activity_ID.

Of course the Activity_ID field will be empty when that participant is found and their record displayed.

Then the dataentry person will have to "manually" find the highest Activity_ID (losing the participant in the process), record that ID, then return to the participant record and then enter the next highest ID number.

As you can see, the process is potentiall error prone and time consuming.

So I need a global field that displays the highest Activity_ID existing in the database - that also updates when a new ID is added and becomes the new highest value.

(of course the "list" function lists only the ID in the current record, not across all records - Max function similarly operates on the current record).

Any suggestions would be appreciated.

PS: My database structure has the Participant name and ID number fields in a header making them universal while in the body I have a number of tabs indicating attendance (and capturing the relevant information) at a number of different clinics - and it is under just one of these tabs (which itself records a number of different "Activities") that a separate "Activity ID" must be assigned.

[Rramjet]

Okay... partial way toward solution...

With a self-join relationship I can create a field and attach a value list that contains all the values from the Activity_ID field.

NOW all I gotta do is have the last (highest) value in that list auto-enter into that second field. It would also be good if I could constrain the value list to display only the highest (ie; the last) value.

suggestions still welcome... :laugh:

[comment]

Can you explain the purpose of this numbering?

[Rramjet]

Hi Comment -

You know... that's a very good question...

Originally the database was developed as a standalone reporting tool and the Activity_ID was the database record serial number - used to link various tables etc. I was tasked with bringing it into the main database I am developing and I just carried it over without thinking very closely about what it might now represent.

Now I have thought about it (prompted by you being very much on the ball!)... it is in fact redundant... and I should have realised this when it was a field I could not place (find a natural home for) anywhere on either subtab (I actually used it to link the portal in - but now of course I realise I can use the existing serial number for that purpose..!).

Nevertheless... my question IS an interesting one... albeit one that is now without a direct application... hmmm (scratches head).

Thanks Comment!

[comment]

I am still not sure what the question is. In general, you can number records ad hoc (i.e. depending on the found/related set and the current sort order) by typing @@ on the layout or using Get (RecordNumber).

If you want individual numbering for each group, use a summary field defined as running count of [any field that cannot be empty], with the restart option checked.

A third way is to define a self-join along the lines of:

Table::Category = Table 2::Category

AND

Table::SerialID ≥ Table 2::SerialID

and count the related records in Table 2. This one is a bit more "permanent", since it ignores the current found set and sort order (though it still self-adjusts when records are deleted or reassigned to another category).

[Rramjet]

The question was basically this:

I have an ID field that identifies groups of participants but that field also continually increments to show the order of the groups. So - there might be three participants in the first group, 2 in the second, 7 in the third, 1 in the fourth, etc. The ID number then would be:

1,1,1,2,2,3,3,3,3,3,3,3,4, &c.

(Remember, this is one number per record in the ID field)

Now, I want to run the next session and the next ID number would be 5 (for however many participants).

Unfortunately I find that the (a) participant already exists in the database (as having attended a completely different activity). I find that participant - but of course the ID number is empty and needs filling in - and I can't remember... "Was it 5 or was it 6?" (Shades of Clint : So I have to search back to find the last, highest ID entry and fill in the next incremental value for my current participant... BUT in doing so I have lost my participant and again must find them... messy...

So I wanted to display that "last highest" ID in a global field so it was just there for all participants so no searching was necessary.

The logic is - Display the maximum value of ID in an indicator field.

Now a self join can get a dropdown displaying ALL values of ID - attached to the indicator field and therefore the data-entry person only has to use the dropdown and look at the last entry on the list (highest or "maximum ID") and go from there - a partial solution, but it would be neat to 1. restrict the dropdown to display ONLY the last (or highest) value and then 2. to have that value also autoenter into the indicator field. Part 2. is of course MUCH more logically problematic given the nature of "global" v. "unstored" calculation fields.

Anyway, I've decided to drop all this because it is unecessary for database functionality - and when it comes to reporting perhaps I'll use (count and sum) the date field by session by activity (much much messier because it crosses tables so requires calculation fields that go back and forth, reading one to the other)...anyway, haven't thought THAT one through yet so...

Thanks for the interest,

Cheers

Rramjet.

[comment]

OK, I see: you want to number the groups of records. See here how to do this in a report:

http://fmforums.com/forum/showtopic.php?tid/196904/post/297916/#297916

I don't think a meaningful result can be obtained by trying to do this with relationships. What you describe is essentially a join table between Participants and Activities - and activities as such should be numbered in the Activities table.

[Rramjet]

Hey Wow! That's neat. I've been struggling with workarounds for that in other parts of the database (involving ... oh... who cares, I don't need to DO that anymore (contented sigh of relief) so THANK YOU Comment. This is going to be very useful for me!