ExecuteSQL and Portals

[MSPJ]

Hi - 

I'm a bit confused about how fields with results of SQL calculations interact with a portal.

On a layout, I have a portal into a table which is related to the layout.  I'm trying to use an ExecuteSQL calculation to simplify subcategorization of the data on the primary layout. I have sQL code that works fine when I set the parameters manually (hardcode) but when I put in arguments with fields that correspond to the portal rows I'm not getting the expected results.  

 

I understand that ExecuteSQL ignores table relationships on the graph -- but is that true for the arguments I'm feeding it? Or to put it another way - if I have fields in a portal that are calculated with ExecuteSQL - do they not care about how that portal is connected to the related layout?

 

More generally - is there a way to do what I'm doing on a portal with SQL without using a portal?  I know I can do a join in the code - but I'm unclear how I'd display the data on a layout - getting a list on a form layout the way you can with a portal. 

 

HOpe this makes sense!

 

Thanks,

Michael

[Wim Decorte]

Too abstract.  Can you expand with how exactly you are doing it.

 

If you pass field references as parameters to en eSQL, those field references will be evaluated from the context that the calculation itself is defined in.  Just like any other calc, so the fact that you using eSQL is irrelevant.

 

To display the result of an eSQL on a layout, look into the virtual list technique.

[MSPJ]

Yeah, kind of thought that was going to be a little vague.  

So I have Visit events, and each visit has a member and service attached to the visit (visit, member, service are independent entities).

 

On a layout based on Member, I want to display summary data about all the visits that utilized  a particular service.  (Or on a layout based on service, display aggregate data for each member).

 

I've done this using standard filemaker relationship graph techniques, but it's starting to get overly complicated so I thought eSQL might simplify things. 

 

I created a join on the graph (not via SQL) between member and service so I could gather information about all the combinations of each of those, and put a portal into that table.  Then I have eSQL calculations doing counts or sums of various Visit data using arguments passed to "Where" via global fields (these seem to work fine) and via a foreign key in that Member/Service join - this one doesn't work as expected.  If I put in an actual member ID "Member001" in the argument list, I get the expected result, but if I put in fk_MemberID, I don't get what I expected.  

 

Is there something fundamentally wrong with this approach?

 

I will check out virtual lists - thanks.

[Wim Decorte]

Nothing wrong with the approach, so something is wrong with how you reference that fk_MemberID field.  You are probably using an unrelated TO (relative to the context of the calculation).

 

The calcs context is displayed at the top left of the calculation window.  Make sure that there is a valid relationship from that TO to the TO that you have used to pick up that fk_MemberID from.

[MSPJ]

Yup, that did it - thank you!

[Aliraza142]

1. You need to make the calculation field with the SQL statement unstored.

 

2. List() gives you a horizontal list if you substitute the ¶ against another delimiter, e.g. ", ", or " / " etc.

 

________________

ali