7 posts in this topic
ExecuteSQL use with Virtual Lists
Hopefully this is in the right place, apologies if not.
I'm using the technique found in the FM 13 advanced training series to generate virtual lists.
I'm using virtual lists to quickly transfer data to a scratch table used for reporting. I have an ExecuteSQL formula to gather some of the data in question:
SELECT GroupID, SUM(RoundedValue)
WHERE CustID_FK = ? and Code < ? and PeriodNumber = 2 and
(GroupID = 68 or GroupID = 3)
GROUP BY GroupID
"|" ; "|" ;
This generates in most cases a dataset of:
68|-3600|3|-7200| <<< as an example for this customer with 68 and 3 being the required group 'names' or headers.
This data is passed via scripting into a field on the scratch table called ListData which has several fieldsdedicated to particular values, two of which are dedicated to Group68 values and Group3 values. The formula in each is:
Get Value(ScratchTable::ListData;2)<<<For Group 68, -3600
Get Value(ScratchTable::ListData;4)<<<For Group 3. -7200
However, there may be cases where a customer does not have any values associated to either 68 or 3 which obviously reduces the return on the dataset and makes the parse section inaccurate. On my scratch table, I have several fields
If a particular customer has NO value for group 68, the returned data set instead looks like:
and this will break all downstream GetValue(Field;x) calculations.
So, what I really need to have is a reliable way of ALWAYS returning 4 pipe separated results in the situation that a customer has data missing from one or other of the groups, and that's where I'm stuck. If I could get to:
as an example, this would enable me to parse the 0 values to where they should be.
Any help on this one greatly appreciated.
Help with Select Sum
By hair on fire
I'm relatively new to the ExecuteSQL statement but I want to learn more about it in order to create a dashboard of charts without having to worry about the underlying layout's base table. I am a lab manager who oversees 4 funds each with different fiscal years. I'd like to see real time totals for each fund. I'm stuck with how to incorporate the various fiscal years to the total spend for each fund. Do I need a statement for each fund?
I have tables for Orders, Funds and a Dashboard.
I was able to successfully write an SQL statement that allows me to chart how much each lab user is spending each calendar year (via a User table). But this has me stumped!
Let me know what information would be helpful for those out there who might help me!
Many thanks in advance!
SQL novice - where to start?
A simple project wants to start with several people out in the field doing data entry; in the interest of uniformity, I set up a simple one-table web-based SQL table with a one-way PHP interface. That's working fine (and is about the limit of my facility with PHP). My intent was to set up an automated recurring import into a FMP database, which I thought would consist of entering credentials and an IP address into the ExecuteSQL script step. I find the learning curve a little more steep.
In this instance, it's really not hard for me to export the contents to my desktop once a day and then hoover them up into FMP. But can you recommend a one-stop tutorial for this kind of straightforward behavior?
SQL Server 2008 ESS - Only displays system tables
This is my first post in the forum - one of no doubt many to come!
I'm working on my first solution and as part of that I'm pulling in data from a SQL Server 2008 database which will be augmented by file maker tables.
I have been able to locate the DSN and setup the External Data Source, however when I try to add the table in the relationships screen I can only see the system tables in the SQL database - not the views and tables that I have setup.
Does anyone have an idea of a possible solution for this?
Running FileMaker Pro Advanced 14 on Windows 7.
ExecuteSQL - Field cannot be found
By Pat Fellows
I am extremely new to working with SQL, so i am currently fumbling my way through achieving my required outcomes.
To explain further explain my issue, i created a table/layout on one of my databases that i could use to practice and visualize the outcomes of ExecuteSQL.
I type into my "query" field and that drives a separate ExecuteSQL calculation.
I have been able to achieve the required outcome in this field, however when i transfer the query directly into a ExecuteSQL calculation i get errors. These relate to the field in financials called "Cost Code"
If i attempt the same calculation without it, i can run it without an issue. However what confuses me is it will work when input through a query field.
Where "Cost Code" = 'FA' and EID = '982 000147908607'
Please find attached images highlighting and showing everything relevant.