Jump to content
Salesforce and other SMB Solutions are coming soon. ×

ExecSQL making me nuts.


NFBC_Danny
 Share

Recommended Posts

New member here, and fairly new to FMP.  I am Webmaster for our local bicycle club.  We use FMP 18 to keep track of our members' ride mileage for each 'club year'.

Recently we have added some changes, we have divided our members into two groups, ones who are able to ride everyday and those that are Limited to only after work and weekends.

I have made the necessary changes in FMP to accommodate this, but our wonderful Board of Directors has concerns that someone may try to cheat by saying they are Limited when they are actually Everyday, so I have to create a running count of the number of rides for everyone.  I am a retired programmer on the IBM Midrange platform with 30 years of experience, so I am quite familiar with SQL.

My first attempt was to make the RideCount field a calculated field using ExecSQL, all I got was ?, so I tried to move the ExecSQL statement to a script, so I could run it in debug and see if I could make it work there.  Then I discovered that I needed to change the field to a non-calculated field, so I removed the calculation aspect and reverted it to a text field.  When I ran the script I again got the ?.

This is my ExecSQL statement:

   ExecuteSQL("SELECT count(*) from Events_Members  WHERE Events_Members::cyclist ID = cyclists::cyclist ID and Events_Members::ride miles >  1";"";"" )

I tried to attach pdf docs of the file layouts, but was not allowed to do that, please let me know what additional info you might need.

Thank in Advance,

Danny Hayes

North Florida Bicycle Club,

Jacksonville, Florida.

Link to comment
Share on other sites

I don't have a field to get a sum of, or at least no field that would be appropriate for summing to get the ride count.

Link to comment
Share on other sites

You should be able to submit pictures and sample files.

I suspect a trouble with your structure.  I wouldn't divide riders into 2 groups.  I would add an attribute to a rider where you can select which 'type' they are.  A related table of the actual rides is also needed.  Then you can find/sort/count/report on that table rather easily.

Can you post a sample file with data?  Or at least a picture of your relationship graph.

Using SQL can be fine for one instance, but using proper FileMaker structure will allow you to make many modifiable reports rather simply. 

Link to comment
Share on other sites

Steve,

Thanks for you help.  Let me say I inherited this and had nothing to do with the original design.  🙂 

I have created a jpeg of the Relationships, I hope this will be enough for you to determine where I have gone astray.

I will say that now the 'system' does everything correctly with the exception of giving me a ride count for each member.

If you want the whole ball of wax I can send it to you, but I am thinking that you do not need that at this time.

If you need the rest just let me know.

Relationship_NFBC.jpg

Link to comment
Share on other sites

Are you looking to get an all-time count of rides? Or the number of rides within a given period?

 

Link to comment
Share on other sites

It would be easier for me to see the file, with some sample data.  I see a number of  "..... 2" TO's which tells me someone tried a bunch of things that may not be necessary.

Link to comment
Share on other sites

3 hours ago, Josh Ormond said:

Are you looking to get an all-time count of rides? Or the number of rides within a given period?

 

We start new each year, so for the whole 9 yards.

Link to comment
Share on other sites

2 hours ago, Steve Martino said:

It would be easier for me to see the file, with some sample data.  I see a number of  "..... 2" TO's which tells me someone tried a bunch of things that may not be necessary.

Here are both tables in their entirety.

cyclists.fmp12 events_members.fmp12

Link to comment
Share on other sites

Are we on the right track? Here it is all in one file with 2 tables.

 

 

Another note:  Make sure your serial numbers in the Parent Table (cyclist) is auto enter, not modifiable, unique and always validate.  Otherwise you'll have problems relating records.

If cyclist ID is the field you're using to relate records, it has to be a serial number.  If it's an arbitrary assigned number, another ID (hidden from users) should be used as a primary key to relate records.  In your example, you'd have to go back and add that primary key to cyclists, and at the same time, assign that value to a foreign key in the child table, then change the relationship.  This can be done with a 1 time looping script.

It was easy to do in your file and I adjusted the next serial number.  You'll notice in the relationship graph FileMaker now understands that the relationship is one to many:

You now see    ----------< , instead of >------------< which indicates a many to many and was confusing in your original graph.

cyclistsMODsm.fmp12

Link to comment
Share on other sites

Steve,

The Cyclist ID is generated on a different system, we do not create it in FMP.  How does this affect what you are saying?

I am assuming (I know...) that you are indicating that the events_members is the Child Table.  In this table events other than rides are tracked. For example attending meetings, volunteering at events and attending Board Meetings, this is why in the SQL I use ride_miles > 0,  which is why I thought a summary field would be problematic.  Each time our statistician goes to enter a new event he downloads a memberlist from our club website in and Excel that is imported into FMP:

May 28, 2020 16:31:04 2020 Correct Master.fmp12 - Import_Members -1-
Import_Members
 

Freeze Window
Go to Layout [ “All Cyclists” (cyclists) ]
Show All Records
Delete All Records
[ No dialog ]
Import Records [ Source: “file:../../Downloads/AllMembers.csv”; Fields Name Row: 0; Predefined: No; Target: “cyclists”; Method: Update; Add
remaining; Character Set: “DOS OEM”; Field Mapping: Source field 1 match with cyclists::cyclist ID(Do Auto-Enter);
Source field 2 import to cyclists::first;
Source field 3 import to cyclists::last(Do Auto-Enter);
Source field 4 import to cyclists::email address;
Source field 5 import to cyclists::street(Do Auto-Enter);
Source field 7 import to cyclists::city;
Source field 8 import to cyclists::state;
Source field 9 import to cyclists::zip5(Do Auto-Enter);
Source field 11 import to cyclists::day phone;
Source field 15 import to cyclists::middle;
cyclists::zip4(Do Auto-Enter);
Source field 24 import to cyclists::Limited;
Source field 27 import to cyclists::date created(Do Auto-Enter);
Source field 28 import to cyclists::expiration;
Source field 30 import to cyclists::status;
Source field 31 import to cyclists::membership_type;
Source field 32 import to cyclists::prefix;
cyclists::date modified(Do Auto-Enter); ]
[ No dialog ]
Go to Layout [ original layout ]
Show Custom Dialog [ Title: "Member Import"; Message: "The membership data has been imported."; Default Button: “OK”, Commit: “Yes” ]

I hope this gives you a better picture of what I am trying to do.

Link to comment
Share on other sites

I thought we were talking about counting and summaries?  Does my modified file do that?

 

Without seeing the entire file I can only take some guesses.

The only potential problem with your script is that it doesn’t trap for errors, specifically duplicate cyclists ID, on import or any other errors.

Considering how easy it is to deploy part of this on FM Go, it would probably be easier to make a small DB to enter this info into Go on an IPad, or a desktop copy, and email you the file for import.  Then you can take care of new cyclist, missing data, and data input errors like duplicating the cyclist ID.

Link to comment
Share on other sites

Steve,

As I said in the beginning, I am new to FileMaker, so using FM Go seems like a stretch right now.

The club has a Club Express hosted website that feeds the new info into FMP, that is kind of locked.

I have sent you the current everything file.  It does all that is needed except for keeping count of the number of rides for each member.

I am not the author of 99% of the system, I inherited it.

I really appreciate your help and apologize for my lack of understanding. 

2020 Correct Master.fmp12

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use