1 2 Previous Next 24 Replies Latest reply on Jun 10, 2016 3:22 AM by ChristianNissen

    What table for "New Record/Request"?

    ChristianNissen

      FM´s lack of rationality and clear documentation drives me mad.

       

      Why on earth is there NOT a variable to define WHAT table I would like to make a new blank post/record/row in?

       

      It seems - correct me if I am wrong (because the documentation does not say) - that the table in question is the table selected in the layout I am operating when doing a "New Record/Request" script step?

       

      However that layout takes data from three different tables and shows it in the layout...

       

      Debits, projects and invoices are the tables. They are joined with debits::projectnumber = projects::projectnumber and debits::invoicenumber = invoices::invoicenumber

       

      Projects is the choosen table for the view. I start with projects that has outstanding not invoiced debit posts. In the table view I can see a list of projects with debits that is not invoiced. I choose one project and change view to forms.

       

      All data is collected from debits of the choosen project and the project it self. The form is filled and the invoicedata (like sums) are calculated, only the invoice number is left blank at this stage.

       

      When the data is checked, and I am ready to take out an autoincremented  invoicenumber, (this serialnumber must be a continous numberseries according to law, with no numbers missing, even if I decide to finish the invoice at a later time) there is a button in a bottom pane that activates a script, that should:

      1/ Make a blank record/post/row in the invoices table

      2/ Set the invoices::invoicenumber to the next serial increment (the field is numeric and autoincrement in the database)

      3/ Read that number into a variable: $invoicenr = invoices::invoicenumber

      4/ Take that variable and read it into all the debitposts in this selection: debits::invoicenumber = $invoicenunr

      5/ Set various other fields of the new invoices record (like from the calculated sums), which I skip in this discussion

      6/ Save the new invoices record

      7/ Save all the debits records (can be from one to maybe twenty) with the changed value of debits:invoicenumber

      8/ Show the now finalized invoice (with the incremented correct invoicenumber) ready to be printed out.

       

      I fail already at point 1/ with the new record in invoices...

      Trying to fool FM with: 

       

      1 - Set Field [invoices::invoicenumber]  *something needs to be pointing away from projects table to the invoice table? Right? Perhaps something else should be used?

      2 - New Record/Request   *seems to be making a new projects record, NOT a new invoices...how much better it would be with "New record [invoices]"? end of doubts....

      3- Set Next Serial Value [invoices::invoicenumber]   * doesn´t matter if I use this row or not, or rely only on the database parameter "serial incerment" which by the way is set to "Autoincrement on creation of record".

       

      I use the "debug" and "dataviewer" to check the progress of invoices::invoice (and other data)

       

      After step 1 its the last accessed invoice post for the selected project in this case invoices::invocenumber is 10006 (numerical)

      After step 2 its NULL

      After step 3 its still NULL

       

      I have no clue....?

        • 1. Re: What table for "New Record/Request"?
          dtcgnet

          You've got multiple issues. So...I'll tackle one.

           

          "1/ Make a blank record/post/row in the invoices table

          2/ Set the invoices::invoicenumber to the next serial increment (the field is numeric and autoincrement in the database)"

           

          Use an Auto-Enter calculation to set the InvoiceNumber. The instant you create a new record, InvoiceNumber would automatically enter the next serial number. You don't need to do it in a script.

           

          In Manage Database, select that field. Click Options. The Auto-enter tab will be frontmost. Click the Serial number option and set the value.

           

          Start there.

          • 2. Re: What table for "New Record/Request"?
            ChristianNissen

            Thanks for the effort!

             

            But I cant really tell if it works, as the New Record/Request doesn´t make a new record in invoices table, but in the projects table instead...

             

            But it does work, if I use a layout with invoices as the main table. But that is not the way users wants to work, they need debits and projects and when satisfied with them they want to make a new invoices record and takle out a serial number.

            • 3. Re: What table for "New Record/Request"?
              coherentkris

              You wrote

              "It seems - correct me if I am wrong (because the documentation does not say) - that the table in question is the table selected in the layout I am operating when doing a "New Record/Request" script step?"

               

              This is correct.

              A layout is based on a single table occurence (TO) from the relationships graph.

              Each TO is bound to a table.

              Portals leverage the relationships graph from the TO of the layout.

              Executing any records type script step ( new, duplicate, delete, et. al) will affect the table that is bound to the TO of the layout.

               

              To affect records NOT in the current context you either have to go to a layout based with the correct context OR use the magic key method of creating related records without changing context.

              record creation | A Filemaker Miscellany

               

              Everything in FM is tied to context.

              This is just the way it is.

              Once you grok that you'll be able to fly.

              • 4. Re: What table for "New Record/Request"?
                dtcgnet

                If the layout is based on the Projects table, then you are correct, New Record/Request will create a new record in the Projects table.

                 

                You are most likely looking at a project, and viewing a portal that shows invoices for that project.

                 

                There are two common methods for this.

                 

                Method 1 involves going to the relationships graph and clicking the box that says "Allow creation of records in this table via this relationship" for the table in question. Then your script would go to the portal, go to the last portal row, and enter data. A new record (with the proper serial number) would automatically be created. This method has its strengths and weaknesses.

                 

                Method 2 involves setting variables for the layout you're on, the fields you'd like to incorporate in the other table (such as grabbing the primary key so you can add it as a foreign key to the other table). Then your script jumps to a layout based on the other table, creates a new record, and returns to the original layout.

                • 5. Re: What table for "New Record/Request"?
                  dtcgnet

                  Also...the FileMaker Training Series is available as a free download, and is very helpful.

                   

                  Database Skills, FileMaker Pro Training | FileMaker

                  • 6. Re: What table for "New Record/Request"?
                    beverly

                    FTS:Basic is free (to all), FTS:Advanced is free to FDS members, otherwise is not free.

                    • 7. Re: What table for "New Record/Request"?
                      richardsrussell

                      If you are on a layout associated with the Projects table when you issue a "New Record/Request" command, you will get a new Projects record.

                       

                      If you are on a layout associated with the Invoices table when you issue a "New Record/Request" command, you will get a new Invoices record.

                       

                      Think of it as following somebody's directions on how to get to 5th Avenue. Instructions that make perfect sense when you're in New York will be absolutely useless if you're in Chicago. The "New Record/Request" command (and many others as well) is like that: context-specific. Depends on where you are when you use it.

                       

                      If your script is activated while you're on a Projects-table layout, but you want to create new Invoices records, use a "Freeze Window" step in the script, "Go to Layout [Invoices]", create your new records, then "Go to Layout [original]".

                       

                      It's entirely possible that your new Invoices records will need some data from the Projects record you started in. If so, save that data before leaving the Projects layout, either in global fields accessible by both Projects and Invoices or in variables (of types $local and $$global) defined right within the script itself.

                      • 8. Re: What table for "New Record/Request"?
                        ChristianNissen

                        Thanks for the tips!

                         

                        So I was right - even if the reference documentation on script commands says nothing about it - its the one table You reference to as Your main table in the layout setup, that can have a new record through "New Record/Request", not any others!

                         

                        Of course I am thankful for all the tips of workarounds of shortcomings in FM. Thank You very much!

                         

                         

                        However I get more and more annoyed over how FM is managing and developing its product.

                         

                        But wouldn´t it be so much better if FM was done properly? Instead of all these workarounds? Last week it was trying to select records from debits where invoicenr = 0 (which I set them to, as new records are made, marking them as not invoiced yet and being the step before the issue in this thread). But the "Perform Find [debits:invoicenr = 0]" returned no posts. After several days another little stupidity of FM reveals itself. The numberfield wasn´t really populated with 0, however 0 was entered and saved in the field, but with something like 0,000000000000000000000001 or equvivalent, thus  debits:invoicenr < 1 must be used to find the posts with 0!, in a product that has been maturing like 30 years! Give me a break!

                         

                        What RDB´s would chain You like this in its programming language? DOS-RDB´s from the 80´s did this better, not to mention modern followers. And how hard would it be to have "New Record [table]".

                         

                        FM is full absolutly full of quirks and shortcomings. My list today have 5-6 serious ones and the list is growing, and nobody seems to bother... are You swedes or anything? (I am a swede regardless of being a rebel).

                         

                        FM has its strenghts in crossplatforms IOS/Windows/Android and really nothing else.

                        • 9. Re: What table for "New Record/Request"?
                          richardsrussell

                          If you go to the program's "Help" menu, select "FileMaker Pro Help", and do a search for "New Record/Request", you'll get a window that ends like this:

                           

                          Send Feedback.jpeg

                           

                          While the Example suggests that you need to go to an appropriate layout ("Invoice Details") before issuing the "New Record/Request" command, you are correct that the explicit Description does not. That's why every page in FileMaker Help contains the "Send feedback" option at the bottom. This looks like a good opportunity for you to use it.

                          • 10. Re: What table for "New Record/Request"?
                            coherentkris

                            What you call workarounds are just the learning curve of getting used to how the product actually works.

                             

                            FM does not claim to be the same as other RDBMS/development platforms that you may have gotten comfortable with.

                            • 11. Re: What table for "New Record/Request"?
                              BruceRobertson

                              Well; in the same sense that a globe or a map is a problematic workaround.

                               

                              You might try learning the tool.

                              • 12. Re: What table for "New Record/Request"?
                                ChristianNissen

                                To be forced to use workarounds rather than fixing problems and shortcomings after 30 years of evolution IS a problem.

                                 

                                The only way of learning these workarounds seems to be to turn to the (helpful) side of this community. As FM´s referencemanuals are so thin.

                                 

                                So Your tip is more or less worthless Bruce.

                                 

                                FM´s people seems totally uninterested. Except for their salesperson in Lund in Sweden who is fighting an uphillbattle. Thanks for Your support Emil.

                                 

                                Again thank You very much to all You nice people that tries to be helpful to cover for FM´s shortcomings! Only very good things to say about You all! Not so good to say about FM.

                                • 13. Re: What table for "New Record/Request"?
                                  siplus

                                  So assuming that you'd have a New Record(TableName), regardless of context, what would the next step in your script look alike ?

                                  • 14. Re: What table for "New Record/Request"?
                                    BruceRobertson

                                    Your statements are quite odd.

                                    A number field that is empty ; is empty.

                                    Records holding this value can be easily found.

                                    A number field that has had a zero entered into it; does in fact retain the simple value of zero.

                                    And again; records holding this value can be easily found.

                                    1 2 Previous Next