field with calculated value based on value list

[sahons]

Hello everyone!

I am working on a database for an artist's estate containing information about all of his artwork. There are two related files. Lets call them "artist_main" and "artist_owners". In "artist_owners" information about the artwork's current owner, like name, address etc., is stored. As there are owners who do not wish to appear in any listing or publication, I have a field called "owner privacy" that has two options to choose from: public or private (value list, radio button set).

In the future, art historians will come to the estate to look at the database. They will only be allowed to look at the "artist_main" file.

What I am trying to do now is to have a layout in my "artist_main" file showing the owner's data - but only if "public" is chosen in "artist_owners".

I've tried to do an IF calculation for the individual fields (in this case last name)

If ( provenance::owner privacy = "public" ;owner last name = provenance::owner last name ; "private" )

But unfortunately, this doesn't work...

Has anyone a suggestion?

Thanks!

[Inky Phil]

Try

Case(provenance::owner privacy = "public" ;provenance::owner last name ; "private" )

in a calc field, result text

HTH

Phil

Edited January 6, 2009 by Guest

[sahons]

Thank you for your quick reply!

I've tried your solution and it works fine with those entries that are private. The last name field in the "artist_main" field now shows 'private'.

But the public entries only show a question mark in the same field.

?

[sahons]

Oh, saw your change!

Thank you very much, now it works!

[Razumovsky]

Because you will have to repeat this calc for each of the artist_owners fields (shouldn't that be "art_owner" or are we really purchasing the artists too now...?), you might consider using the calc to display the artist_mainID in the artist_owner table and use it as the basis for the relationship. That way, there would only be a relationship from artist_main to artist_owner's who are marked public.

[sahons]

Thank you Razumovsky for your suggestion.

If I understand you correctly, with your solution, the records in the two files are only related if the owner is public. If the owner is private on the other hand, there is no relation.

But as the people at the estate who have access to all the information anyway, also to the password protected "artist_owners" file, need this connection between painting information and owner information, this is no option for me. The privacy issue only comes up with guest looking at the database.

[Razumovsky]

Yes, you understood me (although I got my tables mixed up). There are many different ways to accomplish this, and you should choose the one that works best for you. Let me suggest another though:

Since you are trying to assign a certain privilege or privilege restriction to a record based on which account is using it, why not use accounts and privileges to manage it? It will make your solution more elegant and much more secure.

Assuming you have a number field called "Private" in the artist_owner table, and that private owner records have 1 in this field while public ones have nothing, create a custom privilege set "guest" that allows limited access to the records in the artist_owner table based on the simple calculation: "not Private". Assign this privilege set to your guest user accounts, and they will not be able to view any data from records marked private. You will also not need to create separate layouts for guests and admins as you might have to with your above solution.

[sahons]

Indeed, this is a much more elegant solution! I will have a look at it.

It means some major reorganization because right now all the information with restricted access (which includes a lot more than just the owner) is in that separate file. Apart from the problem with access privileges, I was trying to avoid separate layouts...

Using access privileges with calculations would mean that I could have it all in one file. I'll give it a try :P