Function to determine whether a data from multiple related fields contains a particular value

[jn08]

Hi all,

I've got a contacts table which is the parent of a roles table. I'd like to put together a calculation (presumably an IF statement) that calculates whether a particular contact performs a particular role.

So let's say I have a field in the contacts layout for an author biography. I only want this to be filled out if the contact's role is an author or illustrator.

I would also use a similar calculation in other fields determining whether or not to include particular contacts in an export.

Can this be done with a calculation? Or should it be done with a find? I'm not sure how to accomplish it.

Cheers.

[comment]

While it can be done with a calculation, it probably shouldn't - else you'll need a dedicated calculation field for each type of role.

[jn08]

How would I go about doing it? A script with a series of finds and then an export? My Contacts table is related to my Products table. I'd like to run a CSV report that includes all the product information, which includes the author biography field on any related author. How would I best accomplish that?

[comment]

I'd rather not answer this until your other thread is resolved:

http://fmforums.com/forum/topic/80943-one-to-many-to-many/page__fromsearch__1

In general, when you export from a join table, you can include related fields from any of the parent tables.

[jn08]

Hah! No problem. Will figure it out when the other issue is sorted.

[jn08]

Given the changes suggested in http://fmforums.com/forum/topic/80943-one-to-many-to-many/page__fromsearch__1 how do you suggest I structure the related author biography field export?

I've had to set up the role concept in two fields to make it work. One is associated with the contact (a 'skill') and the other in the Contributions table - where each role is associated with a person and a book.

What I'd like to do is include the author bio stored in a field in the contacts database in an export of the book data where the related contact is an author. How would this be best accomplished?

[comment]

where the related contact is an author.

Assuming you mean the contact's role (not skill) is author, and assuming any contact can have content in the bio field, you will need a calculation field in the Contributions table =

Case ( Role = "Author" ; Contacts::Bio )

and export this field.

Keep in mind that CSV is a flat tabular format: all rows have the same columns. If you want to export a project and its contributors, then you must export from the Contributions table - and every row will repeat the parent project's data. Similarly, all non-author contributors will have a blank Bio cell.

[jn08]

I've restricted entry in the author bio field so only people who are authors can have content there. However, I only want the *author* associated with a book to have their bio information exported, not any of the other contributors (some of whom might be authors, just not the author of that particular book).

Is there a reason this calc field has to go in the contributions table? Could it go in the books table?

Also, thank you. Your help has been utterly amazing.

[comment]

I've restricted entry in the author bio field so only people who are authors can have content there. However, I only want the *author* associated with a book to have their bio information exported, not any of the other contributors (some of whom might be authors, just not the author of that particular book).

OK, so since we have already concluded that the author/s of this book are not necessarily categorized as "author" in the Contacts table, AND that the editor of this book could be marked as "author" in the Contacts table, we obviously must consider only the Role field in the Contributions table - and ignore completely the Categories field in the Contacts table.

Is there a reason this calc field has to go in the contributions table? Could it go in the books table?

No, it could not - because a book has many contributors.

[jn08]

There's also an issue with books with multiple authors. I've got a co-author role, but that won't help with the existing calculation. I think the best result for multiple authors would be to concatenate the author bios one after the other in the author bio export field (or another similar field activated by the presence of co-authors). At least, I think so.

[comment]

I think you need to work this out backwards: first, determine what your exported file should look like (taking into account the above limitations of a flat file). Then figure out how to build it.