13 Replies Latest reply on Sep 22, 2016 8:57 AM by tony@eulogist.co.uk

    Filemaker 15 How can I do what I want to do?

    tony@eulogist.co.uk

      hello everyone

      Please advise if you can. I am a long term novice with Filemaker but I normally get to work my way around most things I want/need but am now stuck. Here goes.

      I am a funeral minister delivering funeral services for which I have written a database to track all requirements for each service. I have tables such as:

      Booking details, music, poems, hymns, family, Funeral Director, crematorium etc. These are then linked with relationships and all works well with drop-down lists and I am pleased with the results.

      However, when I compile a service, both for me to deliver and also to print the Order of Service booklets given to the congregation, I have to do this virtually manually as the order for each service is different and bespoke. Some want a poem first then a hymn, some want two hymns then a poem the choices are quite mixed.

      Is there a way I can somehow pick out the various parts of a service in sequence? That is, have a list of poem1, poem2, hymn1, eulogy, poem3 and somehow tick a box or put a sequence number in a box alongside each field to insert these into a layout in the chosen order, perhaps using a script to insert?

      It seems to me that you can only sort records from the same table (I could be wrong there) which is where I am totally stuck.

      Any help would be most welcome.

      Tony

        • 1. Re: Filemaker 15 How can I do what I want to do?
          justinc

          Hey Tony,

               I think you are close to your answer already.  I would suggest a new table:  "ServiceSteps".  This new table would have fields needed for the Service cards: e.g. Title, type, person.  And it would also have the Sort field you mention.

           

          Then when you were done entering in all the Hymns, poems, speeches, whatever in your other tables, you would run a script.  This new script would go to each of those related tables (Hymns, Poems, etc) and copy (via loops) in the data from there into this new table "ServiceSteps" by creating records for each 'type'.  (Another technique would be to copy in the primary key IDs, instead of making a copy of the data, from the various tables into ServiceSteps - this would be called a 'join table' then.)

           

          For example, when copying a related Hymn record in you would copy things like this:

          Hymns::Title -> ServiceSteps::Title

          Hymns::Performer -> ServiceSteps::Person

          Set field ServiceSteps::Type = "Hymn"

           

          And if it were a poem:

          Poems::Title -> ServiceSteps::Title

          Poems::Performer -> ServiceSteps::Person

          Set field ServiceSteps::Type = "Poem"

           

          etc.

           

          Once that is all done, then you give yourself a layout that shows you all of these ServiceSteps records, including showing you the Sort field.   You would then be able to enter values into the Sort field to reorganize things easily.

           

          Be aware that in this case these are COPIES of the original data - if you changed information in the original Hymns record, for example if the person performing the Hymn changed (hopefully you haven't printed things yet!), it wouldn't update your Service Steps record.  You could build your ServiceSteps table to contain just the primary keys for the related tables, and have them dynamically update.  Or your script could just wipe out old steps and recreate all the ServiceSteps records again.  The benefit to making a copy of things is that you could have one 'Title' field for all 'Types': Hymns, Poems, Songs, etc.  If you went with a Join-table structure, you would have to overlay various fields from different tables on the layout in order to see the different things, etc.  It's been my experience that these steps aren't all that numerous, so recreating things wouldn't be a burden.

           

          --  Justin

          • 2. Re: Filemaker 15 How can I do what I want to do?
            tony@eulogist.co.uk

            Wow - thanks a million Justin, at least I know it can be done although I may need a while to study your response in detail in order to implement your suggestions. I will play with this straight away but I may come back if I get stuck with the copying of records from one field to another.

            Very helpful indeed Justin...

            Tony

            • 3. Re: Filemaker 15 How can I do what I want to do?
              justinc

              Just let me know if you have questions.  I'll be happy to explain further.

              • 4. Re: Filemaker 15 How can I do what I want to do?
                philmodjunk

                If you are using a lot of the same steps over and over in a kind of "mix and match" process, you don't have to copy records, just link to them via a "join table" and then sort the records in the join table into a list to produce your order of service.

                 

                Service-----<OrderOfService>-----ServiceParts

                 

                Service::__pkServiceID = OrderOfService::_fkServiceID

                ServiceParts::__pkServicePartID = OrderOfService::_fkServicePartID

                 

                A portal to OrderOfService on a Service layout can be used to list and create the serviceParts that make up that particular service. Fields from Serviceparts can be placed in the portal row of that portal. A number field in OrderOfService can be used to set the order of these parts. _fkserviceID can be set up with a value list for selecting an existing service part. A button placed near the portal could be used to create new service part records unique to the particular service and once added to ServiceParts, they become available for use in future services.

                 

                And for better printing purposes, you can pull up a found set of OrderOfService records on an OrderOfService layout based on that same table with fields from Service and ServiceParts included as needed to produce the desired document.

                • 5. Re: Filemaker 15 How can I do what I want to do?
                  tony@eulogist.co.uk

                  Hi Justin

                  Thought I might need some further help - and I do. I cannot work out how to script to copy the data from one field in a table to another field in another table. I thought I would try this first before I look at the 'join table' idea mentioned by you and philmfdjunk. If you could guide me with the script to copy one field I can do the rest.

                  Table is :Funeral_bookings

                  Field is : Music_title

                                 Music Artist

                                 Music length

                  Other Table is Service_sequence - with same field names

                  Field is : Music_title

                                 Music Artist

                                 Music length

                   

                  Thanks

                  Tony

                  • 6. Re: Filemaker 15 How can I do what I want to do?
                    David Moyer

                    Hi,

                    are you familiar with passing multiple script parameters?  You could write a "sub-script" that:

                    - accepts your scant three parameters as a ¶-separated list

                    - goes to the other layout

                    - creates a new record

                    - populates the three fields

                    - goes to the original layout

                    Example:

                    Perform Script [myScript; Parameter: List("A"; "B"; "C")]

                    Then, in the sub-script:

                    Set Variable $list = Get(ScriptParameter)

                    Then,

                     

                    $param1 = GetValue($list; 1)

                     

                    $param2 = GetValue($list; 2)

                     

                    $param3 = GetValue($list; 3)

                    • 7. Re: Filemaker 15 How can I do what I want to do?
                      tony@eulogist.co.uk

                      David

                      you are so kind - thank you but this is right over my head. I really do not understand this, all suggestions need to be idiot proof!

                      Tony

                      • 8. Re: Filemaker 15 How can I do what I want to do?
                        justinc

                        There are a variety of ways to do this.  The one I might recommend for you, though, is done all through a script.  This script would go to all of the related records and collect the data you need into a couple of array variables, and then go to the "ServiceSteps" table and write the data out.  So you aren't exactly copying directly from one field to another field - you use the script's local variables as an interim stage.

                         

                        I will assume that Funeral_Bookings is the central table, and that Poems, Songs, Hymns, etc are all related to a single record in Funeral_Bookings, with a relationship like: 

                        * Funeral_Booking::ID = Poems::FuneralBookingID_fk

                        * Funeral_Booking::ID = Songs::FuneralBookingID_fk

                        etc...

                         

                        The script would look something like this ( the starting context is "Funeral_Booking" table/layout; "GTRR" = "Go To Related Record"):

                         

                        ----------------------------------

                        # PART 1:  READ the data from various related tables

                        set variable [ $FuneralID ; value: Funeral_Booking::ID ]

                        GTRR [ From table: "Poems" ; layout: "SomeLayoutBasedOnPoems" ]

                        Go To Record [first]

                        set variable [ $i = 1 ]

                        loop

                        Set variable [$Poems [ $i ] ; Poems::Title & ";" & Poems::Performer & ";" & Poems::Length]

                        go to record [next ; exit after last ]

                        Set variable [$i ; $i + 1 ]

                        end loop

                         

                        go to layout [original layout]

                        GTRR [ From table: "Songs" ; layout: "SomeLayoutBasedOnSongs" ]

                        Go To Record [first]

                        set variable [ $i = 1 ]

                        loop

                        Set variable [$Songs [ $i ] ; Songs::Title & ";" & Songs::Performer & ";" & Songs::Length]

                        go to record [next ; exit after last ]

                        Set variable [$i ; $i + 1 ]

                        end loop

                         

                        # Etc.  Repeat these steps for as many tables as you need to read 'data' FROM

                         

                         

                        # PART 2: When you are done READING data, time to write the data to "Service_Sequence"

                        Go To layout [ "Service Sequence Layout"; table: "Service_Sequence"]

                         

                        # Create POEM records

                        set variable [ $i = 1 ]

                        Loop

                        # Create the new 'Service_Sequence' record, and set the FuneralID

                        new record

                        set field [ FuneralBookingID_fk ; $FuneralID ]

                        # Unwrap the data stored in the first half of the script:  extract a single 'Poem' record,

                        #    then split the individual fields onto separate lines

                        set variable [ $Current ; substitute ( getvalue ( $Poems ; $i ) ; ";" ; "¶" )

                        # Now save that data to separate fields

                        set field [ Service_Sequence::Title ; value: getvalue ( $Current ; 1 )

                        set field [ Service_Sequence::Artist ; value: getvalue ( $Current ; 2 )

                        set field [ Service_Sequence::Length ; value: getvalue ( $Current ; 3 )

                        end loop

                         

                        # Create SONG records

                        set variable [ $i = 1 ]

                        Loop

                        # Create the new 'Service_Sequence' record, and set the FuneralID so that it relates correctly back to the parent

                        new record

                        set field [ FuneralBookingID_fk ; $FuneralID ]

                        # Unwrap the data stored in the first half of the script:  extract a single 'Song' record's data from the variable

                        #  then split the individual fields onto separate lines

                        set variable [ $Current ; substitute ( getvalue ( $Songs ; $i ) ; ";" ; "¶" )

                        # Now save that data to separate fields

                        set field [ Service_Sequence::Title ; value: getvalue ( $Current ; 1 )

                        set field [ Service_Sequence::Artist ; value: getvalue ( $Current ; 2 )

                        set field [ Service_Sequence::Length ; value: getvalue ( $Current ; 3 )

                        end loop

                         

                        #  ETC...   one loop for each loop you defined in the first portion of the script.

                         

                        go to layout [original layout]

                        GTRR [ from: "Service Sequence" ; layout: "Service Sorting" ]

                        ----------------------------------

                         

                        Now, I probably forgot some important step or another.  And this is just one method.  This could all be done with ESQL calls; or in a single variable; or with relationships; Selector-Connector; etc.  I would probably lean towards reading in the data via ESQL calls, creating one large array (instead of separate arrays for each category); this would make things much more compact and with less jumping around to other layouts.

                         

                        Once all that is done, this would ideally put you on a layout showing you all the records you just created, and that allowed you to enter in a 'Sort' value for each record.  This way you can rearrange them as you see fit.

                         

                        --  Justin

                        • 9. Re: Filemaker 15 How can I do what I want to do?
                          David Moyer

                          to be slightly less vague, let me add these details ...

                          - a "sub-script" is nothing more than a script that is "called" by one or many other script(s).

                          - Typically, when you create a new record like this, you'll capture the new primary key generated.  You can return this new key to the calling script via Exit Script.

                          - the complement of sending a script parameter and using Get(ScriptParameter) is returning a script parameter to the calling script via Exit Script[result: parameter] and using Get(ScriptResult) in the calling script.

                          (sorry - it's not easy to automate, but once it works ...)

                          • 10. Re: Filemaker 15 How can I do what I want to do?
                            philmodjunk

                            With regards to the two different approaches suggested here...

                             

                            If you rarely "reuse" a service part, making a new record each time for each part of your service is simplest. If you have many service parts that you frequently use when setting up a service, I'd go with a system of linking to them instead of making duplicate copies of the same information over and over for each new service.

                             

                            Likewise, if you have basic parts that are then frequently "customized" to add additional details, copying the record (or at least the record's data) makes more sense.

                            • 11. Re: Filemaker 15 How can I do what I want to do?
                              tony@eulogist.co.uk

                              Thank you so much Justine and the other contributors, this really is an excellent community - I am grateful to you all. I am off for a few days training new ministers but I will work on this whilst away and hope for the best. I will keep you informed.

                              One last question please - This is probably not the place for it but as I have some excellent responses from some obviously wonderful helpful people, here goes. My colleague ministers would love to use my database too (when finished). I upgraded to Filemaker 15 so I could publish it on the web. Can I set this up so each minister could have their own access (username and password) and yet remain private?

                              Thanks again for all your kindness.

                              Tony

                              • 12. Re: Filemaker 15 How can I do what I want to do?
                                justinc

                                You're welcome for the assistance.  It's this kind of community effort that helped me out a lot when I was new at all this, too.

                                 

                                The short answer to your question:  Yes, it can be done.

                                • 13. Re: Filemaker 15 How can I do what I want to do?
                                  tony@eulogist.co.uk

                                  Great, a rough idea of cost and timescale?

                                  Tony