8 Replies Latest reply on Feb 25, 2011 7:47 AM by mgores

    copying fields from related records



      copying fields from related records


      I am writing a script that creates a proposal.  In some instances the proposal can have multiple line items.  I set up the line items as a separate table with fields for qty, item and price. (and a QuoteID for the relationship)

      My script for the proposal is a series of insert text or insert calculated result steps that writes the proposal in a business letter type format into a final text field.  I am trying to figure out how to get it to list the related line items.

      I would like it to look like this


      Insert calc result   "The cost for (Quote::testing) of (Quote:qty) ( Quote:item) will be (Quote:price)" //repeat for however many line items there are.

      End loop

      Would this work?

      Go to portal row - next, exit after last 

      Insert my calculated text for line items like above

      Insert my trailing text and signature stuff.

      Or would I need to have a Goto portal row-first;  then a Go to portal row -next, exit after last

        • 1. Re: copying fields from related records

          Why are you needing to do this? Will this be text in the body of an email?

          In each line item record of your table, you can define a calculation field to return text as:

          "The cost for" & LineItem::Testing & " of " & LineItem::qty & " will be " & LineItem::price

          No need to loop through the records with a script. I'm using LineItem as the table occurence name as you describe looping through line item records for your quote. Don't really see why the table occurrence name in your example woule refer to the quote table instead of the line item table. If it's based on the quote table, why do you need to loop through a set of quote records? Either way, the basic syntax is the same.

          Note: if you do need this text in the body of an email--the only reason for doing this that I can think of--there are at least two ways to pull all the text from such a calculation field in a set of records into the body of an email.

          1) With the right relationship, List ( LineItems::MessageField ) will produce a return separated list of this calculation field.

          2) Bring up these records on a list layout that has no other fields on it. Copy All Records will then copy all the records in the current found set into the clip board where you can then paste the text into a text field that can be referenced in the send Mail step as part of the expression building the email message.

          Either way, no looping needed.

          But I not at all confident that I am understanding correctly what it is that you want to accomplish here as your end result.

          • 2. Re: copying fields from related records

            The script writes out the text into what I called a FinalMessageText field that allowed the user to look it over and edit if necessary before printing it as a pdf and sending the email.

            The format of the text before and after the list of line items is slightly different for each user and I have scripted in those options fine.  The problem is getting the lines from line items table to insert in between.  Most quotes will only have 1 line item, but it is possible that there could be up to 10 maybe more.

            • 3. Re: copying fields from related records

              Hmm, then you might be able to use:

              Set Field [quotes::FinalMessageText ; List ( LineItems::MessageField ) ]

              Where MessageField is the calculation field I described earlier. It requires a relationship from Quotes to LineItems that matches only to the records you want but it will work.

              To include additional text in your field, you can add elements to the expression used in that set field step or you can use a series of set field steps that append data to the existing text in the field.

              Set Field [quotes::FinalMessageText ; List ( LineItems::MessageField ) & "¶¶" & "Additional Text here" ]


              Set Field [quotes::FinalMessageText ; List ( LineItems::MessageField ) ]
              Set Field [quotes::FinalMessageText ; quotes::FinalMessageText  & "¶¶" & "Additional Text here" ]

              ¶ is the symbol you can use in a FileMaker text expression for a carriage return.

              • 4. Re: copying fields from related records

                That's it, List(LineItems:message)  is the command I was looking for.  I just defined that field the way you described earlier.  Now I just entered

                <All the previous stuff>

                InsertCalculatedResult (Quotes:message, List(LineItems:message)

                <All the ending stuff>

                and it is working as hoped for.  The idea of it is to give the user one text field that they can edit freely as desired before clicking a button that prints it as a pdf and either emails it or prints out a paper copy for faxing.  (either way a pdf is created, saved to a folder on the server, and inserted as reference in a container field so that it can be opened by anyone that needs to see it at a later date).

                • 5. Re: copying fields from related records

                  I don't recommend using InsertcalculatedResult for this. It's a bit more fragile than using the set Field step--which does not require that the field be present in order for the step to work correctly the way InsertCaculatedResult does.

                  Just my preference here...

                  • 6. Re: copying fields from related records

                    I would use Set Field instead, but won't that clear everything I had written to the Quotes:message  field before that step.  There are several script steps prior to and after the LineItem list that insert calculated results, field data, or conditional (i.e. if dept=x insert blurb1, if dept=y insert blurb2, etc)

                    I know it is probably more complicated than most solutions, but wriiting the entire text of the quote to a single editable text field seems to be the only way to let the users preview and change the text prior to commiting it to a pdf and locking (psuedo-locking actually) the record.

                    • 7. Re: copying fields from related records

                      Not if you are careful to set up the set field calculations correctly.

                      Review this example I gave earlier:

                      Set Field [quotes::FinalMessageText ; List ( LineItems::MessageField ) ]
                      Set Field [quotes::FinalMessageText ; quotes::FinalMessageText  & "¶¶" & "Additional Text here" ]

                      The second line of text appends "¶¶" & "Additional Text here" to the text already entered into this field in the previous set field step.

                      • 8. Re: copying fields from related records

                        Thanks Phil.  That trick will help clean up my script as well.  I originally started it not knowing much at all and it is a pretty hairy looking series of insert this, insert thats.