7 posts in this topic
ExecuteSQL() sums and group by ERROR...
Trying to figure something for a fellow lister. I am tring to run a query in FileMaker using ExecuteSQL() that works perfectly when I run a similar query in a normal SQL editor:
SUM(i.Quantity) AS sQTY
FROM invoice_invoicelines_Product p
JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3)
WHERE p.pk_ProductID IN (1,2,3)
GROUP BY p.pk_ProductID
It produces a "?" (which is very informative).
I guess need to know if this is too much for FileMaker's SQL engine or if FileMaker is going to force me to use something that is not tradition SQL protocol/syntax.
Any feedback would be appreciated.
Select item from dropdown
I am new to FMP so this maybe a pretty elementary question for some of you. What I want to do is have a dropdown list that is defined by certain options that I have pre-defined. In my example these would be Serial key, Credentials, keygen, or patch. If serial key is selected i want a text entry field to appear below or next to this dropdown list. if credentials is selected, I want to have a username and password text enter field to appear. If keygen or patch is selected i want to have a hyperlink entry field show up.
What I thought was a simple margin calculation in ExecuteSQL.......
I HOPE this is in the right place...
I'm still trying to wrap my head around the FM SQL syntax and to a certain extent have had success. I'm preparing some chart information and need to show a margin percentage over a period of time. This would be calculated as (Income-Cost)/Income/*100. The ExecuteSQL statements at the end of the code should give me 5 percentages to compare over a period of time.
My income and cost records are defined clearly in the data table each one having a text tag of either INC or COST, so DataTable::Amount records are either INC or COST. In turn, these are split into two other columns so I can summarise Income and Cost elsewhere in the system. I want to use these columns now to generate a percentage on a chart.
I need to somehow get that into a SQL statement - the statement below doesn't work (I believe because I'm querying the derived fields of INC and COST which seems ridiculous that you cannot do this, so probably it must be me making an error).
If I were to do Select SUM(Amount), this works fine as Amount is simply a number field?
Thanks in advance for any help
CUST = Customer_Home::Cust_PK;
SQL = "Select (SUM(INC)-SUM(COST))/Sum(INC) from DataTable where
GroupID = ? and
ExecuteSQL(SQL;"";"";CUST;1;5) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;4) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;3) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;2) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;1) & "¶"
Free Video>>> FMPUG-Without Borders - July 15 - SQL Intro for Beginners
By Richard Carlton
Please Excuse the Minor Audio feedback issue at the beginning. We were unkinking out new studio.
FMPug Without Borders | Execute SQL with Todd Dignan
Download the FileMaker Pro 14 & FileMaker 14 for Mobile Devices Training Videos athttp://www.learningfilemaker.com
Please Visit Our Channel: https://www.youtube.com/user/FileMakerVideos Please Subscribe While There.
Richard Carlton Consulting, Inc. provides customized database development services for business, government, and non-profit organizations. Our core competency is FileMaker Pro, where we hold FileMaker's highest certifications. With a team of 27 staff, we are capable of deploying both small and large solutions for a wide variety of customers. We service all modern versions of FileMaker Pro, FileMaker Server, and FileMaker Go (for the iPad and iPhone). We also deploy FileMaker databases to the web, and offer PHP/Web Development, iOS Programming, FileMaker Server Support, and Hosting Services.
Please feel free to contact us at firstname.lastname@example.org
Looking for FM Starting Point free software download: http://www.fmstartingpoint.com
For More Free FileMaker Videos Check out Http://www.filemakerfree.com
Please Comment, Like & Share All of Our Videos.
Feel Free to Embed any of Our Videos on Your Blog or Website.
Watch a FileMaker Training Review Video Here:
Follow Us on Your Favorite Social Media