pc ExecuteSQL - does it not follow portal filtering processes?

[Stu412]

Hi there

 

I'm quite new to FM and come from a SQL background, and am struggling here a bit...

 

I'm looking to run an ExcuteSQL command on a table which contains values for many customers.  Each set of values per customer on my source table has their customer ID and in turn this is related to the customer table.

 

I need to create a portal on the customer layout, based on the value table, using ExecuteSQL.  

 

I need to use ExecuteSQL to enable dynamic variables based on user input which will filter the results in the portal, producing average, maximum, minimum and so on.

 

ExecuteSQL ("

 

Select max(FTE) from Values where Type =?";"";"";GlobalFields::GF_Types

 

)

 

The query above reviews ALL customer data on the value table and instead of the maximum value for the customer who's record is currently open, I get the maximum value for the table overall, which is no good to me.

 

How do I filter this to behave like the portals usually do, displaying information relating only to the parent record being viewed?

 

Any help gratefully received.

 

Thanks

 

[Wim Decorte]

You need a second WHERE clause on the foreign key in values that represents the parent

[Kris M]

ExecuteSQL ("

 

Select max(FTE) from Values where CustomerID=? and EmployeeType =?";"";"";practiceNames::customereid ; GlobalFields::GF_Types )

 

)

 

 

You have to pass both variables into the eSQL function.. It gives you a syntax error "?" because it does not understand CustomerID=PracticeNames::customerID.

 

You should also check to see if "values" is an SQL reserved word.

 

PS ExecuteSQL operates outside the normal boundaries of FM and is context independent. Therefore it does not honor interface (portals, filtered/sorted or not) or relationship graph devices (filtered/sorted relationships )

[Stu412]

Kris, 

 

Thanks for the help on this, works like a dream

[Kris M]

Wim gets some credit too!