Hi all,

I'm trying to put together a database for a art show and charity auction held at a convention. I thought it was going well, but I've gotten stuck and I'm hoping some of you might be able to help.

Here is a description of my setup and problem:

db file:Buyers

This has a portal to"art using ::BadgeNumber, and relarionship to Registration using ::BadgeNumber to pull in the buyers name and address.

I have it set up so that when you enter the buyers badge number in the buyers db it lists everything that the person has bought and totals the amount due. Since the art can be bought at different times (for a quick sale, during the action, or on sunday after the action) I have a field "paid" that contains a yes check box. What I want to happen is that only the art that has not been marked paid should show up in the portal and have its price totaled.

Please help me -- I'm sure there must be an easy way to do it, but I just can't figure out how to get it to omit the checked records in the portal.

Thanks in advance.

btw: Here are the other database files for reference:

db file: artistControl

Fields: It has 20 or so fields for artists information and a portal linking to "art" using ::artist_id_number so the individual pieces of art are entered on the same layout as the other artist information.

db file: art

Fileds: artist_id_number, PieceNumber, Title, Medium, MinimumBid, QuickSale, SundaySale, FinalPrice, BadgeNumber, paid

db file: Registration. This is the main registration database for the convention and has 40 or so fields. I'm just using it so I can pull the name and address information into the buyers db.

Try this :

Use a new calculation cFlagNoPayers= Case(isEmpty (your Flagkey), "BadgeNumber ","BadgeNumber & " 0").

There is a space before th 0. This calc would invalidate any lookup or relationship if the customer paid. Use this calc for a new relationship BadgeNumber::cFlagNoPayers.

You need to have a global field gPaid in Buyers and set this equal to the value of your checkbook when not checked (no). Then have a calc field in Buyers that concatenates this global with the badge number:

In Buyers:

gPaid = NO (do this with a set field)

cBuyerPaid = BadgeNumber & " - " & gPaid

In Art:

cBuyerPaid = BadgeNumber & " - " & Paid

Create your relationship for the portal from cBuyerPaid in Buyers to cBuyerPaid in Art and you will only display records where the Paid field = NO.

The only problem I see is that you have not specified that the PAID field defaults to NO. You should set it up that way, I believe.

HTH-

Courtney

dilucaugo68's way seems better than mine - more succinct! and he's helped me out a bunch (whether or not he knows it), so it only makes sense that that would be the case.

-courtney

Hi Courtney,

Your method will just work fine, but the less global I use, the better I feel.

Actually, I used this method too until some weeks...

I got it from DJ.

Glad I could have help you out. Sure you will one day take me out of mess.

Thank -- I tried both solutions and they worked fine. I went dilucaugo68's since you both think it is the better way to go.

One more problem: I would really like to have the artist name listed along with the the art in the buyers portal. Is there an elegant was to do this?

The artist name is stored in the db_ArtitisControl and I pull it into the db_art using a relationship, but I can't seem to pull it form db_art into the db_buyers.

FMP does not seem to like going to a 2 deep relationships...

Thanks again.

Hello Again-

Make a calc field in art that equals the artist name from artitiscontrol.

cArtistName = rlnship to artitiscontrol::artist name

Now display this new field in your portal.

Cheers!

Courtney

It worked great --Thanks again!