I'm just going to tackle the issue of how to flag a Distributor, but it can be used to create an ID based on any "flagged" value, usually from a radio or checkbox choice field.
First each record needs its own Primary Key field whcih is unique, a basic of any relationship structure. A second field can be named DistributorKey, and should be set up as a stored text or number field (same as the Primary Key field type), but with an auto-enter Evaluated calculation to populate it as the same as the Primary Key IF the checkbox/radio field contains (PatternCount function used) the text string "Distributor" ; otherwise it is to be empty. This field should be set to auto-enter when either the primary key field or the checkbox/radio button field is edited.
That will give you the same Distributor Key as the Primary Key if they are a Distributor, and you can use that to do the relationships andas the basis for reporting. You can even set a field for Distributor Name to behave the same way for a field from which to collect the names of just Distributors for a value list.
Thank you Stephen for your very clear description.
I'm going to try this and let you know how I get on, but I wanted to send a quick thank you for taking the time to reply.
You're quite welcome.
I have several places where I use these types of aut-enter/evalutes for Keys and Names fo building value lists and special relationships so that only records of the correct type appear in the lists and reports. By making them auto-enter/evaluates they can be stored/indexed so that the relationships work both directions, unlike unstored calcs.
I'm doing pretty good, and have come up with a structure which works. I thought I had a breakthrough understanding this morning, of how TO's can filter records to create TO's which contain a subset of records based on the defined relationship.
I'm wanting to display a VList (in Invoices) to display only those Customers who have a Customer_Key the same as the Customer_ID. The Customer_Key calc is working and displays the correct text for the chosen customers, and for the others it is blank.
I've created a TO for Contacts related to Contacts where the Contact_ID = Customer_Key
I've attempted to create the VL to "Include only related values starting from" and using the VL TO - but it comes back blank (nothing in the list - unless I "Include all value" and then it gives me everything in the Contacts Table.
Please help me. Clearly there is something that I'm not understanding?
OK, I found the answer. It took me a while, lots of experimentation, and referencing the FM Training Series (Capter 4 - Page 33).
The elements that I learnt and need to remember are around:
- using a constant global as one half of the relationship match
- making sure the fields are of the same type (eg Text)
You can create a value list of all values in the field(s) for the auto-entered special Key and special Name fields, which will list only those who have that status. You don't need and TO fitering to do that.