Simplest approach is to code the barcodes so that they are identical to your part numbers. Then you have only one field and only one value to use as barcode and partnumber are now one and the same.
You have the fields PartNumber and BarCode already in the Products Table. Create the same fields in the Invoice Table. In both tables create the calculation fields PartNumberAndBarcode = PartNumber & "¶" & BarCode. Create a relationship between the Invoice Table and the Product Table linked by PartNumberAndBarcode.
In the Invoice Table create a portal to the Product table by that relationship. Enter a Product number in the PartNumber field in the Invoice Table. Or, enter a Barcode in the Invoice table. It seems, in the test file I just threw together, to pull records into the portal if they match either the product number or the barcode number.
Will you ever possibly have a part number the same as a bar code? I doubt it is possible but I wanted to verify.
No...the reason for this issue is that we have alpha numeric part numbers (which is necessary) and we have to have a completely numeric bar-code for a client. We created our original barcodes from our part-number. So now we have some product with alpha num barcode = part number which works on the invoice screen, howvever, if we ever pull product from our client we could use the "new (all numeric)" barcode that we already had to change if we could make filemaker do what I asked above. We are then planning to move to the "new (all numeric)" barcodes as we can however we have 10,000 line items so re-barcoding everything would be a terrible hassel.
Sorbsbuster...I know...just now trying to figure this out...sorry about the delay
Could you please tell me if "calculation fields PartNumberAndBarcode = PartNumber & "¶" & BarCode" means to create a calculation field called PartNumberandBarcode to equal your equation?
It will concatonate the Part number ABC/123 on to the barcode 0123456789123 with a return character separating them. It is a very useful feature of FM that it will then match to either of those values.
It's one of three ways to match records using "match any value in this list" logic.
The other two methods are to use a repeating field as the key or to go the whole way and set up an intermediate "join" table of the listed values. The method Sorbsbuster has suggested is the approach I would use here.
Hummm..I am thinking you think I am more knowlegable than I am in FM
Could you explain in more detail : In the Invoice Table create a portal to the Product table by that relationship
You have an Invoice Table.
You have a Product Table
You have a new relationship between them, with the InvoicePartNumber on one side and the new concatonated field on the other.
On the Invoice layout, draw a portal, using the portal tool. You will be asked to specify the relationship the portal should be based upon - choose that new relationship.
You will be asked what fields you want to show in the portal. Choose whatever fields from the Products table that you want to see. Choose the number of rows in the portal that you want to see.
When you enter a number in the InvoicePartNumber (which could also be a barcode) you will see all products that match that. That may only be one, of course, in which case - if it will only ever be one - you don't need a portal to show a list. Just use the fields themselves plonked on your layout.
I have everything created and linked. I am using the Invoices template. It appears to me that where I enter the part number now on the invoice screen is already a portal. I cannot figure out how to add this new field to that portal??
I was suggesting one use for the relationship, but it is not relevant in this case. Can you just revise for me again what the problem is, with reference to that screenshot?
I assume you are entering a Product Identifier (I'm deliberately not using one of your terms) and you want other Product Information to be populated because of that entry. But you wanted to be able to enter either the barcode or the PArt Number and achieve the same result each time.
If that is the case, then use that relationship I described to link to the Products Table (strictly, another Table Occurance of the Products Table) from that Portal Relationship. I am guessing that the layout of the screen shot is the Invoices Header table and the portal is the Invoices Line Items Table. You can check that by double-clicking the portal and seeing what relationship it is based on. From the Table Occurance that the portal is based on, use the relationship described to link to another Table Occurance of the Products Table, and set the fields from the Product Table directly into the portal row.
Wow thank you so much for all that you have contributed thus far... i really appreciate your help...this must really be beyond me as what i just read seems like chineese to me...Arrrggghhh..guess I will have to call the programmer after all as I am not sure this is something I can do unless the below makes any since...
"I assume you are entering a Product Identifier (I'm deliberately not using one of your terms) and you want other Product Information to be populated because of that entry. But you wanted to be able to enter either the barcode or the PArt Number and achieve the same result each time"...This is True
" I am guessing that the layout of the screen shot is the Invoices Header table and the portal is the Invoices Line Items Table" This is also True
"set the fields from the Product Table directly into the portal row" This is where I get lost as i have already created the relationship from Line Items to Invoices...am I even close???
You almost certainly have a relationship between the Invoices Table and the Invoices Line Items table - it is the relationship you are using to build that portal.
Strictly, you have a relationship between a Table Occurance of the Invoices Table and a Table Occurance of the Invoices Line Items Table.
The new relationship that I suggested should be from THAT table occurance of the Invoice Line items (its actual name will be at the top of the box that you see when you double-click the portal in layout mode) and a Table Occurance of the Products Table. Feel free to click on the Products Table in the Define Database -> Relationships graph and duplicate it with the double-green-plus at the bottom left, and make the relationship from the portal's Table Occurance to that new Product Table Occurance.
Then add the Product Table fields you want into the portal from that Product Table Occurance.
Thank you again for all your assistance...I truely wish I could figure this out but is is beyond my knowledge. thanks again