Relationship connections and multiple search fields

[TexWalker]

I am in the process of creating a parts inventory system. I have most of my database done and the system is coming together well. I have a barcode scanning system in place so that my technicians don't have to do anything other than scan a part as they leave my shop. However, I am running into a problem with the way I have Relationships and Lookups established and I am hoping I am overlooking something simple.

 

My database consists of 2 main files (Inventory/parts database and Transaction database). The Transaction database references the Inventory/parts database based on a scanned barcode; every part in the database has a manufacturer/part number/barcode associated with the part (easy). The problem comes in with the fact that some of our suppliers will add their own barcode over the manufacturers barcode (example: Widget A with manufacturer barcode 1234 AND Widget A with vendor barcode WXYZ). I would like to have only 1 page associated with each manufacturer part for inventory reasons, I don't mind having several field boxes in the inventory/parts database to contain different barcode numbers corresponding with different vendors if there is a simple way to search multiple fields with one button/scan.

 

Is there any way to set up my transaction page to scan multiple fields in a related database? Or is there a way to separate several numbers in 1 field and search for each set individually? In the Edit Relationship window, you can have AND logic, is there a way to set it up as an OR instead?

 

Any tips/suggestions/ideas? I am in a little over my head at the moment.

 

Thanks! 

[Fitch]

You can search several fields by stacking up Find requests. E.g. in your script:

 

Set Variable( $code ; scanned barcode )

Enter Find mode

set field( barcode A ; $code )

New Record/Request

set field( barcode B ; $code )

New Record/Request

set field( barcode C ; $code )

Perform Find

 

Is that what you mean?

 

Welcome to the forums, Tex.

[TexWalker]

Now you have gone over my head. Its not so much a "Find" issue as an autofill issue

 

I currently have a button setup with a script that takes me to CNS barcode software on an iPad. Once the barcode is scanned, I have a very simple Set Field [file::barcode; Get( ScriptParameter)] script to place it in the correct box. I then have a Lookup (File -> Manage Database) set up for all of the additional information taken from the inventory/parts database.

 

What I would really like to do is have a couple fields available to list different barcode numbers and have any of the scanned codes auto-populate the rest of the information. Does that make sense?

 

I don't know if your script would work, I can't seem to make heads or tails of the Filemaker scripting. Basically, I want it to be able to find the corresponding record and allow me to tweak my in/out levels to keep inventory numbers right. If I can find a way to have a "Perform Find" and then auto paste the rest of the information, I'd be happy.

 

Just for the record, I hate computers. I was hired to turn wrenches, but got stuck banging on the keyboard with a wrench instead.

 

Thanks! 

[TexWalker]

Tom, I think that that might work...just need a way to turn the "perform find" into a data point. I don't want my guys to have to scroll through multiple things. 

Would there be a way to "perform find", when the found record pops up, take barcodeA, copy it and paste it into my other database as barcode?

 

Cause if something like that could work, I would owe you a 12-pack!

[eos]

Tex,

 

if I understand this correctly, the challenge is that an identical piece of equipment can have several identifications (in the form of a barcode); you only want to keep one record for the part itself (which is the correct idea), but be able to specify which variant(?) code is actually used for a given transaction.

 

Then instead of using another barcode field* , it might make sense to add a table Barcodes to the Parts table, where you store all the barcodes for your parts (even if there is only one). If upon creation of a new transaction only one related barcode exists, then just use that one automatically; if there are several, you can/must select the one to use. (Depending on your workflow, there are different approaches to ensure that the transaction barcode field is always filled in.)

 

Using related records also allows you to add more detailed info to each barcode entry, like vendor/manufacturer ID etc.

 

*Once you start adding fields like barcode2 ({barcode3…}] to a able, you know it's time to set up a new table and use related records instead. This is a bit more work upfront, but pays off handsomely in the long run.

[TexWalker]

Eos, I like where you are going with this. I can definitely create another table but how exactly could I tie a series of barcodes in one table to a single part in another table?

 

I am going to have to think this through a little bit. 

 

Thank you!

[eos]

how exactly could I tie a series of barcodes in one table to a single part in another table?

 

By using the primary key of the part record (a unique ID) as the foreign key for the barcode records; that's the usual method to relate child records to their parent record.

[TexWalker]

You lost me there. Sorry, computers are not my strong point.

[TexWalker]

EOS, I think I get it now, great idea!!!

[Lee Smith]

Understanding Relational Design is important to developing solutions in FileMaker, that is beyond a single file such as a Rolodex file (i.e. name and addresses). If you plan to develop this file yourself, then I recommend that you get a a book on relationships, watch some videos about relationships design, take a class, or hire a consultant.

 

I think that there are some videos on Youtube that can help you, and there are some professional classes put on by VTC.com. I just reviewed a bunch of the video training classes at Lynda.com, and found them to be very good at explaining ERDs, and Relationship designs.

 

HTH

 

Lee

Edited August 28, 2013 by Lee Smith
reworded some of the statements

[TexWalker]

EOS, it worked. You, sir, are a scholar and a gentleman!!!

 

I will write out what I ended up doing as soon as I finish dealing with the emergency here at the office.

 

Thank you all!!!!

[eos]

EOS, it worked. You, sir, are a scholar and a gentleman!!!

 

I will definitely put that in a frame on my wall 

 

Anyway, good to see you had your epiphany. Let us know how it goes, and if you need assistance. (And you may still want to follow up on Lee's suggestions.)

[TexWalker]

Alright, so what I ended up doing is creating a separate table where every barcode is converted into an in-house part number. The in-house part number is then referenced back into the database in the barcodes place. It added a step to the process of creating but I now have a few scripts in place to take me from table to table automatically so its not so bad.