I'm not sure the best way to go about this. I have a Jobs table and a Jobs Pictures table, the latter of which can contain N number of pictures for a given job record. I would like to have a flag on the Job Pictures table that indicates that one picture in particular is the "Front of House" picture. Then, on the Jobs table, for report purposes, I would like to be able to have a calculated field or way to reference that particular field. What is the best way to go about this?

 

Jobs::JOB ID  <----- Job Pictures::JOB ID

 

Jobs::Front Of House Pic ----> Job Pictures::Picture (where Front of House flag = true)

 

Ideas?

 

Thanks,

Ryan

 

Hi Ryan,

 

I suggest you create a *JobPicture ID in Jobs then create a JobPictures value list based upon 'values from field' and all values.  Specify JobPictures::JobPicture ID in the left and its description in the right, showing values only from second field - all values.

 

Place your new field Jobs::JobPicture ID on your Jobs layout, attach the JobPictures value list as popup and label it 'Front Of House'.

 

* you might want to name this field 'FrontOfHouse' but by using its real field name (JobPicturesID), there is no question as to the content.

 

Oh, and the report ... since we now can make popups any style we wish (removing the ugly border it used to have), I prefer just using another popup for reports - no additional relationships or calculations needed then save the style to your theme for all reports.

 I would like to have a flag on the Job Pictures table that indicates that one picture in particular is the "Front of House" picture.

 

I am not sure I understand correctly what you're saying here. If you want to select one (and only one) picture out of all pictures of a Job to be the "Front of House" picture of that Job, then this selection is an attribute of a Job, not of the selected picture. There should not be any "flag" field in the Job Pictures table; instead, the act of selection itself should populate the Jobs::FrontOfHousePictureID field with the value of the selected picture's ID.

Hmmm.... thanks... great suggestions. I'll give that a whirl.

 

Due to synchronization issues, I've been trying to keep non-calculated fields, especially containers, off of my Jobs table. But I think I can live with an ID field.