New item in product database

[Designer]

How can I make a script that will take my client's inputted part number into a field, and check to see if that part number already exists and then either:

1. Show the existing record, or

2. Create a new record with that part number.

I would like to do this without:

A. the annoying "There are no records that match that request" alert box after an unsuccessful FIND, or

B. without my client needing to type the part number more than once, and:

C. without needlessly creating a new record before discovering that number exists already.

Thanks!

[mr_vodka]

The input field should be a global field (lets call it gInput). Create a relationship from gInput to the partnumber field. Check the 'Allow Creation of Record' on the child side.

Then a script can be simple as:

If [ IsEmpty ( Trim (gEmpty) ) ]

Show Custom Dialog [ "Message"; "Please enter a part number in the field' ]

Halt Script

Else If [ IsEmpty (NewRelationship::partnumber) ]

Set Field [ NewRelationship::partnumber; gInput ]

End If

Go to Related Record [from table: "NewRelationship" ]

[Designer]

Hi John,

OK, here's what I've got and it's not working. "Product Control" is the main table in my database. I created a new Global storage field within this table and called it "gInput". I made a relationship from this field to the field "Part Number" (both in the same table) but this required a new occurance of the table "Product Control" in my relationship graph which had the default name "Product Control 2" but I changed the name to "NewRelationship". I'm not sure which of these is the Child for me to "allow creation of records" but I've tried checking the box on both sides and neither one seems to work. Anyway, this is what I have:

If [isEmpty ( Trim ( Product Control::gInput ))]

Show Custom Dialog ["Please enter a new part number"]

Halt script

Else If [isEmpty ( NewRelationship: Part Number)]

Set Field [NewRelationship: Part Number; Product Control::gInput]

End If

Go to Related Record [From table: "NewRelationship"' Using layout: "Spec Sheet" (Product Control)

Can you see where I've gone wrong?

Thanks so much!

Tim

[mr_vodka]

The new table occurence would be the child. Check to see if there is a data mismatch between the gInput field and the Part Number field. If your part number is a text field, make the global field a text field as well.

[Designer]

Hi again, OK, I've checked that. They are both Text fields. Still nothing. Actually, if the gInput field is blank, it does give the alert text to enter a new part number, but then once I do enter that, if I run the script again, it just goes to the specified layout "SpecSheet" but doesn't add a new record - just shows an old existing record.

Should the layout where I'm entering the part number be showing records from "ProductControl" or from "NewRelationship"? Also, on that layout should the new field "gInput" be from "ProductControl" or "NewRelationship"?

Thanks!

[Designer]

On a side note - I looked at your link to the WhitePaperForFMPNovices.pdf and downloaded it. Looks like a good resource - thanks. It recommends reading a good, fat Filemaker book first though, so I wonder what's a good one. Is there a Filemaker book you would recommend?

[mr_vodka]

Special Edition Using FileMaker 7 is good book even though it is for 7, it is a great resource. If you are willing to get some videos there are good training videos that John Mark Osborne has at http://www.databasepros.com

[mr_vodka]

Take a look at this sample file.

GoRecord.zip

Edited April 27, 2007 by Guest
Added missed step

[Designer]

OK, well here's something new. I noticed that in the example you attached, you removed the "Halt Script" line. So I removed it too, and now when I invoke the script if the gInput field is empty, I get the Custom Dialog alright, but when I click OK in that dialog, it takes me to the other layout without giving me the opportunity to enter a part number. Or if I invoke the script with a part number in gInput, still it goes to the other layout - either way, it shows an existing part number from the database and does not add a new one.

Aside from that, I can't for the life of me see what's different but it's still not working in mine like it does in yours. I've attached my version of this in case you'd like to see if you can find what's wrong.

However, I also think maybe I should clarify how I'd like this to work:

When my client adds a new widget to their product line, one of the first things they do is assign it a part number and add it to the database. Up until now ("now" being whenever I get this script figured out), they would Create a New Record, and input the part number they would like to use. With as many items as they have, this has several times caused problems when they inadvertently used a number already assigned to something else. So I would like for them to be able to click on "Add New Item", which will take them to a layout where they will input the new part number, then click on "OK". Then this script should run and the following should happen: If the number is already in use by an existing item they should go to a layout saying so, and displaying the part number and description and maybe a few other things from the same table, OR if that part number is Not already in use, then I just want it to Add the new record to the database with the number they just entered and go to the "SpecSheet" layout.

Looking more closely at what you showed me, I'm wondering if this will be doable from that. What do you think?

I sure appreciate all the help - Thanks!

Product_Control_Clone.fp7.zip

[mr_vodka]

Why not just let FileMaker create a part number for you using a serial number? Is there a particular reason why you need to allow the user to create one?

[Designer]

Yeah, they're somewhat particular about their part numbers in that within a given product group or category, they like to have all the numbers close to one another.

I might just go with what I was working on, and let them click the "continue" button on the Dialog that says "There are no records that match this request". At the rate I'm going, that would save them a lot of money...

Anyway, I sure appreciate your help though, thanks!

[Designer]

I figured it out. Took a fresh look at it this morning and saw I had the fields transposed in my Relationship window. Funny how that eluded me for hours yesterday! Anyway, it works great now! Thank you!!!