Newbies jhancock Posted September 11, 2007 Newbies Share Posted September 11, 2007 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 More sharing options...
Søren Dyhr Posted September 11, 2007 Share Posted September 11, 2007 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 More sharing options...
Newbies jhancock Posted September 11, 2007 Author Newbies Share Posted September 11, 2007 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. Link to comment Share on other sites More sharing options...
comment Posted September 11, 2007 Share Posted September 11, 2007 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 More sharing options...
Newbies jhancock Posted September 11, 2007 Author Newbies Share Posted September 11, 2007 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 More sharing options...
Søren Dyhr Posted September 12, 2007 Share Posted September 12, 2007 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 More sharing options...
Newbies jhancock Posted September 12, 2007 Author Newbies Share Posted September 12, 2007 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 More sharing options...
Recommended Posts
This topic is 6205 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 accountSign in
Already have an account? Sign in here.
Sign In Now