Scripted Record Creation & Then Edit

[Teutonic_Night]

I'm having a problem with a script in a new database. The immediate issue involves 2 tables, "jobs" and "pages". These two tables are related by a "job_number" field in each.

The script begins by creating a new record in the "jobs" table, and prompting the user to fill in certain fields. The user has two options at this point, to continue or cancel. Continue continues to the next step of the script, while cancel reverts the record.

The next step of the script takes the value from the "Total Pages" field in the newly created record in "Jobs" and creates new records in "P ages". The number of records created equals the value of "Total Pages" in "Jobs". As the script creates these new records, it sets the "job_number" and "page_number" fields to the correct values. So far, so good.

In the next step of the script, I need to present the newly created records in "Pages" to the user so that they can edit specific fields. This is where the problem crops up- I can't seem to be able to make it so that *only* the just-created records in "Pages" appear for editing. I've tried using a Perform Find script step, but either I can't get the right syntax to find records with a specific job number or my relationship is horked.

Does anyone have any suggestions as the a way to make this work? I suppose another way to boil down the problem is that the script creates x records, and i then need it to present *only* those x records to the user for editing.

Thanks!

[T-Square]

Give us the script to see. There should be no reason why you can't select only the current records using a scripted find. Or you could use a Go To Related Record script step, assuming: a) you are in the Jobs record, you have a relationship built between jobs and pages, and c) you have a layout for pages built on this related table occurrence.

[LaRetta]

David is correct in that it would help to see the script. But I suspect a Commit Records/Requests is required. Where will depend upon your script, of course. [color:green]But try ...

Continue continues to the next step of the script, while cancel reverts the record.

[color:green]... right about here ... COMMIT before continuing.

The next step of the script takes the value from the "Total Pages" field in the newly created record in "Jobs" and creates new records in "P ages".

Are you sure it is inserting the correct Job# into the new Pages records?

.. or my relationship is horked.

Maybe FM can't identify your new Job because it hasn't been committed? Maybe the new Pages records didn't get the Job# OR (more likely), you can't find the Jobs::Job# (are you using that in your find?). If the new Jobs record hasn't been committed, you won't be able to find it through the relationship. Just maybe ...

Do you write the Job# to global? Or do you use script parameter? At the point of continuing, do you execute a sub-script?

LaRetta

[LaRetta]

Also, an easy way to isolate a newly created record-set, is to begin with NO records.

Loop

Exit Loop If [ not Get ( FoundCount ) ]

Show All Records

Show Omitted Only

End Loop

... begin your record creation segment ...

The reason I enclosed it in a loop is to protect from multi-user. If (between the 3rd and 4th script line) another user creates a new record, it could pop into the omitted set (no records) and show itself and thus be included in your new-record group. That would be bad. This type of 'self find' wouldn't be dependent upon anything because the new records would be isolated first in a beginning found-set of zero.

Edited July 15, 2006 by Guest
Many typos tonight & added afterthought ;-)

[Teutonic_Night]

I will try adding a COMMIT step shortly, but here's a rundown of the scripts:

Main script (minus comments and unrelated items)

Perform Script ["-Prompt User For New Job Details"]

Perform Script ["-Create Page Records For New Job"]

Perform Script ["-Prompt User For Page Zones"]

-Prompt User For New Job Details

Go to Layout ["Add Job Info" (Jobs)]

Adjust Window [Resize to Fit]

New Record/Request

Pause/Resume Script {Indefinitely]

-Create Page Records For New Job

Set Variable [$$job_number; Value:Jobs::job_num]

Set Variable [$$page_count; Value:Jobs::total_pages]

Go to Layout ["Blank For Page Record Creation" Pages]

Loop

New Record/Request

Set Field [Pages::job_number; $$job_number]

Set Field [Pages::page_num; $$page_count]

Set Variable [$$page_count; Value:$$page_count - 1]

Exit Loop If [$$page_count = 0]

End Loop

-Prompt User For Page Zones

Go to Layout ["Set Zones" (Pages)]

Adjust Window [Resize to Fit]

Sort Records [specified Sort Order: Pages::page_num; ascending][Restore; No Dialog]

Pause/Resume Script [indefinitely]

I know that the new job record is created and populated correctly, and I know that the new page records are created and populated with the job number and page number correctly.

I'll post the results of adding a COMMIT step after the page records are created. Thanks everyone!

Edited July 17, 2006 by Guest

[Teutonic_Night]

I added a COMMIT statement as shown below, but it had no impact on the results. The third script, to set zone information for each of the new pages, still displays all records in the Pages table.

-Create Page Records For New Job

Set Variable [$$job_number; Value:Jobs::job_num]

Set Variable [$$page_count; Value:Jobs::total_pages]

Go to Layout ["Blank For Page Record Creation" Pages]

Loop

New Record/Request

Set Field [Pages::job_number; $$job_number]

Set Field [Pages::page_num; $$page_count]

Set Variable [$$page_count; Value:$page_count - 1]

Exit Loop If [$$page_count = 0]

End Loop

Commit Records/Requests []

Out of curiosity, does anyone know how I can perform a find using a global variable as the criteria? Is this even possible?

[comment]

If this is an exact copy of your script, then the problem - or at least a problem - is here:

Set Variable [$$page_count; Value:$page_count - 1]

These are two separate variables.

[Teutonic_Night]

Oops- that was a typo in my post. The script itself is using the correct variable. I've edited my post to correct this.

[comment]

So where exactly does a problem appear? I can't spot anything obviously wrong in your script. Going back to your original question, if you omit all records in the Pages layout before beginning your loop, you will end up with only newly created records at the end.

[fmsavey]

How about...

Perform Script ["-Prompt User For New Job Details"]

Perform Script ["-Create Page Records For New Job"]

Perform Script ["-Prompt User For Page Zones"]

-Prompt User For New Job Details

Go to Layout ["Add Job Info" (Jobs)]

Adjust Window [Resize to Fit]

New Record/Request

Pause/Resume Script {Indefinitely]

-Create Page Records For New Job

Set Variable [$$job_number; Value:Jobs::job_num]

Set Variable [$$page_count; Value:Jobs::total_pages]

New Window [Name:"Create"]

Go to Layout ["Blank For Page Record Creation" Pages]

Loop

New Record/Request

Set Field [Pages::job_number; $$job_number]

Set Field [Pages::page_num; $$page_count]

Set Variable [$$page_count; Value:$$page_count - 1]

Exit Loop If [$$page_count = 0]

End Loop

Close Window [Name:"Create"]

Goto Related Record [From Table:"Pages"; Using Layout:"Set Zone" (Pages);Show Only Related Rcords; Match Current Record Only]

-Prompt User For Page Zones

Adjust Window [Resize to Fit]

Sort Records [specified Sort Order: Pages::page_num; ascending][Restore; No Dialog]

Pause/Resume Script [indefinitely]

[Teutonic_Night]

Inserting "Show All Records" and "Show Omitted Only" steps into the "-Create Page Records For New Job" script between the Go To Layout ["Blank For Page Record Creation" (Pages)] and Loop steps seems to have done the trick. I'll pound on it some more to make sure, but it looks good so far. Thanks for everyone's assistance!

[T-Square]

I'm not sure your problem is completely fixed.

Although I am using FM7 (and therefore don't have direct experience with the use of the $$variables), I believe that you have set up job_number as a GLOBAL variable. Its value will thus carry throughout the application. If the variable is not getting set correctly, it is quite possible that your routine will create a page record for a different (i.e., the previous) job, rather than the current one. You won't necessarily see that in testing because you are repeatedly running that script, but it would be highly problematic in daily use. I would instead use a local variable and pass it as a parameter from one script to the next.

Since the problem appears to be with the job number, I would check carefully whether $$job_number is getting correctly set at the beginning by putting in a temporary dialog box in the line after the var gets set. If it's not set right, it may be that the commit step needs to go at the end of the "-Prompt User For New Job Details" script.

David

[bruceR]

One problem - you have exit loop in the wrong spot. It should be the *first* statement in the loop.

Loop

Exit Loop If [$$page_count = 0]

New Record/Request

Set Field [Pages::job_number; $$job_number]

Set Field [Pages::page_num; $$page_count]

Set Variable [$$page_count; Value:$$page_count - 1]

End Loop

[Teutonic_Night]

I'm not too concerned about this, as this global variable is only used in one particular action. I have used FM8 Advanced's Data Viewer tool to monitor the variable's value as I ran through various aspects of the database. So far, it has always had the correct value whenever it's been used.

[Teutonic_Night]

I'm curious as to why you say this- the loop creates the correct number of records. I start with the counter set to the number of records that need to be created, create a record, decrement the counter, and then check to see if the counter has reached 0. If it has, I exit the loop. If it has not, it goes back to the beginning and creates another record, etc.

Is there a specific reason I should check the counter's value at the beginning of a loop instead of at the end?

Edited July 19, 2006 by Guest

[comment]

It's just a matter of good practice. A new record should NOT be created if counter is 0, so the test should precede the action. You could emply other means to make sure that your script cannot run when Jobs::total_pages is less than 1, but this is playing it safe.

[Teutonic_Night]

The script *doesn't* create a record if the counter is zero-

Set Variable[$$page_count;Value:Jobs::total_pages]

Loop

New Record/Request

Set Field [Pages::job_number; $$job_number]

Set Field [Pages::page_num; $$page_count]

Set Variable [$$page_count; Value:$page_count - 1]

Exit Loop If [$$page_count = 0]

End Loop

For example, if $$page_count starts with a value of 2:

-the loop is entered

-a new record is created

-a few fields have values set

-$$page_count is decremented

-$$page_count is checked to see if it's value equals 0, if it does, the loop is exited, if not, the loop starts over again.

$$page_count is decremented *after* the record creation, so if it's value drops to 0, the loop exits before creating another record.

[comment]

A new record WILL be created if you START with counter at zero - for example, if Jobs::total_pages is empty.

[Teutonic_Night]

Ah. I hadn't really thought of that since the field Jobs::total_pages, which contains the value that $$page_count is set to for this routine, is required to be non-null and greater than or equal to 1.

I see your point, though. If this were a script that was used in more than one place, or where the global variable could be set to the values of different fields in different circumstances, your suggestion would ensure that no records are created when $$page_count was equal to 0.

[T-Square]

You said:

I'm not too concerned about this, as this global variable is only used in one particular action

My point is this: With the first script NOT committing the record, it is not clear whether the Jobs::job_num field will have a value yet or not. If it hasn't got a value, then it is possible that the value from the previous iteration of the script will still be active (and therefore used).

Since the variable is only used in this process, it would be better programming practice to have this variable be local and pass it from one step to the next as a parameter. That way, if there IS a problem with setting the variable, you'll see it more clearly.

Most programming guides recommend that you shun the use of global variables for the basic reason that in complex environments it can be very difficult to guarantee what a variable's value is if it can be changed by any other process in the application.

Most of the vexing intermittent problems I have encountered in my own programs have stemmed from the fact that I was using a global field in a process and it was getting changed by some other process that I had forgotten about. Or I forgot to initialize the variable, and the program happily used the old value stored there.

David