Jump to content

ExecuteSQL and Portals


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

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 3 months later...

This topic is 2448 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

  • Similar Content

    • By Clayton King
      First, I appreciate any help I can get as this project has been evolving over two years and, as these kinds of projects to, has become more involved and complicated over that time. I've posted related questions, but exploring other parts of my project. As I'm not a programmer, but a sort of user nerd, my inquisitive mind wants to learn, but sometimes simple concepts elude me. Feedback, both on the specific goal at hand as well as the project overall is always appreciated.
      Performers is one piece of a much larger project, but I'm trying to enhance the project and working on a piece at a time. 
      BACKGROUND
      I'm sharing the entire project layout to provide some insight into how the parts fit together. The application is a booking management tool for performers, consisting is several tables as shown:

      Again my focus right now is the Performers table and how to use it in relation to the Gigs (and Gigs~Songs_Join) tables.
      There is a Gigs layout which is the hub for performances by one or more Performers. On that layout is summary information about a gig and a portal which shows which songs were performed and in what order (Set & Slot fields). The portal also includes a Performer Name. The assumption at this point is that up to 4 performers will do a given gig. This might change in the future, and could impact the below goals.
      Gigs~Songs_JOIN is a connecting table for Gigs, Performers, Songbook, Venues, Musicians, etc. Performers is a table of singers and includes fields for a serialized SingerID, name, area of speciality (Tenor, Baritone, etc.), etc., and additional fields for combinations of the same performers including: 
      Multi: checkbox-indicates this record is more than one performer
      PerformerName: calculated based on Multi: if NOT Multi, show performers Name, if Multi, show MultiName (below)
      Type: value list-is this a duet, trio, quarter, all, etc.
      MultiName: the description of this record (i.e., Duet-Clayton/Vicky)
      Other summary fields include:
      Total Gigs: count of gigs in which this person has performed
      Total Songs: count of songs this person has performed
      GigID: match field to Gigs~Songs_Join

      GOAL
      I have two goals, one related to the other. First, if the Performer record is checked Multi, I want to "attach" or "associate" individual singer(s) to the record (i.e., show what people are "included" in the MultiName). For example, MultiName=Duet-Clayton/Vicky, I want to show that Clayton and Vicky (each in separate records) are part of Duet-Clayton/Vicky.
      Second, ultimately on the Gigs layout, I want to show which Performer sang which song (currently works in the portal), but more importantly, show variable summary information (goal layout below) which changes for each Gig as to each Performer, how many songs they sang and the total amount of time they sang. The catch is, in the summary I don't want to show the Multi records, but instead increment individual singers. So, in the summary info with related portal records in the example below, rather than showing Clayton sang one song, Vicky sang one song and Duet:Clayton/Vicky sang one song, I want to show Clayton sang 2 songs and Vicky sang 2 songs. In summary info, Duet:Clayton/Vicky wouldn't appear. In other words, the summary would show how much time each person was singing.

       In the summary block of the Gig's layout, each of the above sets is a one-row portal filtered by set. I want to have the SINGER1,2,3,4 replaced with the actual Performer's name, and each band/portal show summary info for the appropriate singer. 
      Here is my relationship graph:

       
    • By Answers
      We have a Filemaker Go on iPads and there is a layout with portal rows, when scrolling through it hesitates and you must wait to scroll down. We have offline files on the iPad, although it happens when we connect to the Filemaker server as well.
      Any suggestions?
      Thank you,
      Caroline
    • By Will_Logic
      Hi, I have been using Base Elements command BE_FileMakerSQL in FileMaker script to SQL select data, and return with '<c>' as field(column) delimiter, and '<r>' as the row delimiter. The below line worked before, I dumped a whole FM database with these delimiters showing in text files. But somehow, now if I run below line, it seems to have changed to ?only allow? 1 character for the delimiter, which of course doesn't make sense anyway with varied characters in the data:
      BE_FileMakerSQL ( $sql_select;  "<c>" ; "<r>" ; $fm_file ) The string this returns now looks like for example: 352265<gwgwh<474848<<wegwgw<65755<gwgw ....
      i.e. it has only taken the first character of the delimiter, I changed delimiter string to test with eg "A≠" instead of "<c>", same problem, it then only inserts "A" as delimiter. Just wonder if anyone can guess what might have happened here?
      Thanks kindly any suggestions!
    • By Will_Logic
      Hi, I am using the Base Elements plugin Execute SQL command to select SQL on a table in another FileMaker file. For a file with many fields and quite a lot data in some fields, this times as about 1 minute just for SQL SELECT command for 1,000 rows. I haven't used MBS (monkeybread software) plugin yet, but I wonder if anyone knows if it is significantly faster?
      I.e. BE_FileMakerSQL vs. MBS(FM.SQL.Execute..) - is one significantly faster?
      Here's the code to select 1,000 rows for reference, $timer_seconds comes as about 60 seconds here. If I export same table internally in FileMaker using the menu export to Excel, it takes 29 minutes for all 41,000 rows, so timing seems similar for the Base Elements SQL. (NB there is some flag going on here in the FileMaker database so internal export gets 41,000 rows but script SQL SELECT COUNT(*) gets 200,000 rows for same table, but I don't think that is relevant to my question).
       
      Set Variable [$time1; Value: Get(CurrentTimeUTCMilliseconds)] Set Variable [$part_ca; Value: BE_FileMakerSQL ( "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; "&≠≠&" ; "EOR≠≠EOR" ; "DataContacts11" )] Set Variable [$timer_seconds; Value: (Get(CurrentTimeUTCMilliseconds) - $time1) / 1000] MBS command I think would be something like:
      MBS( "FM.SQL.Execute"; "DataContacts11"; "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; ...delimiters here I'm assuming.. ) We only have base FileMaker here on machine I am using, which can run the Base Elements plugin, but to run MBS plugin, needs FileMaker Pro Advanced to install it I think, am getting several databases in roughly same FM format where script workspace disabled, hence need to use SQL select run from script in my own external FileMaker file. Might consider getting FMP Pro Advanced if MBS is faster.
      Tx any suggestions!
    • By Matt.C
      I'm hoping someone might be able to help, or at least point me in the right direction.
      I'm creating a portal that displays a student photo, but would like the ability to navigate forward/backward through the photographs in the portal without the vertical scroll bar. My < and > buttons currently point to a Go to Portal Row - Previous/Next script step. It works great when "Allow vertical scrolling" is turned on, but does not work with it turned off. 
      It does work with Allow vertical scrolling turned on and show scroll bar set to "When scrolling", however, the scroll bar appears on mouse-over. 
      Any suggestions on how I might be able to achieve this?
      Many thanks!
      Matt

×
×
  • Create New...

Important Information

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