Hello Board,

Thank you for all your help so far. Even if you've not replied to one of my recent threads, i've read a lot of the posts here and gained some good understanding of what I'm doing. More importantly, i've built parts of a database that are working and helping people do their jobs.

I still need some help.

I need to build a script that will automatically create new records in a database.

So, an operator inputs a JobNumber. This looks up data from the ProductTable. If the Product listed on the JobNumber has a field value (in a paticular field) of YES, then i need the operator to be prompted to enter a number (say 25).

A script then goes to another table, grabs an ID number, inputs it into the ID number field, then goes to a New Record.

I have that last part of the script worked out and it is working for one instance. What I don't understand is how i can make a field appear if that field is a YES value, then take that value and run the script that many times.

Can someone outline what i should be looking at?

1. Can I make a field appear and disappear on a layout based on the status of a field? If so, i could use that field as a space to enter in the number of records to be made. I do not need to retain that number, as the scripting will create the records that i do need.

2. Can I make a dialog box appear and then pause until the operator has entered a number and hit return? Then i could set that input as a Variable and use that variable as a repetition number in the script. Is that the best way to make this work?

Thank you again everyone!

Harry

Hi Harry, :-)

With the small number of records you need to create, I would suggest a loop since it requires no additional setup (versus repetition/import approach). Create a global text field maybe called gInput in any table then create your script similar to this (back up first):

# user inputs job number which looks up Products

If [ Product field = "Yes" ]

Show Custom Dialog [ Ok ; Cancel ; "Enter number of records to create or cancel instead" ... then at input tab, select your global field for the input ]

If [ Get ( LastMessageChoice ) = 2 // User selected cancel

Halt script

Else If [ IsEmpty ( gInput ) // User didn't enter anything ]

Show Custom Dialog [ "No number entered. Process will stop." ]

Halt Script

Else

...I do not understand this part below ...

A script then goes to another table, grabs an ID number, inputs it into the ID number field, then goes to a New Record.

... so do whatever you need such as going to get the ID

Set variable [ $id ; ID ]

Go to Layout [ where new records should be created and begin... ]

New Record

Set Field [ foreign key ; $id ]

Loop

Set Variable [ $count ; $count + 1 ]

Exit Loop If [ $count >= gInput ]

Duplicate Record

End Loop

End If

... do whatever you wish to finish up. By using Custom Dialog there is no need to show/hide a field on the layout to accept the number from the user. Creating blank records usually isn't recommended and this seems overly complex but I couldn't recommend further without seeing the file.

Also you need to fire the script. From iPad, I cannot tell your FM version but I assume you can use script trigger on product ID field exit.

Thank you LaRetta. That will take me a bit to go through and fully understand, but I will come back and let you know how I get on. Thank you again, it's really appreciated. I have spent a long time looking through all my FM books, but it's hard to know which tool to pick up to do a job when you're not sure how many tools are available and what they all do. Learning curves!

I will also try and detail the bit you didn't get. My explanation was vague because it is a bit different but also unimportant to my query.

Cheers!

Forgive me, but I don't understand how to make this script run.

What is the trigger for it? When a number is inputted into the JobNumber field? Or when you exit out of it?

Thanks,

Harry

I am sorry but I do not know how you are entering the data (into a LineItems portal? A modal window?), how you are 'going off and collecting an ID', how your tables are related, why you are creating multiple blank records, whether there are other triggers on the layout, whether there are field-level validations on any of the fields referenced, nor where you want to end up when done. :-)

You can read up on script triggers in FM Help (Script Triggers Reference) to see which would apply in your situation ... OnObjectSave might be best starting point. If it is still unclear after playing with the choices then we can help more specifically if more details (ideally the file) are provided.

And as always, back up first.

Hi, thanks. OK, i appreciate what you're saying. Its a bit difficult for me to talk to you about it because my understanding of it is quite low.

Well, our JobNumbers are 5 digits long. So what i'd like is when 5 digits have been entered, it automatically goes to the next correct field. Right now, though, that's not required and there is a step of user interaction to move to the right field. I have set the script to run OnFieldExit. And that part works.

Let me explain a bit more about the 'ID Grabbing'. We have a pool of labels with sequential numbers on them. They are delivered from a manufacturer. We attach them to products/boxes. At the end of the process, the number is entered into this FM Database and other details are attached to it's record. Such as what the date is and what the JobNumber is etc. etc.

Not all of the labels that are delivered get used. they are cheap and expendable 10 digit labels and we have continual supply.

Some products/boxes don't have these labels, but i'd like them to be put into the same table, to include all the checked products under one roof.

To that end, i've mimicked the pool of delivered labels by producing a table of alpha-numeric numbers that can be drawn from. Once one is used, it is destroyed from the pool, so it cannot be used again, just like the delivered labels.

So, rather than the operator punch/scan in the label number, i'd like my db to recognise if the product should have a label (which i've done) and if it doesn't, to ask the operator how many to 'punch/scan' and then go to that alphanumeric table, get a number, put it into the other table as if it was a scan, and then print a label, then repeat until it has reached the correct amount.

I have pared down your example so as not to confuse myself and to try and get the core part working.

So, I have:

IF [Products::ProductField = "No"]

Show Custom Dialog ["Enter", "How Many?"; Products::gQuantity]

Set Variable [$$gQuantity; Value:Products::gQuantity]

//So, this part works, i think. I am not sure if the variable bit works. But by my understanding, when the dialog box comes up and I entered '25', then hit return, this Script set the global variable $$gQuantity as '25'.

//The 'Grab a number' part goes a bit like this (to paraphrase):

Goto Layout 'Alphanumber'

Goto Record 'First'

Goto Field '1'

Select All

Copy

Delete Record 'no dialog'

Goto Layout 'Label Maker'

Goto Field 'Label Number'

Select All

Paste

//Then it does a Print Record script, that goes to a label printing layout, prints it, then returns for the next record entry.

//This part works.

What I don't understand in your description of the Looping part:

Loop

Set Variable [ $count ; $count + 1 ]

Exit Loop If [ $count >= gInput ]

Duplicate Record

End Loop

//How does this count variable work?

//Exit Loop If means that if the Variable 'Count' is greater than or equal to the gInput Variable that was set in the dialog box, then it exits? I've got that bit. But the Setting of it, i don't understand.

//You're setting the Local Variable 'Count' to equal itself plus 1? How does that work? Can you explain it to me differently or point me in the direction of some good how to's on the Count function? I don't have much experience in it.

I do genuinly appreciate your help and patience with my lack of understanding on this subject. I can see that what i'm doing is quite complicated and I don't want you to think that i'm just coming in here to ask you guys only. I'm doing a lot of reading, and i've solved a lot of problems other than the ones i'm asking you for help with. I've read a lot of your posts and they've all helped, so double thanks.

Cheers!

I think i understand this a bit more. I think i confused Count with $count.

Loop

Set Variable [ $count ; $count + 1 ]

Exit Loop If [ $count >= gInput ]

Duplicate Record

End Loop

//So, you're setting a Variable called $count as + 1 and the way it works is that it 'just works'. That part of the script just becomes a little counter for the loop script and is then referenced to the global variable that was set from the global field that was entered into the dialog box!?

I think i have it.... I'm going to bed, but i'll try this tomorrow morning.

Thank you!

you can simplify this a bit more by setting the count with the let function into the Exit step.

The first iteration thru $count is empty so the let function instantiates the variable to 1

the boolean logic at the end of the let function will evaluate to TRUE and exit the loop when $count is greater then the gInput

Loop

Exit Loop If [ Let ( $count = $count + 1 ; $count > gInput ) ]

Duplicate Record

End Loop

I sort of understand this, but looking at this:

Loop

Set Variable [ $count ; $count + 1 ]

Exit Loop If [ $count >= gInput ]

Duplicate Record

End Loop

I see that the first time it runs through the loop, it's adding 1 to 0, isn't it? To make the first 'count' a 1.

So, this script is resetting the Variable $Count every time it runs through the script? Do i have that right?

Cheers.

Hi Harry,

$Count is empty when first entering the loop. Add 1 and it then starts with 1 so then each loop is counted AND INCREMENTED by 1 with each loop. :-)

Hey Stephen!

Consider this major stolen and thank you! I will do speed tests to be sure but logically it would be faster AND it is one less line which is always a good choice. Thank you!

Added to first sentence

Hi!

I've managed to get this whole thing working with all your help. Thank you very much.

Harry