10 Replies Latest reply on Feb 5, 2013 9:44 AM by grandflow

    Need multiple records from a FM12DB to appear on another DB's single record

    grandflow

      Title

      Need multiple records from a FM12DB to appear on another DB's single record

      Post

           Hello all,

           I need help please. I have a purchase order layout as one DB, and a seperate Customer DB for all my clients. Some of my clients have multiple locations and what to purchase and ship to all or some of them from one PO. So, my questions is, how I do grab lets say 5 records from the client DB (all from same cliient or client ID) and have them appear (somewhere ) on my PO Layout.

           Thanks.

        • 1. Re: Need multiple records from a FM12DB to appear on another DB's single record
          philmodjunk

               Define a relationship that matches to the desired set of records and use a portal on your PO layout to display them. If you are using Filemaker 11 or newer, you can further refine what related records appear with a portal filter.

          • 2. Re: Need multiple records from a FM12DB to appear on another DB's single record
            grandflow

                 Thank you. I have created a portal and relationship. All of the clients remote locations show in the portal. New problem is out of the 10 records showing as remote locations, I only need to ship to 5 of them. How can I further identify or flag these records. Can a checkbox be used in the portal for a user to select which locations to ship to and then script a find?

            • 3. Re: Need multiple records from a FM12DB to appear on another DB's single record
              philmodjunk

                   There are multiple options for that. A checkbox is one option, but may create problems for your users if you have two or more users selecting checkboxes in the portal for the same parent record (the layout's current record).

                   I can't tell from here if that is a possibility or not for your database.

                   If it is, there are other methods that use a button in the portal row to perform a script that builds a list of the selected records unique to each user on a networked database.

                   Note that you can also use a portal filter to reduce the list of records shown in the portal. If some remote locations are simply not possible shipping destinations, a filter can be used to elminate them from the list in the portal.

              • 4. Re: Need multiple records from a FM12DB to appear on another DB's single record
                grandflow

                     Thank you. 10 user system so I agree with your note about problems with parent record. All locations are viable ship to's. How would the script look for the portal row button? Filtering not viable in this process I do not think. I would love to have a user click on a button in the portal next to the record and have that record appear somewhere in the PO. But, the somewhere is not a finite field count, so if I add 5 new fields in the PO, it may not be enough.

                      

                • 5. Re: Need multiple records from a FM12DB to appear on another DB's single record
                  philmodjunk

                       Does this mean that a single PO record has to reference multiple shipping locations? That seems very unusual. How would you desginate which part of a single PO refers to each such location?

                       Can you describe what you mean by "somewhere in the PO" in more detail?

                  • 6. Re: Need multiple records from a FM12DB to appear on another DB's single record
                    grandflow

                         Sorry to be so vague. Yes, A single PO to a single vendor, that ships to clients multiple locations. Very common in our industry. The PO is a reference to the vendor, not the client. The client shipments are tracked via UPS tracking numbers that populate the PO when shipped. When I say somewhere on PO, I need to identify to the vendor, the multiple locations to ship to. I am thinking, a merge field in the Client database that makes a one line full shipto address, then script a cut and paste into a PO field(that is the somewhere). So maybe a multiline field that gets pasted into each time a record in the portal is click. But, how will the button script know which record I want, script using record row? Sorry this is so vague, but I never did this in filemaker.

                    • 7. Re: Need multiple records from a FM12DB to appear on another DB's single record
                      philmodjunk
                           

                                The PO is a reference to the vendor, not the client.

                           As it should. If it referenced the client, it'd be called in invoice. wink

                           But this doesn't answer my question.

                           If your PO specifies 300 widgets and 25 thingamabobs. How do you designate which items and how many get shipped to the first location and how many of which items get shipped to the second location and so forth... All on one PO record?

                           There should not be any need to paste any data. We should be able to simply link the PO to one or more location records--the ones selected by the user when setting up the PO.

                      • 8. Re: Need multiple records from a FM12DB to appear on another DB's single record
                        grandflow

                             Po has 300 widgets. Client always requires equal shipments to each location, which the customer service staff denotes in a memo field (so, 10 locations receive 30 each.) Yes, all in one PO record. WE do not send individual PO's to the vendor for each shipment, the order is denoted as a split ship, common in the print industry. If it was multiple vendors, then yes, multiple PO's.

                             So vendor knows to ship equal amounts to each location. So total qty  of order is divided by number of locations to ship to.

                        • 9. Re: Need multiple records from a FM12DB to appear on another DB's single record
                          philmodjunk

                               Ok, that answers the question I needed an answer to. The possibility had occurred to me that different line items in the PO might need to be designated as for specific shipping destinations so I needed a clearer idea as to how you would use this info.

                               You have PO's that list multiple locations and Locations that can appear on multiple PO's. This is known as a many to many relationship.

                               You can thus define this relationship:

                               POs----<PO_Location>------Locations

                               PO::__pkPOID = PO_Location::_fkPO_Location
                               Locations::__pkLocationID = PO_Location::_fkLocationID

                               A Portal to PO_Location on your PO layout can list each specified location for that PO. Fields from Locations can be included in the row of that portal to show the needed address and other info.

                               You can set up what looks and feels like "check boxes" but which are really buttons that either select or deselect that location for a given location by adding or deleting a related record in PO_Location. Conditional formatting can make a part of that button appear and disappear just like the X in a check box to show whether or not a given location is selected.

                               Here's a demo file that illustrates this technique. You'll find it on the Checkboxes layout and you can examine the scripts, condtional formatting and the underlying relationships to see how it matches Events to Contacts in exactly the same way you need to match locations to POs.

                          https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                          • 10. Re: Need multiple records from a FM12DB to appear on another DB's single record
                            grandflow

                                 Excellent. I will give it a go and report back. Thank you so much for the time spent.