Help please re relationship/list

[kiwiora]

:( It's official... I suck at relationships. I admit it. But, I'm not too proud to ask for help

Re the attached - Because you can't have a relationship on a calculation, I need to come up with a better solution.

Basically the story goes that only those works available (i.e not sold) will be listed when selecting from the line items drop down list. I tried to re-create a technique with a major fail

There are several tables involved

Artworks

Invoices

invoice line items

Artworks has a relationship that identifies if an item is sold, based upon a relationship of the SerialID to invoice line items.

Hope that all makes sense :S TIA

ShowSoldWorks.zip

[comment]

you can't have a relationship on a calculation

That's not accurate: only unstored calculations cannot be used as the matchfield on the child side.

only those works available (i.e not sold) will be listed when selecting

How many times can the same artwork be sold?

[kiwiora]

How many times can the same artwork be sold?

Only once (they're very exclusive )

[comment]

Then what is the purpose of the LineItems table?

[kiwiora]

Because that was the way it was set up . I only post a cut down version of what is needed, rather than the entire system for simplicity sake.

[comment]

Because that was the way it was set up .

I don't think that answers my question. What is in the line items table that cannot be in the artworks table instead?

[kiwiora]

Invoice has multiple items.

[bruceR]

Invoice has multiple items.

That is not an answer to the question.

Are there items on the invoice that are NOT artworks?

If no, then a field InvoiceID in Artworks would do the job of attaching one or more artworks to an invoice.

ShowSoldWorks.fp7.zip

[kiwiora]

Well none of it is the answer to my original question either!!!!

invoice has artworks, freight, insurance, framing, commission...

[bruceR]

So finally! An answer to #4!

You didn't get answers to your question because you didn't supply enough information to do so.

[comment]

Well none of it is the answer to my original question either!!!!

I didn't answer your original question, because the solution is rather complex - and as it turns out, the problem doesn't exist.

[kiwiora]

and yet I can't get it to work /frustrated

It won't let me index the field(s)

[comment]

Have you looked at the demo posted by Bruce?

[bruceR]

and yet I can't get it to work /frustrated

It won't let me index the field(s)

"IT"?

Fields. WHICH fields?

What are we supposed to do with your empty answers?

[kiwiora]

"IT"?

Fields. WHICH fields?

What are we supposed to do with your empty answers?

Per my original post

only those works available (i.e not sold) will be listed when selecting from the line items drop down list

I have to have line items for the invoice because I may also be importing from an online site (that is not of my creation). i.e I need to work with the original design - Artwork <-> Line Item <-> Invoice

In the portal on the Invoice, the Line Item drop down needs to be a list of the artworks available (i.e NOT sold). The record on the Artworks table identifies as being sold if there is a corresponding item found in line items i.e Sum(Artworks_InvoiceLineItems::Constant).

[comment]

I have to have line items for the invoice because I may also be importing from an online site

I can't see the connection between the two. By now it is clear that the relationship between Invoices and Artworks is one-to-many - therefore a LineItems table is not only redundant, but actually causes unnecessary problems. I would certainly not let someone else's site dictate my data structure.

IOW, you are saying "I need to shoot myself in the foot, can someone please help with the bandaging?"

[bruceR]

Per my original post

Your original post provided no useful information; hence the questions, which you have only just now answered in part.

Then, after providing the answers, you go back to your nothingness post.

It isn't really clear what you expect here, and why you are not cooperating by providing complete and clear detail about what you are trying to accomplish.

[bruceR]

I can't see the connection between the two. By now it is clear that the relationship between Invoices and Artworks is one-to-many - therefore a LineItems table is not only redundant, but actually causes unnecessary problems. I would certainly not let someone else's site dictate my data structure.

IOW, you are saying "I need to shoot myself in the foot, can someone please help with the bandaging?"

I'm not following this.

It seems that an invoice can cover one or more artworks; plus several other charges, which seem like they could be listed as line items. It is not clear whether these other charges have a 1-1 relation to the artwork, or whether they apply as a single charge even when an order may contain multiple artworks.

It is true that since art items are unique, an invoice line for an art item will have a 1-1 relation to the artworks table.

Is there a structure that makes sense to you?

[comment]

It seems that an invoice can cover one or more artworks; plus several other charges, which seem like they could be listed as line items.

I'll change my mind if it turns out that you can buy 1 artwork with 2 frames, or just insurance and freight with no artwork...

[bruceR]

Your intended structure is not at all apparent. What about three art works; and one freight charge? Maybe a gift wrap charge. At this point you're not being any more clear than the original poster.

Maybe you advocate that the line items table only describe the additional charges?