"Group by", "Distinct" on fields?

[lzeng]

Hi:

I have no idea on expressing SQL's "group by" and "distinct" in CDML.

I have only one database named "Patient.fp5", in this file, there is one field named "Doctor Name".

Now I want to use a link to get a list of distinct "Doctor Name". Then in this result page, I wish to use a link on each doctor to access a group of patients which belongs to this doctor.

So totally there are two links in this task, does anybody have any ideas to write these two links?

Thanks in advance!

Catty

[Vaughan]

The best bet might be to determine what "group by" and "distinct" might be in FMP.

I know nothing of SQL, but I'd guess that "group by" might be the equivilent of FMP's Summary field.

[Garry Claridge]

Depending on what you want to do with the list, you can either use a "summary" as Vaughan suggested, or you can use a value-list.

Create a value-list using the field "Doctor Name".

Just depends on how you want to use it.

All the best.

Garry

[Garry Claridge]

Catty,

Another way to deal with this is by creating another table (FM database) called 'doctors.fp5'. In that table you have a single entry for each doctor; initially one field called 'doctor_name'.

Now create a 'Relationship' to the 'Patient.fp5' table/database based on the 'doctor_name' to 'Doctor Name' fields.

Next, on a layout in the 'doctors.fp5' database, create a 'portal' based on the new relationship. Place a couple of fields from the related file in the portal.

You will find that you can now view the patients who relate to that doctor. From this relationship you will be able to create claculated fields in the 'doctors.fp5' which Count(), Sum() and provide other group type information.

For a cdml search you would query the 'doctors.fp5' table and use the [FMP-Portal] tags for related record viewing.

Hope this helps.

Garry

[lzeng]

Hi Garry and Vaughan:

Thanks very much for guiding me to the right direction. I'll follow Garry's last suggestion.

Catty