Compare Field to a Value List

[jhancock]

This is a fairly complicated request.

I have two tables in one file, Shipping & Invoicing. Each Shipping Record has a shipping_number, each Invoicing Record has a invoice_number. The Invoicing Table has a repeating field populated with the shipping numbers that are being used for that invoice (an invoice can have more than one shipping record). I would like to show the invoice_number on the Shipping Table based on if the shipping_number is included in the repeating field. I can make a value list from the repeating field but I am having a hard time writing the calculation to see if the shipping number on the Shipping Table is a value in the value list on the Invoicing Table and if it is show the invoice_number on the Shipping Table.

[Søren Dyhr]

The repeating fields are a cul de sac!! But since you're on an advanced version, can you strain what's in one valuelist ValueListItems( or text field with another ditto via a CF, such as:

http://www.briandunning.com/cf/39

But honestly would it be pretty backward to turn it back into a repeating field again ... I and other developers might think up a solution if we were forced to it, but we would rather optain a complicated fracture on the old leg than ignore relational concepts!

So please ditch repeaters immediately!!! Eventhough you have ranked yourself as advanced in skill level, must it be as chess player or as chairman in the local philatelist club??? Or please tell us why both portals and relational concepts as such in this particular solution have been avoided??

--sd

[jhancock]

Thanks for the heads up. I have so many portals and complicated relationships going on that I thought the repeating field and comparing to a value list would be a simpler solution. I will rethink my solution.

Thanks again.

[comment]

I don't get this. If there are many shippings to one invoice, why is the invoice holding the shipping numbers, instead of the other way round? If this is a many-to-many, then you should consider using a join table. But you say you "would like to show the invoice_number on the Shipping Table", so I am guessing there must be only one. So why this weird arrangement?

In any case, you should be able to show the invoice number on a layout of shipping simply by placing it there (assuming there is a relationship between the two files based on the repeating field in Invoices and the shipping number in Shippings).

[jhancock]

It is a little more complicated then I relayed. An order may have multiple shipping records, usually we have one invoice for one shipping record. There are times (customer driven) when we need to consolidate mulitple orders and shipping records on one invoice. Showing the invoice number on the shipping records helps us make sure that each shipping record has been assigned an invoice. I think I will omit the repeating field altogether and use a small related table.

[Søren Dyhr]

It really depends on the nature of the shipment, if all items wears unique ID's could an extra table be overkill ...but if the case is that shipment not always is a subset of the orders data, are you playing with fire... You seems to hint slightly in that direction??

helps us make sure that each shipping record has been assigned an invoice

Invoice and Order ought to originate from the same table, they should have only one single attribute telling whether or not it's an invoice as well!

--sd

[jhancock]

I reworked the solution using a small related table. This is working well for the outcome that I need. There is a cross reference in both the Invoice Table, which shows the shipping number (entered through scripting and manual entry for invoices that need to be adjusted per customer request) & the Shipping Table, which shows all the Invoices with that shipping number.

Thank you for the idea.