Jump to content

Search the Community

Showing results for tags 'sql'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type

Community Forums

  • The New Claris Platform
    • FileMaker Pro 19
    • FileMaker Server 19
    • FileMaker Server 19 (Linux)
    • Claris Connect
    • Core ML
    • NFC and iBeacons
    • Add-on Modules
    • iOS Shortcuts and FMGo
    • Save Schema as XML and Upgrade Tool
    • Java Script and the Web Viewer
  • Community Resources
    • Community Articles, Tips, & Techniques
    • FileMaker Marketplace Discussions
  • FileMaker Platform
    • FileMaker Interface Features
    • FileMaker Schema & Logical Functions
    • FileMaker Go for iPad and iPhone
    • FileMaker and the Internet
    • FileMaker Pro 18 Advanced
  • FileMaker Server Administration
    • Previous Version Server Discussions
    • FileMaker Cloud
    • FileMaker Custom SSL Certificates
    • oAuth and External Server Authentication
    • Zabbix Server Monitoring
  • Brain Food
  • FMForums Affiliates & Sponsors
  • FileMaker Classifieds
  • FM Forums Operations
  • FileMaker Friday Night Chat's Topics


There are no results to display.

There are no results to display.


  • White Papers
  • Infographics
  • Samples
  • Add-ons
  • FMGo
  • Solutions
  • Tutorials
  • Plug-Ins

Product Groups

  • Workplace Innovation Platform
  • Site Advertising
  • Development & Hosting

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start







Website URL




OS Version

Found 68 results

  1. 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!
  2. 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!
  3. Well, I'm new to SQL and have used it in it's basic select mode. Now I have been asked too create a dashboard using data from different tables to display. Which on the most part is OK and working great. I have had a request for a top customer list for the month and previous month (say best 5 ranked from 1-5.) and not together so 2 seperate views I am able to pull customers and SUM totals (seperately)for the month using SELECT DISTINCT I am able to get a list of the customers for the month. How would you then get a total or SUM of the invoice amounts for the given month with the customer name and their total ? also is there a similar function to FRACTION in filemaker? Listed is my SQL Statement that just returns a ? Which I understand to mean a syntax error however, being a SQL "Virgin" any halp would be appreciated. I know I am able to do this natively through filemaker however, as I said this is for a dashboard that uses data from a number of tables. Here is my calc that is obviously wrong. Currently working with Filemaker 15 Advanced. Let ( [ ReturnSub = "\n" ; SQLResult = ExecuteSQL ( "SELECT DISTINCT (a.\"Customer Name\"), SUM(a.\"InvoiceTotal\") FROM \"Invoices \" a WHERE a.\"Invoice Sub Total\" > ? AND a.\"InvoiceMonth\" = ? AND a.\"InvoiceYear\" = ? AND a.\"Job Status\" <> ?" GROUP ON a.\"Customer Name\" ASC; " " ; "|*|" ; "0" ; Month(Get(CurrentDate))-1 ; Year(Get(CurrentDate)); "Giveaway" ) ] ; Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) )
  4. FOR IMMEDIATE RELEASE 24U Software releases 24U Toolbox Plug-In 1.1.1 Toolbox of the most useful simple functions for FileMaker® Pro adding precise timestamps, application-wide globals, and more... now also for server-side scripts and web publishing Prague, CZ -- September 27, 2011 24U Software announces an immediate availability of 24U Toolbox Plug-In 1.1.1 for FileMaker Pro 11. The new version of 24U Toolbox Plug-In has been updated to run in FileMaker Pro 11 on the most recent versions of Mac OS X and Windows. It also sports new useful functions and capability to be used as a server plug-in for server-side scripts and web publishing on FileMaker Server and FileMaker Server Advanced. What's new in version 1.1.1: - Application-wide globals let developers shared variables between FileMaker files - Precise timestamps allow for measuring of time with microsecond precision - Value type checking can be used to check data type of values passed to calculations - Auto Update savvy version function simplifies automated deployment and updates - Server-side functionality helps creating powerful server-side scripts and web solutions Other Features include: - Execute system scripts (Bash, Perl, PHP, Python, AppleScript, BAT, VBS) - Execute SQL on the current database - Count and substitute text using Regular Expression patterns - Find out which special windows (Script Debugger, Data Viewer) are open - Merge fields and variables into text (faster than using Substitute) - Get the client's public IP address Use 24U Toolbox Plug-In to add a completely new dimension to your server-side data processing Server-side scripts can be efficiently used with FileMaker Server 11 for nightly batch data imports and processing. The powerful features of 24U Toolbox Plug-in let you make this processing much faster and more powerful by being able to use system-level scripts, access your data with SQL queries, merging fields and variables into text, etc. HOnza Koudelka, Software Division Manager of 24U, says: "For a customer project I need to download data from several websites, import the data into a FileMaker database, parse the HTML, and extract thousands of records from the imported data every day. I am using Toolbox Plug-In to trigger curl to download the web pages, SimpleFile to import the downloaded files, and then Toolbox Plug-In again to help with fast parsing and preprocessing of the data. The text merging feature of Toolbox lets me even recognize graphical symbols such as smilies and translate them to text equivalents." 24U Toolbox Plug-In is designed to help FileMaker developers in their daily work by providing a solid toolbox of the most commonly needed simple but powerful functions. Requirements and Compatibility 24U Toolbox Plug-In 1.1.1 requires Mac OS X 10.4 or newer and FileMaker Pro 8.5 or newer. Recommended is Mac OS X 10.6 Snow Leopard and FileMaker Pro 11 Advanced. Windows XP SP3 and FileMaker Pro 8.5 are minimum requirements for using the plug-in on the Windows platform. Windows 7 and FileMaker Pro 11 Advanced are recommended. Some features of the plug-in are specifically designed for FileMaker Pro 8.5 and 9 and do not work with newer versions of FileMaker Pro. Availability and Pricing 24U Toolbox Plug-In is immediately available for download as a free fully functional 14-days trial version, which can be activated after purchasing a license code. More info: http://www.24uSoftware.com/Toolbox Download: http://www.24usoftwa...oolbox#download License for 1 user is $48, additional users pay just $24 or less! Developer license starts at $349 US limited to 20 concurrent users per site, but unlimited to a number of solutions or customers. Server-side license is available for $499 US and covers unlimited server-side scripts and unlimited web users seved by a single installation of FileMaker Server or FileMaker Server Advanced. To purchase visit: http://www.24usoftware.com/Toolbox#buy More about 24U licenses: http://www.24usoftwa...om/licenseguide Discounts are available to FBA, FileMaker TechNet, and FMPUG members. 24U Software offers solutions for people, not for computers! With FileMaker experience reaching back into the early 90's and developing FileMaker plug-ins since 1997, 24U has become recognized a key vendor of plug-ins for FileMaker Pro. Dedicated to creating the easiest to use FileMaker plug-ins... Optimizing performance of FileMaker solutions... Inventing unique iOS applications… Helping organizations to get more out of their technologies... We connect the dare of education with the experience of industry to achieve the impossible. Customer contact: HOnza Koudelka Software Division Manager, 24U s.r.o. FileMaker 8, 10, 11 Certified Developer FileMaker Business Alliance Member mailto:sales@24usoftware.com http://www.24usoftware.com Press Contact: mailto:pr@24uSoftware.com ###
  5. 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?
  6. Hi guys, problem here. I'm working with a web viewer and I'm familiar with triggering a script from within the web viewer using the fmp:// protocol. But, what I need is kind of the opposite. I have a 2000 records list that I need to display in my webviewer. As one may find, that is lots of html data, what makes my solution very slow. What I want is a webviewer that displays the first records and, when I scroll down (my first option) or I click a button (second option, but also ok) it displays more records. I'm aware that I could do that using the url protocol, calling a script, rewriting the html code for the webviewer, but that seems as a dumb solution So, it's my understanding that javascript can change the html of a webpage (played a little with javascript, but not a master). So, in theory, there is a way for javascritp to append to the end of my <body> the next record I could use the ExecuteSQL function (not the script step) if it worked, but I'm not sure how to do that without hardcoding the change of the webviewer code, and I rather not do that. I'm really hoping I can handle it from the webviewer I have a hard time being brief. What I want is to pass a parameter from the filemaker to javascript to execute its function. Any help will be overwhelmingly appreciated
  7. Hi - I have two tables, Visits and Members. A particular member will have many visits, so there is a primary key in Member : Member ID and a matching foreign key in Visits. Each visit has a field which contains an expense. (OOP Amnt Copy). I'd like to get the sum of all the visit expenses for each member, put it in a field for that member and show this in a portal of Members. I'm using the ExecuteSQL function. If I do this: ExecuteSQL("Select Sum (Visits."OOP Amnt Copy") From "Family Member" Left Outer Join Visits on "Family Member"."Member ID" = Visits."Member ID"";"";"") I get the sum across ALL members of all the visits - not individual sums for each member. So I tried Where to do a find of all the matching visits for each member: ExecuteSQL("Select Sum("OOP Amnt Copy") from Visits where "Family Member"."Member ID" = Visits."Member ID"";"";"") THis gets an evaluation error - ? But then, if I replace Family Member.Member ID with an actual value -- 'M001' - it works for that member. ExecuteSQL("Select Sum (Visits."OOP Amnt Copy") From Visits Where Visits."Member ID" = 'M001'";"";"") So shouldn't my second function do the same thing as the last one, but with the calculation for each member being replaced by their member ID? Thanks! Michael
  8. Three tables in question are [CUSTOMERS], [uPDATE], [PRODUCTS] the relationship is many customers to many products with [uPDATE] being the join table. The only field in update is the primary key, the two foreign keys, and a date timestamp. In the [PRODUCTS] table i have a (GROUP) field. In the [CUSTOMERS] table let’s assume there are no fields that will matter in this except the customer ID When i look at the Customer Detail Layout, Which is related to the [CUSTOMERS] table i have Products related in the many to many direction as mentioned above in a portal. In this Customer Detail Layout, I want to show a graph chart that counts how many products the customer has based on the product (GROUP) Field. Using fruit as an example, let’s assume i have these products related to this customer from the [PRODUCTS] TABLE via the [uPDATE] join table. [PRODUCTS TABLE] (NAME FIELD): "APPLE" (GROUP FIELD): "RED" [PRODUCTS TABLE] (NAME FIELD): "APPLE" (GROUP FIELD): "GREEN" [PRODUCTS TABLE] (NAME FIELD): "GRAPE" (GROUP FIELD): "RED" What im trying to accomplish is a chart in the customers detail layout would show the following via graph. RED: 2 GREEN: 1
  9. I need some help with ExecuteSQL. I have a TO “Sales” that is related to Estimates, Inspections, and Proposals via ID_SALES:id_sales. I created some simple ExecuteSQL calculations that work on Estimates and Inspections, but the same calculations will not work with the related Proposals TO. · I know that the relationship is good because I can get the SUM and COUNT from Proposals with a basic calculation (not ExecuteSQL). · I know there is not a problem with the name on the related Proposals TO (like a space between words). I've copied and pasted the working calculations and I have started from scratch, and neither works. This statement works: ExecuteSQL ( "SELECT COUNT (ID_ESTIMATES) FROM TO40_ESTIMATES WHERE ID_SALES = ? “ ; "" ; "" ; TO13_sales_ESTIMATES||id_sales::id_sales ) --- This statement does not work: ExecuteSQL ( " SELECT COUNT (ID_PROPOSAL) FROM TO26_PROPOSALS WHERE ID_SALES = ? "; "" ; "" ; TO13_sales_PROPOSALS||id_sales::id_sales) Any help would be appreciated. SC
  10. Greetings All I am looking to add a line into the following code into the 'prepare payload for client' section to select only records that match the following criteria I have a variable set called: $additional_settings The field that needs to match with $additional_settings is called _kf_uuid_companys the code that i have already is as follows, i need to add a line into this code to pick records where the field _kf_uuid_companys matches $additional Settings. Any help would be greatly appreciated: "SELECT " & $dyn_sql & ", '" & $$record_delimiter & "'" & " FROM \\"" & $sync_table & "\\"" & " WHERE " & // Exclude records flagged for sync exclusion. "( COALESCE ( BS_Exclude, 0 ) = 0 ) " & // If the client is merging new/updated data with data already on the device... // Only include records that have been added/updated since the last pull... // And only include data that they did not just push (i.e. no "round tripping!")... If ( ( $$sync_method = "Merge" ) and ( $last_pull_utc > 0 ) ; "AND ( BS_UTC_Time > " & $last_pull_utc & " ) " & "AND ( COALESCE ( BS_Device_ID, 'X' ) <> '" & $client_persistent_id & "') "; "" )
  11. Two tables Journalists ------ RecordID = JournalistID ------ Affiliations (very simple) 1 Value List called "ListAffiliationsForJournalist", based on Journalists, Affiliations::Name 1 Unstored calculation field on Journalists, ValueListItems ( Get (FileName) ; "ListAffiliationsForJournalist" ) All of this works - if a journalist record has two affiliations records, the unstored calculation field lists the names of both affiliations. HOWEVER: I'm worried about performance. Is there a way to replace this unstored calculation with an Execute SQL function? I just spend several hours reading up on SQL and FileMaker (I'm really new with this), and I'm not getting anywhere... I believe I just need a simple sample to get me started in the above context. Or maybe this doesn't make sense at all?? Thank you so much in advance!
  12. Hi there 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 Let ([ CUST = Customer_Home::Cust_PK; SQL = "Select (SUM(INC)-SUM(COST))/Sum(INC) from DataTable where CustID_FK=? and GroupID = ? and PeriodNumber=?"]; 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) & "¶" )
  13. I created a database to track permits by building. Each permit type is stored in its own table. The only link between tables is the Building Name which is the physical location where the permit exists. Tables: v_Buildings (fields include building name, ID, building status, address, city, state, zipcode) Air (ID, Building Name, Expiration Date, Days_to_Expiration, Type, etc...) Water Food Elevator ...9 permit types in all, and I will add more in the future Each of these tables has the same fields in common: BuildingName PermitExpirationDate PermitOwner Days_to_Expiration I created a Dashboard layout with the idea that users could select a building name from a drop-down menu and then see all of the permits for that particular building along with their expiration date and the permit owner's name. I am just baffled as to how to do this across multiple tables. I have looked at SQL, Join tables, portals etc...., and I cannot figure out how to aggregate all of this information into one view for users. I cannot even seem to figure out what table should be used for the Dashboard layout. I would love to hear from the community the best and hopefully scalable approach for designing this layout. Thanks in advance from a novice user.
  14. Prague, CZ -- December 5, 2011 -- 24U Toolbox Plug-In has been recently updated to version 1.1.1, adding new useful functions and support for server server-side scripts and web publishing. The version 1.1.2 is an important maintenance update recommended for all users of the previous versions. What's new in version 1.1.2: - Registration codes of server-side and client-side licenses can now be used on the same computer - Toolbox_GetTimestamp now correctly returns local time of the computer where it's evaluated Recently introduced in version 1.1.1: - Application-wide globals let developers create variables shared between multiple FileMaker files - Precise timestamps allow for measuring of time with microsecond precision - Value type checking functions can be used to check data type of value passed to a calculation - Auto Update savvy version function simplifies automated deployment and updates of the plug-in - Server-side functionality helps creating powerful server-side scripts and web solutions Other features include: - Execute system scripts (Bash, Perl, PHP, Python, AppleScript, BAT, VisualBasic Script) - Execute SQL on the current database - Count and substitute text using Regular Expression patterns - Find out by calculation which special windows (Script Debugger, Data Viewer) are open - Merge fields and variables into text (faster than using Substitute) - Get the client's public IP address Use 24U Toolbox Plug-In to add a completely new dimension to your server-side data processing 24U Toolbox Plug-In is designed to help FileMaker developers in their daily work by providing a solid toolbox of the most commonly needed simple but powerful functions. Pricing: $48 for 1st user, $24 additional, $349 developer, $499 server More info: http://www.24uSoftware.com/Toolbox Download: http://www.24usoftwa...oolbox#download Purchase: http://www.24usoftware.com/Toolbox#buy This announcement is intentionally brief to not bother members of this mailing list who are not interested in detailed information. To read complete Press Releases, please visit the following page: http://www.24usoftware.com/pr 24U Software offers solutions for people, not for computers! -- HOnza Koudelka Software Division Manager, 24U s.r.o. FileMaker 8 Certified Developer FileMaker Business Alliance Member mailto:sales@24usoftware.com http://www.24usoftware.com ###
  15. The new version of SQLexplorer includes tools to review your query results and refine your query before pasting it into your FileMaker solution. And of course it still writes SQL queries for beginner so you can learn FileMaker SQL as you're putting it to use in your own files. Query results are now displayed in an HTML5 portal with native column resizing and sorting. And you can interrogate your results, by extending the query, and then paste these extensions back in as additional "and" criteria: Learn about all the new features here: SQL Explorer New Features. Oh, and all that HTML5 can be skinned in-app. (Props to Jason Young for some amazing work here!) The file is completely unlocked for you to checkout. Download SQLexplorer and see a video of the new features in action here: FileMaker SQL Explorer. Enjoy!
  16. I am trying to create a list of dates from multiple tables to create a gantt chart. My projects have Shipping dates, Installation dates and task item dates. Each of which come from their own tables. Is there a good way to import all of these dates into one table related to the project, each with it's own record of Start date and end date. I'd rather have: Project | Item | Start_Date | End_Date| than: Project | Install_Start_Date | Install_End_Date| Ship_date | Ship_Arrive_Date | Task_Start_Date | Task_End_date
  17. Hi all, I am trying to work with Invoices and the programs looks fine the only thing i want to change is that in the Menu -> Invoices -> Product Id. I want to add a button in the left of Products Id called "Find" and when i will insert an Id for example 3 and press the botton, i want to appear a drop-down menu to the current Line Item and show me a list of Id, Name of producst that have similar name as the name of the product that i entered. I came with an algorithm in my mind but i have never worked with FileMaker scripting and i dont know how to implement this onButtonClick { myId = take from the current selected line item the Product ID; myName = find the Product Name that corresponds in this ID; execute the query (SELECT Id, Name FROM Products WHERE Name LIKE '%myName%' AND Id!=myId;) create a drop-down list in the current line item; put the result of the query. } Ι would really appreciate it if someone could and would like to help me, Thanks in advance.
  18. Hi all I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are: Clients Client_ID, Seats Jobs Client_IDF, Job_time, Job_Date The best I've managed so far is to find the distinct Client_IDF from all Jobs: ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J WHERE j.Log_date >= ?"; ""; ""; $date_30_days_ago) And then loop through the list and perform the 'divide by seats' calculation for each line Or to do something similar but from the Clients table: ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C JOIN Jobs J ON j.log_client_IDF = c.Client_ID WHERE l.Log_date >= ?"; ""; ""; $date_30_days_ago) But no joy in either case when it comes to actually performing calculations within SQL. Is this possible in FM SQL? Many thanks
  19. Can anyone see whats wrong here: Let ( [ invoiceID = id ; decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ; SQL = ExecuteSQL ( " SELECT SUM ( b.\"Amount\" ) FROM \"Invoice Data\" b WHERE b.\"id_Invoice\" = ? AND b.\"Type\" = Rental " ; "" ; "" ; invoiceID ) ] ; If ( SQL ; Substitute ( SQL; "." ; decimal ) ; 0 ) ) There are 2 tables Invoice and Invoice Data. The items added to the invoice can either be a rental, Sale or service item and want to total each type on the invoice layout.. Thank you
  20. Hi there, for each record in table "Beleg" I need to create 1-5 records in table "Buchung". How can I easily create records in another table (within a script) while remaining in the first table? Switching layouts is way too slow when you have lots of records. An answer for this first question would be cool. However, this begs for another question: What about the 1-5 records that need to be created would delete themselves when the user makes changes to the records in the first table? Ideally, a commit record in "Beleg" should automagically insert, correct or delete the corresponding records in "Buchung". BTW, the table "Buchung" will not be edited/changed in any way by the user, only displayed/exported. I hope I can make myself clear :-) See example below. Thanks a lot for any help! Gary Example clarifying the second question: One record in "Beleg": idBeleg1 item1 value1 value2 value3 valueN count1_5 Lets say: count1_5 = 1---- Corresponding record in "Buchung": idBuchung1 idBeleg1 Beleg::item1 Beleg:value1 Another record in "Beleg": idBeleg2 item1 value1 value2 value3 valueN count1_5 when count1_5 = 1-3-5 idBuchung1 idBeleg2 Beleg::item1 Beleg:value1 idBuchung2 idBeleg2 Beleg::item1 Beleg:value3 idBuchung3 idBeleg2 Beleg::item1 Beleg:value5 Yet another record in "Beleg": idBeleg3 item1 value1 value2 value3 valueN count1_5 when count1_5 = 1--4- idBuchung1 idBeleg3 Beleg::item1 Beleg:value1 idBuchung2 idBeleg3 Beleg::item1 Beleg:value4 When the user changes for example Beleg::value1 in record 1 from $22 to $33, all value1 in "Buchung" relating the current record in "Beleg" will change. Thats cool. But, when count1_5 changes, it would be way cool if the corresponding records in "Buchung" could automatically be deleted/inserted. P.S.: It would be so easy using sql (at least for the first problem), but I don't want to carry a plugin with me all the time (FileMaker's ExecuteSQL doesn't do INSERT).
  21. Hi all I'm trying to connect to an Azure SQL database from FileMaker 16. I've added the DSN via ODBC 32bit (see pic). However, when I go to File>Manage>External Data Sources, it doesn't show the DSN. Where am I going wrong? Thanks in advance
  22. Hi, I need help with SQL and Running Total (instead of Total). TABLES INV = Invoice table - INV::Amount ( Amount of Invoice) - INV::DateMonth ( Month of InvoiceDate) - INV::DateYear ( Year of InvoiceDate) DTE = Months table (fixed 12 records) - DTE:Order=1...12) - DTE::CurYear = 2016 Below SQL request works, see my result! But what I would require is a running total, adding each month sales total, see my "Required result" ExecuteSQL ( " SELECT SUM(a.\"Amount\"/1000) FROM \"DTE\" b LEFT JOIN \"INV\" aON b.\"Order\" = a.\"DateMonth\"AND b.\"CurYear\" = a.\"DateYear\"GROUP BY b.\"Order\""; " | " ; "0¶" ) My current result as above SQL request 726.605050 818.7150 739.067850 0 703.7210 620.805350 599.986160 506.9230 597.8370 801.78250 1014.11510 567.64575 Below is what I require (a Running Total)726.605051545.320052284.38792284.38792988.10893608.914254208.900414715.823415313.660416115.442917129.558017697.20376 I can do this without SQL, but I want to do using SQL and use it for my Charts. Would appreciate any help. My SQL understanding is basic and I am using FileMaker 14 Adv
  23. Hello, 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. Query field; SELECT SUM(Total) FROM financials Where "Cost Code" = 'FA' and EID = '982 000147908607' Please find attached images highlighting and showing everything relevant. Kind regards Pat
  24. I've recently been studying the ExecuteSQL function, and thus SQL as well. I've learned quite a lot and am able to write equations using dynamic WHERE clauses, JOIN etc. I find nesting the function in a Let is very efficient. Ok . . . Now my silly question. Where do I use this amazingly fast and flexible function? I mean where can I insert it in an existing solution. I can see where it would allow for the elimination of many TOs, but if the TOs exist to enable data input in a related table I can't see how SQL will help. Should I view ExecuteSQL as simply a function to make reporting more efficient? Does anyone out there have a classic use of the function that really made a big difference in a solution? Don't get me wrong. In my next solution I will make extensive use of SQL, but now I'm at a bit of a loss even though I've managed to use the function to return exactly what I set out to return. Rick.
  • Create New...

Important Information

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