Jump to content

Search the Community

Showing results for tags 'executesql'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Community Forums

  • The New FileMaker Platform
  • 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 Advanced 18
    • FileMaker Pro Advanced 17
    • Legacy FileMaker Platform Discussions
  • FileMaker Server Administration
    • FileMaker Server 18
    • Zabbix Server Monitoring
    • FileMaker Server 17
    • FileMaker Cloud
    • FileMaker Custom SSL Certificates
    • oAuth and External Server Authentication
  • Brain Food
    • Security Concepts
    • The Left Brain
    • Upgrading & Migration
    • Data Analysis
    • Development Standards
    • The Separation Model
    • Relational Database Theory
    • Damaged / Corrupt File Problems
    • Bar Codes (Printer, Scanners, Software)
    • Hardware & Networking
    • OS Level Database Automation
    • Product Ideas
  • JavaScript Integration
  • FMForums Affiliates & Sponsors
  • FileMaker Classifieds
  • FM Forums Operations
  • FileMaker Friday Night Chat's Topics

Blogs

There are no results to display.

There are no results to display.

Categories

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

Product Groups

  • Workplace Innovation Platform
  • Site Advertising
  • Development & Hosting

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Title


Industry


Location


Interests


Website URL


Skype


MSN


Yahoo


OS Version

Found 41 results

  1. I'm having a syntax problem getting the 'IN' clause to work in FM's ExecuteSQL statement. I would like to use a '?' placeholder in the statement (just looks cleaner), but when I do it doesn't work. I already have a string that contains the list of numbers (this is all numbers...except the fact that it is a string of numbers :) ). I want to use that variable in this statement. Here's a WORKING example: Let ( [ ids = "281,282,283"; sq = "SELECT ID_fk, sum(Amount) FROM Payments WHERE ID_fk IN ( " & ids & " ) GROUP BY ID_fk" ] ; ExecuteSQL ( sq ; "" ; "" ) ) But it's not using the '?' placeholder. What I would like to do instead: Let ( [ ids = "281,282,283"; sq = "SELECT ID_fk, sum(Amount) FROM Payments WHERE ID_fk IN ( ? ) GROUP BY ID_fk" ] ; ExecuteSQL ( sq ; "" ; "" ; ids ) ) I found some custom functions from folks that help format this correctly, but I would rather not use a custom function for just this one need (only doing this once). Thanks, -- Justin
  2. I have used the combination of ExecuteSQL and ValueCount quite a few times throughout my databases before, with almost no issues. Generally what I will do is set a variable by running an ExecuteSQL, then take a ValueCount of that variable. Then with that ValueCount I will loop through my original ExecuteSQL variable. However, I am now trying to do something different with this combination. Instead of using the ValueCount variable for a loop, I'm trying to use it as a Global Variable that will be displayed on the main menu. Here is an example of the code I'm trying to use: Set Variable [$source; Value: ExecuteSQL ( "SELECT UUID FROM Inspections WHERE Inspector = ? AND Accepted ≠ Accepted" ; "" ; "" ; $$fullName )] Set Variable [$$accepted; Value: ValueCount ( $source )] The UUID field is, as expected, an auto-entered "Get ( UUID )" field. The Inspection table is a table containing all of our part inspections. Each inspection begins with it's Accepted field having a value of "Rejected", if the correct parameters are met this field will become "Accepted". The Inspector field contains the first and last name of whomever conducted the part inspection. The $$fullName variable is set previously in this same script by comparing the user's Username to a Table that contains all of our user's Usernames and Full Names. What I was hoping to do is use a Merge Variable on our main menu that displays our $$accepted variable, showing the user how many unaccepted inspections from that particular user in our database. However, it only ever displays a value of "1". One of our users has 6 unaccepted inspections in our database currently, but this script only shows that he has 1. I am wondering if you can't use Merge Variables or ExecuteSQL in this way, or if I'm simply missing something obvious. Please let me know of any more information I should provide. Thank you for any assistance you may offer!
  3. My statement is failing when I try to add something to the value of an aggregate function. For example, this work fine: select Min(Lat) from Points This fails: select Min(Lat) + 1 from Points Is this a bug? Is there some other syntax I can use to accomplish this? I'm aware I can just add 1 to the result of the ExecuteSql, but I'm doing something more complex and this is just simplified version of the bug I found.
  4. To begin, I have about 100,000 records (and growing) in a "Parent" table. Each Parent will have at least one "Child" (usually about 2 or 3, but can be 5 or 6). Each Child has a "Quantity" calculation field {sum( in - out)} which is related to the "Transactions" table.  I'm trying to filter a table (in list view) of the "Product" aka. parent to show only products that have any "Inventory" items aka. child that have quantity > 0.  I'm able to generate the results, my problem is the speed at which it calculates  For instance, "Prod A" might have three children: A_1, with qty 1 A_2, with qty 0 A_3, with qty 0  "Prod B": B_1, with qty 0 B_2, with qty 0  "Prod C": C_1, with qty 0 C_2, with qty 0 C_3, with qty 5 C_4, with qty 7  Therefore, I created a calculation field called "invQty"in the Product table as such: sum (inv::quantity). I have a button on my product layout attached to a script that looks for a value > 0 in the invQty field.  The result of the above example will display "Prod A" and "Prod C" only, which is great but it takes about 45 seconds. (Script is performed on client, file is hosted on FMS)  Would this be a time to use executeSQL, if so, how would the calculation be written (I have yet to use this function) from what i've read it isn't much faster? Otherwise, is there a faster way to do this? You guys always come up with some clever stuff, hope you can help me.  Additional thoughts: I've been working with PSoS lately, and it's been amazing, but not sure if this can work here to get a found set to display on client. I've also been looking into the "MasterDetail" setup by Todd Geist. Pretty awesome stuff, just not sure if it's the right solution for me yet.  Thanks in advance to everyone.. I also attached an image of the relationship for this example. I put a lot of information for this simple task, just wanted to be clear as possible.   Â
  5. This is my first time posting, and I apologize if this is the wrong place. I've only been filemakering for a few months, so again, apologies if this is a stupid problem. I could use some advice. I'm working on a solution that must create monthly reports in the following form: http://i.imgur.com/Cf4Fjic.png I've given up trying to bend the summary fields to my will, and began using the following approach: Make two new tables - Queries and Reports. In queries table, I have fields for M1, M2, M3, Region, Category, Subcategory and four result fields (M1, M2, M3 and YTD.) I also have foreign key into the reports table. Then in reports table, I portal into the Queries table. When report is run, a bunch of queries in queries table are created. The Results are populated via ExecuteSQL expressions. My question is mainly this: Is this too backwards? I feel like maybe this is too weird of an approach and I"m just missing something simple about reporting and creating summaries? Thanks!
  6. Here is a demo of conditional value lists using FMP12 and ExecuteSQL. In the demo there is one sample of using straight text and another using IDs instead of the text. There are no relationships between the user interface and the tables of values. ConditionalVL_SQL.zip
  7. Hi, I can't seem to get filemaker pro to plot google charts the way I want them in web viewer. I want the graph to look something like this: ...Seems it would be pretty basic at first but I can't get this particular multi-series chart to work. It needs to plot ALL the records and MORE THAN ONE series. I've created one table called 'MyTable' with 3 fields 'CarName', 'CarSpeed' and 'CarWeight'. I've populated the table with 5 records. CarName on the Y-Axis, and against this, CarSpeed and CarWeight are plotted on the X-Axis as 2 series. i.e. series one is 'CarName' vs 'CarSpeed', series 2 is 'CarName' vs 'CarWeight' I've managed to successfully create 2 graphs, first one plots the series ok but only works for one record. The second, in which I've used an ExecuteSQL command to collect the table data from FMP, shows all the records but for only one field on the X-Axis. Any help appreciated. I'm running filemaker pro 14, and I've attached the fmp file, as well as the javascript code from the web viewer of each graph. Any help greatly appreciated Google Charts-Demo File.fmp12 AllRecords-OneSeries.rtf AllSeries-OneRecord.rtf
  8. I've been working on optimizing a complex part of a solution that relied on the List() function to send multiple parameters to a custom function. Through my research I came across this article: http://www.teamdf.com/weetbicks/the-search-for-fast-aggregates--trial--error/99/ This article discusses the fact that when you use List(), Sum() or other aggregate functions, FileMaker sends the entire record set to the client, not just the data from the requested field(s). Does anyone know if this is also the case with ExecuteSQL()? I would assume that an SQL query would only return the requested data, but I'd like to know if this is definitely happening or not.
  9. Can someone enlighten me why this fails: ExecuteSQL( "SELECT COUNT ( IDs ) FROM TableA WHERE aaName = "Bob Bob" " ; "" ; "" ) But this one works? ExecuteSQL( "SELECT COUNT ( IDs ) FROM TableA WHERE aaName = 'Bob Bob' " ; "" ; "" ) To mine eyes the only difference is the single quotes, and I thought that the escaped double quotes should be an acceptable form. I know I have used escaped double quotes before, I swear. (Oh wait, that's right, I am swearing too much recently. ) I guess I have used escaped quotes around field/table names but never as part of the WHERE clause's string-to-match. I typically have used the replaceable parameter in these cases; but this time I was trying to put it directly in the statement. I.e. this version also works: ExecuteSQL("SELECT COUNT ( IDs ) FROM TableA WHERE aaName = ?" ; "" ; "" ; "Bob Bob" ) Thanks, C
  10. Hi, I noticed that using ExecuteSQL in a calculation field doesn't mark the field as unstored although I used other TO. So the question is : "Are the result stored or not?". Thanks
  11. Hi, I'm having problem trying to get this query work. Let ( [ q = "SELECT i.c_nomComplet FROM personnesiPad as i LEFT OUTER JOIN personnesServer as s ON i.uuid = s.uuid WHERE i.__modification_ts <> s.__modification_ts"; r = ExecuteSQL (q; ";"; "¶") ]; r ) The problem seems to be my fieldnames in the WHERE clause. I've tried to quote them with the quote function, but I still have a validation error. How do I managed to use fieldnames with special characters? BTW, I'm trying to do a synchronisation file between an ipad and a server. Thanks for helping!
  12. Just trying to learn this executeSQL and can not seem to get sensible results. I have a table which represents any cost prices changes that occur with a product. If I use a simple statement eg ExecuteSQL ( "SELECT ProductDescription FROM CostPriceChanges" ;"";",") i get a nice list of products show up eg Sweet Potatoes, Limes, Limes, Lettuce, etc etc However when i add where criteria I get the dreaded ? ExecuteSQL ( "SELECT ProductDescription FROM CostPriceChanges WHERE Cost Price >= .01" ;"";",") I am thinking, it is something in the syntax of the where that i am not getting into the grey matter but that is where i am having trouble. Any Help would be appreciated.
  13. Having trouble with an ExecuteSQL calculation. I used SeedCode's SQL Explorer to build the calculation. It works fine if I use literal values from a specific record; but if I instead change it to a field reference, the calculation fails. I get the message <unrelated table> in the Data Viewer. The code is below. Am I misusing the field names somehow? // Build SQL Query q = "SELECT SUM ( " & ainterestMineralDecimal & " ) FROM " & a@LINEITEMS & " WHERE " & a_id_lease & " = ? AND " & a_id_tract & " = ? AND " & a_id_unit & " = ? " ; // Run SQL Query result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; @LineItems::_id_lease ; @LineItems::_id_tract ; @LineItems::_id_unit ) ] ; Thanks, Tom RuBane
  14. I have created a "viewer" using a virtual list technique and ExecuteSQL. The "viewer" is a portal that contains one field - a unstored calc text field from the virtual list table. This allows me to run a script for a "report" and have the results appear in this field. I can run a different script and have different data appear in that field. Everything works great, except column widths. I understand how to adjust Tabs, alignment, etc using FM>>Line Spacing>>Tabs. However, that sticks to the field regardless of the data it is showing. I can use hard tabs in the script to place tabs where I want but only for FM fields or text (for column headers for example). I set a dozen tabs in FM>>Line Spacing>>Tabs - one every 50 points and then used Hard Tabs n the script to move column headers (simple text) over as many tabs as desired. This works great. However, I can't figure out how to control the column width WITHIN the ExecuteSQL function. I need this, as I will end up with dozens of "reports" that I want to view in this single field, all created on the fly via scripting. Is it possible to control the tab width with the ExecuteSQL function or am I heading down the wrong path? Thanks in advance for any suggestions!
  15. 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
  16. I'm working on a cross-tab report using ExecuteSQL. I'd like to try to do it as a single eSQL statement but I'm not sure if its possible or not. I'm still a bit of an eSQL newbie. Background, the database manages work done in a condominium building. Each apartment in the building is identified by a floor and a location in the building; 1-9 for the floors and N, NE, SE, W for the location. For the report, the floor is on the vertical axis and location is the horizontal axis. Each cell in the report is a sum of apartments where the work has been completed. I have the first part of the statement working, I can grab a total number of apartments completed on each floor. What I need is the total for each location, on each floor. So far the SELECT statement looks like this. I didn't include the eSQL and the delimiter bits. It returns the floor # and the total number of apartments completed correctly. "SELECT a.\"apt_loc_floor\", COUNT (a.\"zz_ID\") FROM \"rep_Apartments\" a INNER JOIN \"accounts\" b ON a.\"zz_ID\" = b.\"zz_fk_apartment\" WHERE b.\"ac_conversion_date\" is not null GROUP BY a.\"apt_loc_floor\" ORDER BY a.\"apt_loc_floor\" I know it could be done via several eSQL statements but I'd like to try to do it with as few as possible. Any suggestions? Also, the second field for the location is "apt_loc_location" Dave Zakary
  17. I am relatively new to sql queries in Filemaker, and I'm receiving the ? in response to the following queries. Because I don't have the advanced version of FMP, I used Evaluate() to return the error code 8310 (syntax error). However I can't figure out where my syntax error is. Hopefully more experienced eyes will spot my problem(s). The following is entered in a calculated field that returns text: Let( [ $initials=ExecuteSQL("SELECT TaskAssignedTo FROM workflow WHERE" & Quote("_IDDocLog") & "=workflow::" & Quote("_IDDocLog") & "AND workflow::TaskNumber=9"; "" ; "" ); $name=ExecuteSQL ("SELECT FullName FROM staff WHERE AccountName= $initials"; "" ; "") ]; $name ) Thanks!! Oops I meant to say that I used EvaluationError()
  18. I am developing a Professional Development database for our school district. I have several tables with one being our STAFF (our existing staff names, positions, etc). Another being COURSES begin offered for staff to take. The COURSES table has a STATUS field than is either Open or Closed. If Open, staff can sign up. I would like a Portal to display the Courses Open within the Staff table, but because the Status is a calculation table, the Portal will not work. So I was hoping to create a Script using ExecuteSQL to build a Virtual List to display the results in the Portal. Problem is, I am new to ExecuteSQL and Virtual Lists, so I am in need of some assistance. Been looking at the web for some learning techniques, but haven't found anything yet.
  19. Hi, I'm trying to use Filemaker to manage a mysql database on Amazon RDS. I'm trying to set up an import layout that will allow users to load data into into the RDS database. Mostly, Filemaker and mysql are playing well together. But I'm getting a problem when I try import data using LOAD DATA LOCAL INFILE . I've run the query successfully with a different client on the same database (Sequel Pro) but when I run it using the Execute SQL script step in Filemaker I get an error, with the message "Load data local infile forbidden". I've looked around online, and it seems like I need the server and client to both enable 'local_infile'. I know it's enabled on the server (because I can check manually, and the query works on a different client). Does anyone know how to check or change this within Filemaker? Otherwise, does anyone have any ideas as to how I might work around this? Unfortunately I can't call a stored procedure on the server because the query relies on variables. My understanding is that you can't use variables in a LOAD DATA INFILE statement on the server, and you can't use LOAD DATA INFILE in a prepared statement. Does anyone know how to change the local_infile variable within the Filemaker mysql client, or failing that, does anyone have a good workaround for the restriction on variables with the LOAD DATA INFILE statement if I run it on the server? In case it's not already blatantly obvious, I don't really know what I'm doing, so any help (especially help that is dumbed down) would be hugely appreciated. Thanks, John
  20. Hi all Can someone tell me if this query is correct? I'm trying to find all currently open Cases, but only those that belong to a personnel whose 'exclude_from_stats' field is null. So far this does seem to give me the correct results, however I am a bit uncertain as I have been having problems recently defining criteria in related records. It seems that you sometimes have to resort to different types of joins or you run the risk of excluding/including too many records. SELECT COUNT (*) FROM Cases C JOIN Personnel P ON p.personnel_ID = c.User_IDF WHERE c.Closed IS NULL AND p.exclude_stats IS NULL Many thanks
  21. Hello, I'm trying to create a virtual list ( 70 Columns X 300 Rows ) with the below codes. But it fails after the 30th SELECT clause. I cannot figure out, please help. Thanks, Recep Let ([ @x1 = GetValue ( $$YemekIDList ; 1 ); @x2 = GetValue ( $$YemekIDList ; 2 ); @x3 = GetValue ( $$YemekIDList ; 3 ); @x4 = GetValue ( $$YemekIDList ; 4 ); @x5 = GetValue ( $$YemekIDList ; 5 ); @x6 = GetValue ( $$YemekIDList ; 6 ); @x7 = GetValue ( $$YemekIDList ; 7 ); @x8 = GetValue ( $$YemekIDList ; 8 ); @x9 = GetValue ( $$YemekIDList ; 9 ); @x10 = GetValue ( $$YemekIDList ; 10 ); @x11 = GetValue ( $$YemekIDList ; 11 ); @x12 = GetValue ( $$YemekIDList ; 12 ); @x13 = GetValue ( $$YemekIDList ; 13 ); @x14 = GetValue ( $$YemekIDList ; 14 ); @x15 = GetValue ( $$YemekIDList ; 15 ); @x16 = GetValue ( $$YemekIDList ; 16 ); @x17 = GetValue ( $$YemekIDList ; 17 ); @x18 = GetValue ( $$YemekIDList ; 18 ); @x19 = GetValue ( $$YemekIDList ; 19 ); @x20 = GetValue ( $$YemekIDList ; 20 ); @x21 = GetValue ( $$YemekIDList ; 21 ); @x22 = GetValue ( $$YemekIDList ; 22 ); @x23 = GetValue ( $$YemekIDList ; 23 ); @x24 = GetValue ( $$YemekIDList ; 24 ); @x25 = GetValue ( $$YemekIDList ; 25 ); @x26 = GetValue ( $$YemekIDList ; 26 ); @x27 = GetValue ( $$YemekIDList ; 27 ); @x28 = GetValue ( $$YemekIDList ; 28 ); @x29 = GetValue ( $$YemekIDList ; 29 ); @x30 = GetValue ( $$YemekIDList ; 30 ); @x31 = GetValue ( $$YemekIDList ; 31 ); @x32 = GetValue ( $$YemekIDList ; 32 ); query = "SELECT DISTINCT MusteriIsmi , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) , ( SELECT SUM ( Porsiyon ) FROM vl_UPLItems AS b WHERE b.PlanTarihi = ? AND b.\"_idOgun\" = ? AND MusteriIsmi = a.MusteriIsmi AND \"_idYMK\" = ? ) FROM vl_UPLItems AS a ORDER BY a.MusteriIsmi"; result = ExecuteSQL ( query ; "•" ; "" ; UPL::PlanTarihi ; UPL::_idOgun ; @x1 ; UPL::PlanTarihi ; UPL::_idOgun ; @x2 ; UPL::PlanTarihi ; UPL::_idOgun ; @x3 ; UPL::PlanTarihi ; UPL::_idOgun ; @x4 ; UPL::PlanTarihi ; UPL::_idOgun ; @x5 ; UPL::PlanTarihi ; UPL::_idOgun ; @x6 ; UPL::PlanTarihi ; UPL::_idOgun ; @x7 ; UPL::PlanTarihi ; UPL::_idOgun ; @x8 ; UPL::PlanTarihi ; UPL::_idOgun ; @x9 ; UPL::PlanTarihi ; UPL::_idOgun ; @x10 ; UPL::PlanTarihi ; UPL::_idOgun ; @x11 ; UPL::PlanTarihi ; UPL::_idOgun ; @x12 ; UPL::PlanTarihi ; UPL::_idOgun ; @x13 ; UPL::PlanTarihi ; UPL::_idOgun ; @x14 ; UPL::PlanTarihi ; UPL::_idOgun ; @x15 ; UPL::PlanTarihi ; UPL::_idOgun ; @x16 ; UPL::PlanTarihi ; UPL::_idOgun ; @x17 ; UPL::PlanTarihi ; UPL::_idOgun ; @x18 ; UPL::PlanTarihi ; UPL::_idOgun ; @x19 ; UPL::PlanTarihi ; UPL::_idOgun ; @x20 ; UPL::PlanTarihi ; UPL::_idOgun ; @x21 ; UPL::PlanTarihi ; UPL::_idOgun ; @x22 ; UPL::PlanTarihi ; UPL::_idOgun ; @x23 ; UPL::PlanTarihi ; UPL::_idOgun ; @x24 ; UPL::PlanTarihi ; UPL::_idOgun ; @x25 ; UPL::PlanTarihi ; UPL::_idOgun ; @x26 ; UPL::PlanTarihi ; UPL::_idOgun ; @x27 ; UPL::PlanTarihi ; UPL::_idOgun ; @x28 ; UPL::PlanTarihi ; UPL::_idOgun ; @x29 ; UPL::PlanTarihi ; UPL::_idOgun ; @x30 ; UPL::PlanTarihi ; UPL::_idOgun ; @x31 ; UPL::PlanTarihi ; UPL::_idOgun ; @x32 ) ]; result ) // end
  22. Hi - I've been trying a number of approaches to get a calculated value list using ExecuteSQL code. I initially tried putting the SQL code directly into the Value List definition but that didn't work. Right now, I've done this: I created a UnusedClaims field in a Visit table, whose value is calculated by this: ExecuteSQL(" Select \"_k1_xSAClaim_ID\" FROM \"xSA Claim Status:xSAClaim\" WHERE \"_k1_xSAClaim_ID\" NOT IN ( Select \"_k2_ClaimID\" FROM \"xSA Claim Status:VisitClaims\" WHERE \"_k2_VisitID\" = ? ) " ;""; "" ; Visits::${Visit ID} ) I then put a Claim TO in and related its primariy ID variable to the UnusedClaims field in the visit table. Then I created a ValueList, using the _k1_xsaClaim_ID value from that related Claim TO, and in the value list definition clicked Use only related values from the Visits table. Sometimes this works - but other times it includes values that are in the list that should be NOT IN. The ExecuteSQL code works perfectly in the data viewer, but the Unused Claims field, which has the exact same code in it, includes values it should not include. What am I missing?
  23. I have a question which might just come down to improper syntax. I'm giving my first attempt at using the ExecuteSQL command and I've gone through some of the primers (specifically the one by Beverly) but I am having trouble with what seems like a basic query. I'm attempting to find the primary key of my record in a table using two fields and their data. Here's what I got: Let ( [ x = 1; y = 2013; $query = " SELECT _pkLotteryRecordID FROM Lottery WHERE LotteryGrade = ? AND LotterYearOnly = ? " ; $result = ExecuteSQL ( "$query"; "" ; "" ; x ; y ) ]; $result )
  24. MightyData, a Platinum Member of the FileMaker Business Alliance, will present two Unconference sessions, one PreCon session and one FileMaker Training Series session at the 2013 FileMaker Developer Conference in San Diego. "10 Reasons I Believe in Value Pricing" is an Unconference session hosted by Kirk Bowman, Visionary of Value at MightyData and a Practicing Fellow at the VeraSage Institute. On September 17, 2009, Kirk made a public commitment on the FileMaker Talk podcast to change his business model from hourly billing to value pricing. He had fully changed over his business by January 1, 2011. Since then he has had several successes and some failures. This session will be an open discussion of what works, what does not work, and why value pricing is a viable alternative for a developer to sell his knowledge and experience to improve the lives of his customers. "Bare-Bones ExecuteSQL: The Unconference Session for SQL Newbies" is an Unconference session hosted by Darren Burgess, Maestro of Metamorphosis at MightyData. Darren has spent the last eighteen months learning to use the ExecuteSQL function with FileMaker and has written about his experiences in blog posts such as "FileMaker SQL Nightmares: Tips for Degugging SQL Queries." Recently, he rewrote the SQL queries for a customer solution to make the code more dynamic and less vulnerable to changes in the schema. Darren will share what he has learned including code examples, custom functions, and resources to the SQL novices use the ExecuteSQL function in his development. "Bare Bones PHP" is a PreCon session presented by Anders Monsen, Architect of Integration at MightyData. This session will dispense with theory and focus on writing basic PHP code using a text editor. The attendee will learn how to search a FileMaker database, display the results and handle errors using the FileMaker API for PHP. This is the fourth year MightyData has offered the Bare Bones session at developer conference. Anders shares PHP posts regularly on MightyData’s blog, such as "PHP Development for FileMaker Developers." "FTS: FileMaker Web Publishing" will also be presented by Anders, a FileMaker Authorized Trainer. The session will be an overview of the Instant Web Publishing and Custom Web Publishing technologies in FileMaker Server. The attendee will learn when to use each technology and what the requirements are to set up each one. Anders will share his experience from deploying with web publishing with FileMaker Server over 100 times in his career. The schedule for the two Unconference sessions will be announced on Twitter the evening of Sunday, August 11. Follow @mightydata to receive the latest updates. Bare Bones PHP will be Monday, August 12 at 10:30am. FTS: FileMaker Web Publishing will be Thursday, August 14, at 9am. Both the PreCon and FTS session are part of the Gold Package for the FileMaker Developer Conference. At MightyData, we’re all in. That means, everything we do, we do with a never-ending commitment to create value for our customers. Our relentless pursuit of what’s possible with technology gives us the power to help transform your business from respectable to remarkable. When it comes to delivering value, we pack a mighty punch.
  25. Hi Everybody, I am trying SQL Explorer from Seedcode (http://www.seedcode.com/filemaker-sql-explorer/) because it is a great option to create EXECUTESQL queries. I have found that with this tool it is possible to export my table data to an HTML grid (using jquery). This option is fantastics in my case to export my reports and upload to dropbox. Then I have used site44.com plugin to link my dropbox account and have my own "website". You can check the result here: http://proteomics.site44.com/S10/ The problem is that I want to modify the jquery files to get a Grid with subgrid as in this example: http://trirand.com/blog/jqgrid/jqgrid.html (in "Advanced" item > "Grid as Subgrid") but I don't have any idea of jquery. Could anybody help me to modify the jquery structure code to change the grid view? Thank you very much, Wardiam.
×
×
  • Create New...

Important Information

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