Jump to content
Sign in to follow this  
Designer

New item in product database

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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" ]

Share this post


Link to post
Share on other sites

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::B 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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Take a look at this sample file.

GoRecord.zip

Edited by Guest
Added missed step

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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!!!

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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