Jump to content

ExecuteSQL - does it not follow portal filtering processes?


This topic is 2822 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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
 
Link to comment
Share on other sites

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 )
Link to comment
Share on other sites

This topic is 2822 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.