Jump to content

Search the Community

Showing results for tags 'executesql'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Community Forums

  • The New Claris Platform
  • Community Resources
    • Community Videos, Tips, & Techniques, Articles.
    • FileMaker Marketplace Discussions
  • FileMaker Platform
    • FileMaker Interface Features
    • FileMaker Schema & Logical Functions
    • FileMaker Go for iPad and iPhone
    • FileMaker and the Internet
    • FileMaker Pro 19
  • FileMaker Server Administration
    • FileMaker Server 19
    • FileMaker Server 19 (Linux)
    • FileMaker Cloud
    • FileMaker Custom SSL Certificates
    • oAuth and External Server Authentication
    • Zabbix Server Monitoring
    • Previous Version Server Discussions
  • JavaScript Integration
    • Java Script and the Web Viewer
    • Carafe.FM
    • Widget Studio
  • Brain Food
    • Security Concepts
    • The Left Brain
    • Upgrading & Migration
    • Data Analysis
    • Development Standards
    • The Separation Model
    • Relational Database Theory
    • Damaged / Corrupt File Problems
    • Hardware & Networking
    • Product Ideas
  • FMForums Affiliates & Sponsors
    • Productive Computing Support Forums
    • 360 Works Official Product Support Forums
    • Proof+Geist
    • FileMaker Vendors
    • FileMaker Training Resources
  • FileMaker Classifieds
    • Announcements of FileMaker Product, Services or Resources
    • Classifieds
    • Tools Of The Trade
  • FM Forums Operations
    • FM Forums Feedback & Site News
  • FileMaker Friday Night Chat's Topics

Blogs

  • Captain's Blog
  • FileMaker Weetbicks
  • FileMaker Security Blog
  • The FileMaker Rift
  • HOnza's Bits @ FMForums
  • SeedCode
  • FileMaker Hacks
  • Skeleton Key's Blog
  • Wing Forward Solutions
  • 360Works Articles and Tutorials
  • GoBillit
  • Filemaker - an amateur's view
  • Linear Blue
  • Surya Kanta Mekap's Blog
  • eXcelisys' Blog
  • Manjit Behera
  • Blue Feather's Blog
  • RCC's FileMaker News and Bits
  • Thorsen Consulting
  • DB Services Blog
  • FileMaker Magazine
  • Eye on FileMaker
  • Dev Talk
  • The Philosophy of FileMaker
  • 1-more-blog
  • Productive Computing
  • Geist Interactive
  • Soliant Consulting

Categories

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

Product Groups

  • Workplace Innovation Platform
  • Site Advertising
  • Development
  • FileMaker Hosting

Calendars

  • FileMaker Events
  • Community Calendar

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

  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. 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
  3. 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!
  4. 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!
  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. I am dealing with a very weird condition in my solution where an ExecuteSQL statement was failing to return a matching record. My solution tracks documents such as deeds and leases, where a document can have N grantors and N grantees. So I have a Parties table, a Documents table, and identical join tables, Grantors and Grantees. I noticed the problem when printing out a report, and a particular document didn't have any grantees. The report uses the executeSQL query in question. So I looked for that document in the database, and the particular grantee appeared as expected in the portal. I looked in the Grantee join table, and there was a matching record, complete with the ID of the Party and the ID of the document. I then checked, and every record where that particular party appeared, in the report that party was omitted for every record where it should have appeared. In one record, that party was 1 of 6 grantees. The ExecuteSQL statement returned the other 5 parties, but not that particular one. In the course of troubleshooting, I created a new record for that party, nameFirst = Mollie, nameLast = Sternberg. Then I added the new Mollie Sternberg record to those documents. The original Mollie Sternberg record, I set the formerly empty field nameMaiden to "Bugged". Then I re-ran the report. The new Mollie Sternberg record appeared as expected, but so did the original record, Mollie Sternberg Bugged. So I don't know why changing a field in the party table for that record would cause the ExecuteSQL statement to return it, when it hadn't been returning before. I'm freaked out, because I identified a second party that was having the same issue. Changing that party's original record to nameMaiden = Bugged also caused it start showing up, too. But I don't know how many other Party records may be having the same problem, and I have no way to identify a characteristic of the Party record that may have caused the probelm, so that I can identify potential problem Party records. Is this a bug in FileMaker? Or an issue with my SQL query? Any thoughts or suggestions most appreciated. The query is below. Thank you, Tom // Find Document Grantors // Built by SQLExplorer. Compliments of SeedCode… Cheers! Let ( [ // Define Carriage Return Substitution Character ReturnSub = "\n" ; // Enable the second line here if you want the header in your results header = ""; //header = ""; // Define Table variables a@GRANTORS = Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id ) ; "::" ; ¶ ) ; 1 ) ) & " a" ; b@PARTIES = Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::_id ) ; "::" ; ¶ ) ; 1 ) ) & " b" ; // Define Field Variables acapacity = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::capacity ) ; "::" ; ¶ ) ; 2 ) ) ; a_id_parent = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id_parent ) ; "::" ; ¶ ) ; 2 ) ) ; a_id_party = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id_party ) ; "::" ; ¶ ) ; 2 ) ) ; aakaFull__c = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::akaFull__c ) ; "::" ; ¶ ) ; 2 ) ) ; ainterestConveyed = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::interestConveyed ) ; "::" ; ¶ ) ; 2 ) ) ; b_id = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::_id ) ; "::" ; ¶ ) ; 2 ) ) ; bnameDisplay = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::nameDisplay ) ; "::" ; ¶ ) ; 2 ) ) ; // Build SQL Query q = "SELECT " & bnameDisplay & " , " & aakaFull__c & " , " & acapacity & " , " & ainterestConveyed & " FROM " & a@GRANTORS & " INNER JOIN " & b@PARTIES & " ON " & a_id_party & " = " & b_id & " WHERE " & a_id_parent & " = ? " ; // Run SQL Query result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $_id_assignment ) ] ; // Clean up carriage returns List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) ) )
  7. 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.
  8. 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
  9. 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
  10. This is just about as simple as an ExecuteSQL gets, but I can't figure out why it generates a "?". ExecuteSQL ( "SELECT Count (\"voteYes\") FROM \"MotionVote\" WHERE \"motionYear\" = '2016' "; "" ; "" ) MotionVote is a TO; voteYes and motionYear are fields therein; voteYes can either be 1 or blank. The objective is to display a count of "yes" votes. What did I miss?
  11. 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
  12. I have a uniqueness criteria for a particular number field, but the uniqueness is based on a combination of the number field with a state field (e.g. the number must be unique within all records for that state). I built a custom function to return a boolean if the number+state combination already exists in the database, and that CF uses a simple SQL query, Issue is that the CF seems to work flawlessly when tested via Data Viewer, but when inserted as the field validation calculation, returns a validation failure for any value entered. Can anyone enlighten me as a to a limitation here I may not be aware of? My work around path is a field level trigger to a validation script, but do feel like I am missing something. Cheers, Juz.
  13. Hi all, Curious about ExecuteSQL this works... ExecuteSQL ( "SELECT myfield FROM myTable WHERE myTable.color = ? and myTable.style = ? and myTable.id = ?" ; "" ; "" ; "black" ; "test" ; "3" ) but this fails... when I replace myTable.color selection from "black" to empty string "" then the result fails (when there are rows with this criteria)... ExecuteSQL ( "SELECT myfield FROM myTable WHERE myTable.color = ? and myTable.style = ? and myTable.id = ?" ; "" ; "" ; "" ; "test" ; "3" ) Q: any reason it fails? is it a syntax thing? how do I search for field = "" ?
  14. 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
  15. 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()
  16. Hi, I'm attempting to return a literal out of an ExecuteSQL() statement so I can test and develop another SQL query but I cannot get it to return successfully (it reports SQL Syntax error). I used verbatim this model example from Beverly on the FMForums only changing the "myTableRef" reference to a valid TO name in my solution but no results are returned. Ultimately I want to coalesce or cast a text field to an integer so I can use the value in a "WHERE ... IN()" SQL statement expecting integers. I had no success using CAST, COALESCE or NUMVAL SQL functions doing this which lead me to this use of literals which I was hoping to use to debug my casting problem. I'm using FM Pro Advanced v14 / El Capitan. Thanks in advance for any help you can provide. Let ( [ _pre = " '$' " // change as needed ; _num = Round( 2345.017; 2) // change to actual field ref & round first ; _query = " SELECT " & If ( not IsEmpty(_pre); _pre & "||" ) & " STRVAL( COALESCE( INT( " & _num & " ) , 0 ) ) " & " || '.' " & " || SUBSTR( STRVAL( " & _num & " - INT( " & _num & " ) ) || '000', 2, 2 ) " & " FROM myTableRef " ; result = ExecuteSQL ( _query ; "" ; "" ) ]; result //"" ) // $2345.02
  17. 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
  18. I am getting a strange result, I am attempting to use the ExecuteSQL command to avoid using multiple Table Occurrences. However I am stumped when the field I need does not work on ExecuteSQL for an odd reason. ItemNo and Level are both Indexed Number Fields with the EXACT SAME SETTINGS, however when performing these commands, ItemNo gives me a result, when Level does not. They both have data inside, etc. Any thoughts or ideas why I am receiving a "?" on the second ExecuteSQL("SELECT c.ItemNo FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"") ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"")
  19. Hello All, I have a table "Staff" and a related table "Lesson_Observations". A Staff record has many Lesson_Observation records. Each lesson observation is attributed a score. I have a working ExecuteSQL calculation that finds the average of all lesson observations scores for staff in a specific Faculty ("English" in the example below) and only where the Staff record has the attribute "Active". Let ( [ $query = "SELECT AVG(L.obCritAverageVal) FROM Lesson_Observation L INNER JOIN Staff S ON L.FK_StaffCode = S.PK_StaffCode WHERE S.Fk_FacName = ? AND S.Status = ?" ; $result = ExecuteSQL ( $query ; "" ; "" ; "English"; "Active") ]; $result ) Each Lesson_Observation record has an attribute "Observation_Date". Is there a way I can modify the query above to find the average of only the MOST RECENT lesson_observation for each member of staff in a Faculty where that member is staff is still Active? Thank you! Ben
  20. Ok Ladies and gentlemen, so I’ve dipped my toes into wanting to start using the ExcuteSQL Function, I have no idea what I’m doing with it. I downloaded Seed codes SQL Explore and I am more lost than ever. I don’t understand the function and I am hoping someone can break it down for me. So something simple to start with. If I wanted to get a total value of a field in a related recorded normally I would just use Sum(TestRecord::Field) This would give me a total How the calculation above gets broke down into ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } ) Is beyond me. Can someone explain?
  21. 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?
  22. 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!
  23. Hi there I'm quite new to FM and come from a SQL background, and am struggling here a bit... I'm looking to run an ExcuteSQL command on a table which contains values for many customers. Each set of values per customer on my source table has their customer ID and in turn this is related to the customer table. I need to create a portal on the customer layout, based on the value table, using ExecuteSQL. I need to use ExecuteSQL to enable dynamic variables based on user input which will filter the results in the portal, producing average, maximum, minimum and so on. ExecuteSQL (" Select max(FTE) from Values where Type =?";"";"";GlobalFields::GF_Types ) The query above reviews ALL customer data on the value table and instead of the maximum value for the customer who's record is currently open, I get the maximum value for the table overall, which is no good to me. How do I filter this to behave like the portals usually do, displaying information relating only to the parent record being viewed? Any help gratefully received. Thanks
  24. 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
  25. 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.   Â
×
×
  • Create New...

Important Information

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