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

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.

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

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

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!)

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,