Conundrum - getting last (or first) record of a portal into a fixed field.

[Al Spiegleman]

Complicated issue, but I'll try to be concise.

Have a DB (FM9) with many tables/relationships. Customer Table has  
customer info, and Appointments table has all appointments for all  
customers.

Have a portal in Customers that shows all appointments for that  
customer, from day 1, sorted in decending order by date, so that the  
last appt is always at the top.

I need to get the top record into a fixed field that I can export to a  
different database (for use with FMTouch). I use lookups for most of  
the fields that I import from the Customer Table, but can't get my  
head around how to get just the last appointment record for that  
customer imported into the DB that I am using for FMTouch.

The reason that I need to do this is that the FMTouch DB will just be  
used in the field for reference, and all "syncing" will take place  
from the new FMTouch DB (the master is way too big to use with  
FMTouch) so I have to pare it down to include just essential  
information. A script finds all of the active customers and  
essentially recreates (deletes all records - then imports into the  
FMTouch DB) when you want the latest info in the FMTouch DB so that  
you can sync it with the iPhone.

Been going round and round on this, and still can't find an elegant  
solution.
Any help would be appreciated.

Al

----------------------------------------
Al Spiegleman
AM Consulting
Voice: 303-674-1074 - Fax: [phone]


_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Darren Terry-3]

On Oct 7, 2008, at 3:50 PM, Al Spiegleman wrote:

> I need to get the top record into a fixed field that I can export  
> to a different database (for use with FMTouch). I use lookups for  
> most of the fields that I import from the Customer Table, but can't  
> get my head around how to get just the last appointment record for  
> that customer imported into the DB that I am using for FMTouch.

Hi Al:

When you refer to a related field in a calculation or lookup, FM  
always grabs the first related record.  If the relationship is  
sorted, then it will be the first related record in sorted order.

Therefore, if you define a calc equal to the field you need from the  
appointments relationship, it will be that field from the most recent  
appointment.

HTH

regards,
Darren

******************************
  Darren Terry                               darren_terry@...
  Pacific Data Management, Inc.              http://www.pdm-inc.com/
  111 W. St. John St.                      Phone: [phone] x303
  Suite 404                                  Fax:   [phone]
  San Jose, CA 95113
***** FileMaker 9 Certified Developer *****



_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

[Al Spiegleman]

Darren.

I feel like a complete idiot. I have been looking at the relationship  
setup for I have no idea how many years, and I have never "seen" the  
relationship sort option!

I've been programming in Filemaker since the beginning. I remember  
when you used to work there. I've done projects for myself, for  
customers big and small, and have never even looked at the options at  
the bottom of the setup. I really feel like a DUFUS!

Thanks for teaching an old dog to see what has been staring at him for  
years.

Al
----------------------------------------
Al Spiegleman
AM Consulting
Voice: 303-674-1074 - Fax: [phone]


On Oct 7, 2008, at 5:47 PM, Darren Terry wrote:

>
> On Oct 7, 2008, at 3:50 PM, Al Spiegleman wrote:
>
>> I need to get the top record into a fixed field that I can export  
>> to a different database (for use with FMTouch). I use lookups for  
>> most of the fields that I import from the Customer Table, but can't  
>> get my head around how to get just the last appointment record for  
>> that customer imported into the DB that I am using for FMTouch.
>
> Hi Al:
>
> When you refer to a related field in a calculation or lookup, FM  
> always grabs the first related record.  If the relationship is  
> sorted, then it will be the first related record in sorted order.
>
> Therefore, if you define a calc equal to the field you need from the  
> appointments relationship, it will be that field from the most  
> recent appointment.
>
> HTH
>
> regards,
> Darren
>
> ******************************
> Darren Terry                               darren_terry@...
> Pacific Data Management, Inc.              http://www.pdm-inc.com/
> 111 W. St. John St.                      Phone: [phone] x303
> Suite 404                                  Fax:   [phone]
> San Jose, CA 95113
> ***** FileMaker 9 Certified Developer *****
>
>
>

_______________________________________________
FMPexperts mailing list
FMPexperts@...
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au