10 Replies Latest reply on Jul 23, 2012 2:35 PM by TimWalker

    Avoiding COPY PASTE

    TimWalker

      Title

      Avoiding COPY PASTE

      Post



      I'm a novice with a challange.
      After using FM Go I found a script using COPY PASTE (worked fine for years) which copied a Trailing Grand Summary field from Invoices Table and PASTED to Contacts Table did not work. My script basically finds all Invoices with a balance due > 0 then totals all found records. Copies the result and Pastes to Contacts table field. I have tried to use a relationship but the field I want to use is grayed out.
      Is there any way to do this without COPY PASTE?

        • 1. Re: Avoiding COPY PASTE
          philmodjunk

          Copy and Paste script steps should be avoided whenever possible. There are better alternatives that can be used 99% of the time. Copy destroys any data the user has copied to the clipboard so running a script and having your copied data be mysteriously replaced by the data copied in the script can both annoy and confuse users. Boty copy and paste will silently fail to work if the field they reference is not present on the current layout when they are performed.

          The general replacement for copy/paste is Set Variable/Set Field. You use set variable to "copy" the data into a variable and set field to "paste" the data from the variable into the target field.

          • 2. Re: Avoiding COPY PASTE
            TimWalker

            It looks like your answer uses "copy "paste"

            Is it using the OS?

            • 3. Re: Avoiding COPY PASTE
              philmodjunk

              This is a method that widely used in FileMaker solutions by a very many developers.

              My answer does not use copy nor paste. It uses set field and set variable. I referred to "copy" and "paste" only as a way to match the two steps to the copy and paste steps that they replace.

              In a script, it would look like this:

              Set variable [$VariableName ; YourTable::FieldToCopyFromHere ]
              Set Field [YourTable::FieldToPasteIntoHere ; $VariableName ]

              Often, YourTable::FieldToCopyFromHere and YourTable::FieldToPasteIntoHere are fields in different tables and often there is a go to layout step in between these two steps.

              • 4. Re: Avoiding COPY PASTE
                TimWalker

                Worked on you script idea with no results. I obviously posted the wrong question from the start, sorry.

                All I really wanted to accomplish is a field that will display a total of "Invoices with a balance due >0

                AND have the result in another table.

                • 5. Re: Avoiding COPY PASTE
                  philmodjunk

                  That does not require any script at all to do. Just the correct relationship and other layout design details.

                  Let's start over.

                  What do you have and what do you want?

                  Describe your tables, layouts and relationships that you have as well as the result that you want.

                  Example: A typical Invoicing system is build around these basic tables and relationships (your names may vary):

                  Customers----<Invoices----<LineItems>------Products

                  Customers::CustomerID = Invoices::CustomerID
                  Invoices::InvoiceID = LineItems::InvoiceID
                  Products::ProductID = LIneItems::ProductID

                  Then additional tables may be added to manage payments as you may have cases where a payment does not exactly match the total due for a single invoice and this can get a bit complicated--but it's important for me to know how you determine the "balance due" before I can suggest how to get what you want.

                  Also, the table on which your layout is based where you want this will be of key importance.

                  • 6. Re: Avoiding COPY PASTE
                    TimWalker

                    Here are my tables

                     

                    Contacts::Contact Code = Invoices::Contact Code
                    Invoices::Company Code = Contacts::Contact Code
                    Contacts::Contact Code = Estimates::Company Code

                     

                    "balance Due" = Order Total - Amount Paid - Amount Paid 2

                    Occasionally someone overpays (rare) therefore a -number results.

                    I am only looking for positive numbers of "balance due" to be totaled and the result to be used in the "Contacts" table.

                    • 7. Re: Avoiding COPY PASTE
                      philmodjunk

                      Define a new calculation field, cBalanceDueGT0 in Invoices:

                      Max ( Balance Due ; 0 )

                      This will keep the negative values due to over payment from affecting the total unpaid balance.

                      Then you have two choices for getting the same total:

                      1) Define a summary field in Invoices that computes the Total Of cBalanceDueGT0 . Put the summary field from Invoices on your contacts layout.

                      2) Define a calculation field in contacts as : Sum ( INvoices::cBalanceDueGT0 ). Put that calculation field on your contacts layout.

                      • 8. Re: Avoiding COPY PASTE
                        TimWalker

                        Here are my results for both choices.

                        1) Define a summary field in Invoices that computes the Total Of cBalanceDueGT0 . Put the summary field from Invoices on your contacts layout.

                        Works great in Invoice layout, shows total of "Balance due on every record. But putting the summary field from Invoices to Contacts layout shows

                        total of all invoices for that contact Code only.

                        2) Define a calculation field in contacts as : Sum ( INvoices::cBalanceDueGT0 ). Put that calculation field on your contacts layout.

                        Same as above result

                        • 9. Re: Avoiding COPY PASTE
                          philmodjunk

                          Yep. That was the result I undestood was what you wanted. I gather you wanted a total for all the invoices--not just those for the current contact?

                          If so, define a new relationship to a new occurrence of invoices like this:

                          Contacts::anyfield X AllInvoices::anyfield

                          In Manage | Database | relationships, make a new table occurrence of Invoices by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be AllInvoices.

                          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                          You can double click the relationship line between the two occurrence boxes to open a dialog box where you can change the operator from = to X.

                          Then add your summary field from AllInvoices instead of invoices or define the sum function to refer to AllInvoices::cBalanceDueGTO instead of invoices.

                          • 10. Re: Avoiding COPY PASTE
                            TimWalker

                            It WORKS, another FM mystery SOLVED!

                            Thank you Phil!!!

                            I had been working on this problem for a few days.