Jump to content

Compare Field to a Value List


jhancock

This topic is 6129 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

This topic is 6129 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.