1 2 3 Previous Next 60 Replies Latest reply on Jul 28, 2011 4:40 AM by brian.curran

    Delivery addresses and Billing addresses

    brian.curran

      Title

      Delivery addresses and Billing addresses

      Post

      I won't bore you all (or embarass myself) with the actual details but I screwed up my database structure so I have decided to amend it.

      I currently have an Addresses table which is mainly used for customer Sites where we provide our services, I would like to add some more fields to the layout to contain a Billing address.

      Here is what I think needs to be done, your views/advice would be welcome:

      • Create the extra 'Billing' fields on the layout
      • Duplicate the Addresses TO and link them together somehow (?)
      • Create a script button that enables the user to simply copy the Site address data to the new Billing fields if it's the same address (or simply type in a different Billing address)

      Job done?

      Thanks
      Brian. 

        • 1. Re: Delivery addresses and Billing addresses
          philmodjunk

          What are your addresses records linked to and with what relationship?

          Let's say you have this relationship:

          Contacts::ContactID = Addresses::ContactID

          That enables you to have many addresses records for the same contact (or customer, or vendor, or...)

          Add a text field, AddressType, to your addresses table.

          Add it to your layout, formatted with a check box group with at least "Billing", "Shipping" as it's value list.

          Now you can either use a filtered portal (FileMaker 11 only) or a filtered relationship (older version) to display any address records with "Billing" selected in AddressType. A different filtered portal or relationship can be used to display the "Shipping" (Or all other) address records for a given contact.

          • 2. Re: Delivery addresses and Billing addresses
            brian.curran

            Hi Phil,
            The Addresses table is linked to lots of other 'child' related tables. One of the relationships enables me to add several Contacts to a customer address.

            I still have an AddressType checkbox field in the Addresses table that has "Billing" as one of its 4 values. I've just added a Portal to the Layout but had to use another TO for the related records.

            The filter is:
            Addresses 2::address_type = "Billing"

            This doesn't display any records in the Portal, which I think is due to the relationship? 

            Thanks
            Brian.

            • 3. Re: Delivery addresses and Billing addresses
              philmodjunk

              As this is a check box field, you cannot use = as your expression will not be true if more values than "billing" is selected for the address record.

              Try:

              Valuecount ( FilterValues ( Addresses 2::Address_type ; "Billing" ) )

              • 4. Re: Delivery addresses and Billing addresses
                brian.curran

                Thanks Phil, that works in that an Address is displayed in the Portal but I must be missing something as every record in the Addresses table has the Portal displaying details of the first record.

                There doesn't appear to be anything in my relationship that ties two records together.
                For example:

                kp Co Address
                1 Bloggs England
                2 Smith England
                3 Bloggs Wales

                In the above case, I would enter Bloggs England as the delivery address where we perform our services but would like to add Bloggs Wales as another address record for billing purposes.

                How do I tell FM that kp3 is the billing address for kp1?

                Thanks
                Brian. 

                • 5. Re: Delivery addresses and Billing addresses
                  brian.curran

                  I've been thinking about this a bit more, should I enter a KF field in the Addresses table and use this in my relationship to the KP field in my additional TO (Addresses2)?

                  Thanks
                  Brian. 

                  • 6. Re: Delivery addresses and Billing addresses
                    brian.curran

                    Ok, I added the KF field to the Addresses table then linked it to the Addresses2 TO:
                    Addresses::kp = Addresses2::kf 

                    I amended the Portal filter to:
                    ValueCount ( FilterValues ( Addresses2::address_type ; "Billing" ) )  and Addresses2::_kf_address_id = Addresses::_kp_address_id

                    This appears to work but I would like to know if I'm heading down the right route before going any further, is there a better way of achieving this result?

                    Thanks
                    Brian. 

                    • 7. Re: Delivery addresses and Billing addresses
                      philmodjunk

                      The second half of your expression should not be necessary as the relationship between your layout's table occurrence (addresses?) and Address2 should be based on that pair of fields. Perhaps you are using X instead of = in that relationship?

                      • 8. Re: Delivery addresses and Billing addresses
                        brian.curran

                        Thanks, I've now deleted the second half of the expression and double checked the relationship to make sure it was an = and not an x but the Portal still displays the first record from the Addresses table.

                        Here is what I have so far:

                        Addresses Table:
                        KP KF Company Address Address_Type
                        32 Bloggs England "Site"
                        173 32 Bloggs Wales "Billing"

                        Addresses::KP = Addresses2::KF

                        This is only "half working" as the Bloggs England record is the only one that displays the Portal. However, the record displayed is the first record in the Addresses table instead of Bloggs Wales?

                        How many TO's are allowed from a Table? I have Addresses2 and Addresses3 so was wondering if that might make the difference...

                        Thanks
                        Brian. 

                        • 9. Re: Delivery addresses and Billing addresses
                          philmodjunk

                          The number of TOs in your relationship are not a factor here.

                          I'm not sure from your post how you have set up values in "KP" and "KF" to link addesses records to each other here.

                          I would think you'd be linking records by a common value that links them all to the same contact either directly (One contact per set of addresses) or via Join table (More than one contact can link to a set of Address records.)

                          • 10. Re: Delivery addresses and Billing addresses
                            brian.curran

                            "I'm not sure from your post how you have set up values in "KP" and "KF" to link addesses records to each other here."
                            For testing purposes, I manually typed the Site address "KP" into the Billing address "KF" field. Is this what you meant? I thought that as one equalled the other in the Relationship then the Portal filter would match the two together but...

                            I would think you'd be linking records by a common value that links them all to the same contact either directly (One contact per set of addresses) or via Join table (More than one contact can link to a set of Address records.
                            I'm really struggling to get my head around this so I need to have another look at what I've already got. The contacts are separated out in another table and they are fine as they are. In fact, maybe they will give me a clue as to what needs to be done.

                            Thanks
                            Brian. 

                            • 11. Re: Delivery addresses and Billing addresses
                              philmodjunk

                              What I'm looking for is the logic you intend to use when linking one Address record to another. I keep finding myself thinking that this should be done from the perspecitive of a Contact record where you have a billing address displayed on one part of the layout and a shipping address displayed in another part of it. This would require either a one to many or many to many relationship between contacts and Addresses. I'm afraid that doing this as a connection from one address record and another doesn't really make sense to me.

                              Yes, since Addresses2::_kf_address_id = Addresses::_kp_address_id

                              worked in the portal filter, then

                              Addresses2::_kf_address_id = Addresses::_kp_address_id

                              should also work in the relationship. That's why I suggested this change in the first place. The fact that it didn't work for you is why I'm asking question to get a more complete picture of what you have set up and why.

                              • 12. Re: Delivery addresses and Billing addresses
                                brian.curran

                                I've just set up a Test file at http://dl.dropbox.com/u/1948142/FM_Forums/Test.fp7 and it works! I now need to set it up again in the other file and see if I can get that to work too.

                                • 13. Re: Delivery addresses and Billing addresses
                                  brian.curran

                                  Well, I double checked and triple checked the settings but it still wouldn't work so I deleted the Portal and started again. It now works perfectly but I have no idea why :)

                                  Next stage:
                                  For customers that have the same Site address and Billing address, I will simply tick the checkbox set for both the "Site" and "Billing" options.
                                  For customers that have a different Billing address, I will need to add that address to the Table (with a new KP) and also copy the original (Site) KP and automatically add it to the KF field. It would also be handy to automatically select the "Billing" checkbox for this record too...

                                  Any suggestions on how I could tackle this?

                                  Thanks
                                  Brian. 

                                  • 14. Re: Delivery addresses and Billing addresses
                                    philmodjunk

                                    That's where I need to know how you need to structure your data. I think you'll need a join table here if you have multiple contacts/customers with the same address.

                                    You'll need to tell me more about how you need this to work so that I don't suggest you right into a mess here.

                                    1 2 3 Previous Next