how to display particular related record

[julesw]

hi all,

am trying to do something that should be really simple.

ive got a database for a small publication, to keep track of all our clients and what editorial/advertising they have had with us.

i have got 3 related tables:

company info (addresses etc)

people

issue content (each record is an article or advertisement)

i have a layout called booking form, which displays current/future ad bookings.

on this layout, i also need to display a particular record from the people table.

the people table has a field called 'billing contact', which is basically a checkbox. the user specifies which person in the organisation is the contact for booking ads, and i want this person and their contact details to display on the booking form.

have tried to do it in a number of ways.

first i tried to create calc fields in the company table: Case(billing people::billing contact = "billing"; billing people::firstname)

at first this appeared to work, but then i realised that if there were several people related to a given company, it would only display the billing contact's name if they were the first in the list.

i thought this might be to do with it being a calculation field, so tried changing it to a text field which auto-enters (the same) calculated result, but that didnt seem to work.

then i thought maybe i should be doing it via a relationship, where there is a field in the company table that has a constant value "billing", and the relationship finds the record with the matching company ID that has billing contact selected in the people table.

i tried to do this by setting up a global field in the company table, but this didn't work, and looking back over past issues, i remembered that you cannot use global fields to do this.. correct? so tried to set up the field to auto-enter the word 'billing' and put this data in all existing records. it still doesnt work.

i tried putting the billing contact person fields on the layout both within a portal and without. it didn

[julesw]

okay, i seem to have got it working now. i created a new layout, using the relationship for the billing contact. the other portal which links to the issue content table still seems to work. i dont exactly understand why this is, but i am happy it is working!

another question though.. is there an easy way i can make it impossible for more than one contact in a company can be listed as the billing contact? i am guessing i would need to do this somehow with a self-join, but i dont know where to go from there. it is sort of like wanting to use the 'is unique' field validation, but only comparing with values in records with the same identifier. i figure something like this should be possible, can anyone suggest how? unfortunately there is no function for 'is unique'.

j

[julesw]

actually, on second thoughts i dont think the other portal (linked to issue content) is now displaying correctly. previously it was only showing current and future bookings, but now it is displaying past bookings too... arggghhh!

can anyone help? please.......!

[comment]

The attached demo shows two methods to set a "main" related record, either by picking one from a pop-up menu, or by clicking on one in the portal.

setMainContact.fp7.zip