Max Fuction with Sql Fields

[Lelantos]

Hi all

I am having a problem having the max function work with a sql table in filemaker.

I have the Sql table in filemaker and i created an extra field in filemaker named it Constant and has always Value 1, i created a new table with 2 fields , Constant , MaxID. I create a relationship to the two tables through constant and created the calculation Max ( ID ) in the MaxID field, but this never return me the maximum id number from the sql field.

Sql Table___________________Filemaker Table

Constant(FM Field)_---------_Constant

ID__________________________MaxID (Max Function)

If anyone got any solution

thank you

Edited January 9, 2009 by Guest

[bcooney]

I don't have experience with SQL, but if this were two FM tables, you just relate them thru Constant=Constant or using a cartesian (x) join. Now table1 sees all the recs in table2.

The calc in table1 is: max(table1::table2:numfield).

[Lelantos]

Thank you for the reply

I have already done a relation through constant but the problem is the ID is a serial number that is created in both sql and filemaker, when a new ID is created on the sql the Max Value in filemaker doesn't refresh.

I have also noticed that when there are many sql records filemaker does a lot of time to do the refresh.

I am thinking maybe creating the Max value on the sql side i hope maybe that way it works.

[bcooney]

The goal is to get the maximum ID in the SQL table? Why? Are you aware that if a relationship is sorted by ID desc, the first related record would contain the maxID, given an auto-enter serial. Or, use the Last() function on a non-sorted relationship.

What are you doing with the MaxID once you have it?