1 2 3 4 Previous Next 58 Replies Latest reply on Nov 15, 2015 11:39 AM by telebube

    Connected services - service group




      I'm programming a new DB for a natural health professional. Everything works perfect, but one thing would be perfect to have.


      There are services which are "grouped". One service comes always with different others. Is there a possibility to connect several services?


      At the moment I need to enter every single service, it would be perfect to enter one key service and all connected services will be filled in automatically.


      Is there a way to do this?


      Thanks a lot.



        • 1. Re: Connected services - service group

          Hello, Tobias.


          Yes, you can certainly do this. Create a table that joins the parent service to its child services. This table should contain, at a minimum, the unique IDs of the parent service and the child service. On the layout where you create your services, drop a portal that points to this new "join" table. (It may or may not technically be a join table - defining a many-to-many relationship - depending on your data model.)


          Enter the services for each parent that should be added into the portal. You can use related fields to indicate what the child services are (so you don't have to look only at the IDs, which are probably not human-meaningful). Once that's done, you're in a position to automate the adding of the child services.


          Now, create a new relationship from whatever table you're entering the services on. That relationship should point to the parent ID in the join table from the service ID in the entry table. That way, FileMaker will be able to tell whenever you enter a service that has child services.


          At this point, you're ready with a structure to support the automation to add the new services. You can write a script that adds those services. It might look something like this:


          If [ not IsEmpty ( joinTable::childID ) ]

               Set Variable [ $childList ; List ( joinTable::childID ) ]


                    Go to Field [ service::serviceID ]   // I'm assuming you have a related table for adding services here

                    Go to Portal Row [ Last ]

                    Set Field [ service::serviceID ; GetValue ( $childList ; 1 ) ]

                    Set Variable [ $childList ; RightValues ( $childList ; ValueCount ( $childList ) - 1 ]

                    Exit Loop If [ ValueCount ( $childList ) < 1 ]

               End Loop

          End If


          So basically, what you're doing is grabbing the list of service IDs from your join table and looping over the list to create the records. But you first set up the structure to hold that list, which is where the join table comes in.


          Hope that makes sense.



          • 2. Re: Connected services - service group

            Hello Mike,


            thanks for your reply. I tried it, but I'm not sure where I have my error in reasoning. Maybe in the data model?


            If I enter the service number of the parent ID the cursor jumps to the next service field and the corresponding fields keep empty.


            I attach a few screenshots. Maybe you see the bug.


            Thank you!


            TobiasBildschirmfoto 2015-11-06 um 19.59.33.pngScreen Shot 2015-11-06 at 20.03.07.pngScreen Shot 2015-11-06 at 20.03.37.pngScreen Shot 2015-11-06 at 20.04.53.pngScreen Shot 2015-11-06 at 20.05.07.png

            • 3. Re: Connected services - service group

              Line 2 should be:


                   Set Variable [ $childList ; List ( LeistungsgruppenPositionenen::LeistungsZiffer ) ]

              Line 5 should be:

                   Go to Portal Row [ Last ]

              Line 7 should be:

                   Set Variable [ $childList ; RightValues ( $childList ; ValueCount ( $childList ) - 1 ]

              • 4. Re: Connected services - service group

                Hi Mike,


                I didn't get it running.


                Screen Shot 2015-11-07 at 14.23.39.png

                I also tried the following script, which I tried to figure out. Didn't work either…

                Screen Shot 2015-11-07 at 14.26.05.png

                I don't know what to do…


                Thanks for your support.



                • 5. Re: Connected services - service group

                  Get rid of the Select option in line 5.


                  After that, we have to look at your data model. What is it not doing?

                  • 6. Re: Connected services - service group

                    Same same, I fill in parent ID and it jumps to the next field and doesn't fill in anything. Deleting line 5 changes that the corresponding service description has been filled in, but not with corresponding services


                    It just jumps to the next field.


                    Probably the problem comes fromthe data model.


                    I fill in the services in a join (n:m) table "Rechnungsdaten" into field "Leistungs_ID Abgleichsfeld" which references to field "Leistungs_ID Abgleichsfeld" in table "Leistungen". So I get the corresponding description and price.


                    What other references do I need? I'm a little bit confused. Not a little bit, totally .


                    Thank you!

                    • 7. Re: Connected services - service group

                      Do you have FileMaker Advanced? If so, run this through the Script Debugger and see what error codes are created.

                      • 8. Re: Connected services - service group

                        Just PRO, no advanced

                        • 9. Re: Connected services - service group

                          But I'm still working with a test version, I'll purchase the advanced version immediately!

                          • 10. Re: Connected services - service group

                            Hi Mike,


                            there's only one thing wrong, the childList contains the wrong elements, three times the "LG_ID ABGLEICHSFELD". Why is this? The rest works! For now the only thing is to get the correct list values. Then everything should be perfect.


                            Can you help me?


                            Screen Shot 2015-11-07 at 22.47.31.pngScreen Shot 2015-11-07 at 22.41.38.png

                            • 11. Re: Connected services - service group

                              It means you have three copies of that item in the relationship. Which probably means your data model isn't correct.


                              I'm a bit hampered by the fact I don't speak or read German (assuming it's German?). I don't know what these various tables are, or what the relationships between them are. But basically, you need a table that connects the current parent service to all the child services you want to add. You probably don't have that right now. So you'll likely need to add it.


                              This would normally be done with a join table, which is a way of creating a many-to-many relationship in a database. It looks something like this:


                              Service >-- Group --< Service


                              On both sides, you put the Service table. In the middle, you have (at a minimum) the unique IDs of both services that you want to join together - so each record in the "group" table (for lack of a better word) represents a unique combination of services. You will have one record for each pair of services.


                              Let's say you have a service called, I don't know, "Whole Body Massage". With that service comes "Aromatherapy". You might also have a service associated with "Aromatherapy" that includes "Nutritional Counseling". (I have absolutely no idea; I'm making this up.) So your records in the Group table would look like this:


                              parentID               childID


                              101                         102

                              101                         103


                              where 101 is the ID of "Whole Body Massage", 102 is the ID of "Aromatherapy", and 103 is the ID of "Nutritional Counseling". This is the table where you draw your list from.


                              Now, if "Aromatherapy" is bundled with other services besides "Whole Body Massage", then you might also have records for it in the other direction. So it might look something like this:


                              parentID               childID


                              102                         101

                              102                         106

                              102                         109


                              where 101 is "Whole Body Massage" and 106 and 109 are other, completely different services.


                              The point being, you have to have a table that tells the database what services are "children" of the current service to establish the list of "bundled" services to add. If you don't have that, then this isn't going to work.

                              • 12. Re: Connected services - service group

                                Hi Mike,


                                yes, you're right it's German . Thank you for your explanations!


                                I understand what you mean, I didn't know that I can use one table multiple times in the data model.


                                I changed the data model the way you've described. "Leistungen" means "services" and "LeistungsgruppenPositionen" are the combined services to define a group. That's the way you mean, isn't it? Then I defined one group, service 39.1 is connected to four other services as you see in the table. So the ValueCount of the list should be 5


                                Screen Shot 2015-11-08 at 14.45.50.png

                                Screen Shot 2015-11-08 at 14.45.28.png


                                But, nothing changed. There must be another additional mistake in my data model. The value of $childList should be 39.1 39.10 39.1139.12 39.12a, isn't it?


                                Screen Shot 2015-11-08 at 14.53.34.png


                                Hard to get it right.


                                Thanks for your patience.


                                • 13. Re: Connected services - service group

                                  This script will only work if you execute it from the context of Leistungen 2. In other words, your layout has to sit on that table occurrence. That's the only way you'll get the correct list of IDs.


                                  But you have the general structure right, it looks like.

                                  • 14. Re: Connected services - service group

                                    We're getting closer. The list looks perfect, the list entries are ok, but "Set Field" command seems to work not properly.

                                    I defined an additional variable $listenEintrag to have an idea whether the values are correct. The values are perfect.

                                    Screen Shot 2015-11-08 at 21.35.56.png


                                    here the result:

                                    Screen Shot 2015-11-08 at 21.36.43.png


                                    But big problem: if I enter regular services (without any group connection) there is a total mess. I think I give up on that topic.


                                    I've no idea how to solve this thing .

                                    1 2 3 4 Previous Next