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.
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?
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.
Valuecount ( FilterValues ( Addresses 2::Address_type ; "Billing" ) )
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.
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?
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)?
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?
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?
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:
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...
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.)
"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.
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.
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 :)
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?
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.