1 2 Previous Next 21 Replies Latest reply on Sep 10, 2014 4:19 PM by dsvail

    Difficulty with SQL

    herby

      Hi,

       

      I'm a new user on day 13 of a 30 day trial version of Filemaker Pro13 trying to adapt the ready made FMP Solution "Invoices" for use as a Jobs Register. I'm having reasonable success but have hit a snag with SQL (a language with which I'm not familiar).

       

      The Dashboard in my Jobs Register file is not playing ball. The problems are:

       

      (a) The additions of jobs by Staus [type] (In Progress, Completed, Stock, No Charge, Internal) are correct provided that they were started in the current year. If a job was started in 2013 or earlier it's value is not included in the type's [Status] total. (Refer record #1 starter 30/6/13).

       

      (b) When selecting "All" records, the total field shows up as a "?".

       

      The Invoices solution template was designed to handle current month/year records however, it is necessary that our Jobs Register is capable of displaying all records since we commenced operations.

       

      Beechworth Men's Shed is a not-for-profit association run for and by old guys like myself. I've set up good working systems for job costing, membership, assets register etc. using Google Docs however they depend for maintenance on yours truly and I'm not getting any younger (I'm 79). Hence the desire to set up systems that require little maintennce and for which others can be readily trained. Filemake Pro is a great platform and seems to be the way to go. The costs of the two or three licenses is straining our resources hence the time I'm spending at present adapting Invoices as a Job register is so that I can convince my colleagues that the money will be well spent. If you can help it will be very much appreciated.

       

      The attached file "Job Regiser Ver 6.fmp12" is still a work-in-progress as you will see however, it is hoped that it will give you an insight into what I'm attempting the do.

       

      Hope to hear from someone soon.

       

      Best wishes,

       

      Herby

        • 1. Re: Difficulty with SQL
          erolst

          Have no time to look into 1. but re 2:

           

          a) Make the SQL calculation field unstored. b) the calculated BMS status in the Invoices table is type text; either make it a number, or alter the SQL IN Clause to wrap the values in single quotes.

           

          Of course, if you want to see all invoices, and create separate SQL strings depending on the Status filter's … well, status anyway, you could just drop that predicate entirely from the query.

           

          On a related note: I suggest you create a table with the order stati; this way you don't need to artificially calculate a numeric value in several places, but can simply refer to an ID (or add a field to be able to sort in a chronological order, if you need that feature).

          • 2. Re: Difficulty with SQL
            herby

            Thanks so much for taking the time to look into my problem. Much appreciated. Cheers, Herby

            • 3. Re: Difficulty with SQL
              dsvail

              snag with SQL (a language with which I'm not familiar).

               

              Herb,  looks like FileMaker in their Invoice solution was showing one way it could be done using a dashboard as a view into the data.  Since they are using a "Filtered" portal to show invoices from the dashboard it looks like it requires using "ExecuteSQL" to show totals.  You don't have to go this way especially since your are not familiar with SQL.

               

              Another way to go about it would be to create some global fields in the Dashboard Table (gStatus, gDateStart, gDateEnd for example) and create a new relationship to Invoices from Dashboard based on those globals.  Example: BMS Dashboard:gStatus = Invoices:Type (note: looking at the invoice view the field that is labeled status is actually the field "Type").  You could have those global fields set to default values on layout entry using a OnLayoutEntry ScriptTrigger. User could pull down (create a value list to do so ) on the gStatus or gDate fields to change what shows in the portal.  To get the totals create a Summary field in the Invoices Table equal to Total of Total.  You can then more easily show totals with good formatting.... ie. $400.12.   The SQL was losing the ability to format b/c it is "text".

              I would also look at the logic of "Status" they are using and make sure that it makes since in your situation (maybe more complicated than needs to be). 

               

              Taking it a step further ( to see "ALL" ..which is not a value in the Status/Type field ) I've used a find script and a Table Occurence showing the FoundSet of records. Or maybe someone has other solution to show "ALL" via a realtionship ie. another Table Occurence on Graph along with a second Tab Panel the script would navigate to if "ALL" was chosen. 

               

              Screen Shot 2014-08-28 at 11.41.44 AM.png

              Screen Shot 2014-08-28 at 11.42.00 AM.png  

              • 4. Re: Difficulty with SQL
                dsvail

                Screen Shot 2014-08-28 at 11.59.59 AM.png

                1 of 1 people found this helpful
                • 5. Re: Difficulty with SQL
                  herby

                  Hi there,

                   

                  This looks more like something I can tackle! I am most grateful to received such a detailed response and will do my best to implement your suggestions. Kind regards, Herby

                  • 6. Re: Difficulty with SQL
                    dsvail

                    I would send you the changes for you to look at,  but I don't know how to attach a file within this dialogue.

                    • 7. Re: Difficulty with SQL
                      alquimby

                      dsvail,

                       

                           After you click "Reply", click "Use advanced editor." At the bottom, there is place to Attach files.

                       

                      Al Quimby

                      • 8. Re: Difficulty with SQL
                        herby

                        Hello again,

                         

                        I’m eagerly looking forward to receiving an editable example of the solution you sent me earlier. Cheers and I’ll stay in touch,  Herby

                        • 9. Re: Difficulty with SQL
                          dsvail

                          Herby,  here is example... goes to layout which I believe is the best solution. Tried it two different ways ... one with a filtered portal ..just couldn't get the total to work when all records were showing...maybe someone else can.  The other way was with doing a find based on the global field "gStatus". Check both out and the script triggers required on the gStatus field.  Also looked like you were trying to get around the awkwardness of field "Status" being a calc ... using the field "Type" ; got rid of that and used field "NewStatus" on your Job Layout. 

                          • 10. Re: Difficulty with SQL
                            herby

                            Hi dsvail. I must thank you again for taking the time out to help a beginner like me. A bonus is that the records now display even when the date added is earlier than the current month. I wonder at why the authors of the Invoices FMP starter solution restricted displays to the current year.

                             

                            As I need the Invoices (i.e. Jobs) in the portal to appear in descending order I specified Descending in the Portal Setup - Specify Sort Portal Records however the records displayed in the portal are always in ascending order. How can I fix this.

                             

                            Cheers - Herby

                            • 11. Re: Difficulty with SQL
                              erolst

                              For your approach using the found set, you could – since this for v13 – simply use the summary field type “List of” for the primary key. Grabbing its contents after the Find would save you the costly loop through the found set.

                               

                              When summarizing related records, it's usually better to use a summary field from the child table, since you can use that in every context (native/related), while a calculation field in the parent table only works for one specific relationship.

                               

                              You also cannot use that calc field for a filtered portal, since it works via the relationship itself; displaying a summary field in a filtered portal, however, will show the correct aggregate for the filtered set.

                              1 of 1 people found this helpful
                              • 12. Re: Difficulty with SQL
                                dsvail

                                Herby, verify that you are on the layout "BMS Dashboard _Invoices (using foundset)" go into layout mode, click on the portal and click on "Specify" button on "Sort portal records"... clear whatever is in there ... choose the date field and make descending. 

                                • 13. Re: Difficulty with SQL
                                  herby

                                  dsvail - Thanks yet again. Worked ike a charm.

                                  • 14. Re: Difficulty with SQL
                                    dsvail

                                    thanks erolst,  was unaware of the "list of" summary field option in 13.  I'll play around with that as alternative to how I've always done this.....

                                    1 2 Previous Next