Getting data from a particular record in a different table

[AlaninYorkshire]

I have been using FMP8 for just a couple of weeks now and love it already. Never got on with Access (far too complex) and previously I was a committed Q&A User (Great Symantec product, long since discontinued, and only a flat form database not relational).

Mine is a typical newbie question but I have searched F1 help and online here without success, in part, because I don't know what the thing I want is called. Here is the problem.

I have a database with two tables, Patients (which is a list of my patients and their details) and PatRecs (which is a list of their sessions with me and data pertinent to each session). When I add a new session record in PatRecs, I want to automatically enter a value in the Session No. field which is 1 more than that patient's last session no. Of course this isn't a simple serial number but a serial number in a series based just on that patient and not all the records in the table. Each patient has a unique identifying no. BTW.

I have looked at Portals and also various formulae like Max and Count but I can't seem to get the result I need. I would really appreciate any suggestions on what, I am sure, must be a simple problem really. Many thanks.

[IdealData]

Use the MAX function, but you need to establish a self-join relationship in the PatRecs table using the PatientID on both sides of the relationship.

The purpose of the relationship is to collate only the records that match the relationship, therefore you can use the MAX function to establish the MAXimum record number in the related data, viz. a patient.

Please be aware that performance will deteriorate as more records are added (becasue FM has to "count" the records) - however don't worry until you're into many hundreds of records for a given patient.

[IdealData]

Incidentally, AlaninYorkshire - I'm also in Yorkshire. FM Developers are thin on the ground here, but the grass is definitely much greener. Ee bah gum!

[AlaninYorkshire]

Thank you IdealData for your prompt reply. I have looked up self-joining relationships. Although I don't yet completely understand what you mean, you have pointed me in the right direction and I think I sort of understand the principle. I will look into it further. I would obviously not have known to look there for the solution, but I did at least recognise that the max function alone was not the answer! I will post back later, letting the forum know how I get on.

Incidentally ... yes I am indeed in wonderful Yorkshire, up on the Pennine hills in fact and the grass is indeed greener, at least now it has started to rain again! I would hardly class myself as an FM developer but I am glad that I gave the program a trial and I am excited about what it seems like I could learn to do with it.

[AlaninYorkshire]

Thank you, I have done it! Having struggled for many hours before posting to this forum, I have just succeeded in 5 minutes!

For others who may need this info here is how; in Define Databse, Relationships I created a self-joining relationship between Patrecs table and a further occurance called Patrecs2 (How? [in V8] Select Patrecs table on graph then click on 3rd icon that looks like two plus signs). Linked patientID fields as being equal (=) in these tables by drawing a line between them. Then in Define Database, Fields, I set the options on the Session No. field to be a Calculated Value with the formula (Max ( PatRecs 2::Session No )) + 1. It works a treat, thanks again IdealData.