Jump to content
Sign in to follow this  
photoman

Making a choice

Recommended Posts

Hello,

I have three related files:

Art Invoice

Photo invoice

Invoice Report

Both Art Invoice and Photo invoice have portals to Invoice Report.

I want one field in Invoice Report to be able to display either ART or PHOTO in the record, depending on which file it came from. I've been stumbling around this seemingly simple problem for days, and can't find the answer. Any ideas?

Aaron

Share this post


Link to post
Share on other sites

From your description, I am guessing that there is a field in your 'Invoice Report' file which is used as the key field for relationships to each of the other two files (but that you expect to find a matching record in only one of those files for any one record in the 'Invoice Report' file).

If that is the case, you can create a calculating field with a formula along the lines of:

Case(IsValid(ArtInvoice::AnyMandatoryField), "ART", IsValid(PhotoInvoice::AnyMandatoryField), "PHOTO", "N/A")

...where 'AnyMandatoryField' is a field in the relevant related database which will always hold a value - eg invoice date (or any other field, but preferably one which is auto-entered when the record is created and cannot be modified).

If there is any possibility of a matching record being found in both Art and Photo databases simultaneously, it may be wise to check for this within the 'Case' statement and return an appropriate alternative result (eg "ERROR") if both target fields are valid.

Share this post


Link to post
Share on other sites

Was there some reason that "Art" or "Photo" isn't just a "Type" in a file "Invoices". A single file would make everything from searches to reports of "Total Sales" easier.

-bd

Share this post


Link to post
Share on other sites

Ray,

More explanation:

Yes, there is a field in Invoice Report that I want to be the key for both the other files, Art and Photo, respectively. I am attaching the formula you gave me to that field (called Art "or" Photo).

-- I have set up one field each in the Art file and Photo file, as a text field, Art Label and Photo Label.

-- In the Invoice Report File, I also have two Label fields, but with Auto Enter > Data > ART (a string with no quotes) and the other Data > PHOTO.

-- Back in the layouts of Art file and Photo file, I am using a relationship to the Invoice fields for each Label, and each displays fine.

-- In the Invoice Report file, the field Art "or" Photo Calculation refers to the label files in Invoice Report, not the Art or Photo file.

Needless to say, I think I have created a very un-elegant monster. Perhaps BD has a point, one file containing layouts for Art, Photo and Report might work better.

Thanks for your time,

Aaron

Share this post


Link to post
Share on other sites

Hi Aaron,

Yes it does sound as though you would be able to achieve what you want with a less complex stucture - and simpler tends to be better (or at the very least, easier!) smile.gif

Changing the structure will be a job you will want to plan for carefully, however, especially if you have a good deal of accumulated data. In the meantime, hopefully the formula approach will get you out of trouble...

Cheers,

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.