Hi all

How would I go about achieving the following please? Apologies for the
length of the email, but I need to explain exactly what is going on...

There is a contracts table
Users add in a new contract and add what products are being ordered.
The products are entered into a contract_lineitems table obviously related
to the contract.

In contract line items we have the following fields (amongst others)
ContractID
ProductID
ProductSize
Colour1
Colour1Qty
Colour2
Colour2Qty
Colour3
Colour3Qty
Colour4
Colour4Qty
SelectedColours
CostPerUnit

ContractID is added automatically when a line item is added
ProductID is selected from a list of products
ProductSize is selected from a list based on the selection of the ProductID
Colour1/2/3/4 are popup menus from a list of maybe 25 colours relating to
the product
The Qty fields are entered by the user, the quantity ordered of each colour
SelectedColours is a calc field which lists the colours selected in the 4
fields
CostPerUnit is entered in by the user

Firstly, I know this is not the correct way to do it. Each separate colour
should be a line item of its own, but due to reasons I won't go into now,
the client wants it this way (had several heated discussions on the matter!)

A user could enter
ProductID: 1
ProductSize: Small
Colour1: Red
Colour1Qty: 5
Colour2: Blue
Colour2Qty: 6
Colour3:
Colour3Qty:
Colour4:
Colour4Qty:
CostPerUnit: 5.50

The cost can be dependent on the colour, so they link together items on one
lineitem where the product, size and cost is the same. So we could then have
a 2nd line items with exactly the same product and size but with different
colours and a different cost for these colours...

ProductID: 1
ProductSize: Small
Colour1: Yellow
Colour1Qty: 2
Colour2: Green
Colour2Qty: 3
Colour3:
Colour3Qty:
Colour4:
Colour4Qty:
CostPerUnit: 6.50

So, SelectedColours for Record1:
Red
Blue

SelectedColours for Record2:
Yellow
Green

All works OK. Not how it should be done, but it is how it is working now and
they won't budge.

I then have another table where the user marks what has been shipped (may
not all be shipped together)
They select the contract, the product and the size
This completes the relationship between the contract_lineitems and the
shipped_lineitems table

contract_lineitems::ContractID = shipped_lineitems::ContractID
AND
contract_lineitems::ProductID = shipped_lineitems::ProductID
AND
contract_lineitems::Size = shipped_lineitems::Size


In shipped_lineitems I need a field which then lists all the colours

Obviously with the above examples the relationship from the
shipped_lineitems table matches 2 records in the contract_lineitems table.
So the list always just displays the list from the 1st matched record...
Red
Blue
But I need the list to be:
Red
Blue
Yellow
Green

So taking in the colours from all matched records.

This is so the screen reads, what has been shipped? The following colours
match this contract/product/size:
Red
Blue
Yellow
Green

I really do know this a horrible way of doing things and if I could convince
them, it would be so much more simple. But it is not, so I ask for your
help! Is there any way to generate this field so it combines the lists
(SelectedColours) of all the found records in the relationship?

Many thanks
Ed








#############################################################
This message is sent to you because you are subscribed to
the mailing list FMPro
FMPro@lists.lassosoft.com
To unsubscribe, E-mail to: <FMPro-unsubscribe@lists.lassosoft.com>
Send administrative queries to <FMPro-request@lists.lassosoft.com>