New Records based on Field Value

[Tim]

Hi all, I am using FileMaker for the first time and need some help. I would like to create new records based on a field value (Number of Samples). These records need to be grouped under a serial number. I would like to have the records created automatically when exiting the field. Is this possible? Open to other ways of doing this.

 

For example,

Serial Number TM-499

Number of Samples = 5 (when exiting this field create 5 records)

 

TM-499

1

2

3

4

5

[J Wenmeekers]

You need a plug-in to do that.

Like oAzium Events.

 

Or, if the field is a checkbox, you can script it based upon the value, but that is an other story.

 

HTH

 

John

[Tim]

Thanks, for getting back to me. I want to try to stay away from plugins. What about creating the records via a button.

 

Not sure about the script in FM, but something like this:

 

numsamples = 5

 

Do Until numsamples = 0

Create New Record

numsamples = numsamples - 1

Loop

 

Tim

[andygaunt]

Tim,

 

Almost.

 

what you will want is a counter field (global number)

 

Is your numsamples a global too.

 

Then the script

 

Set Field (counter) = ""

Loop

Exit Loop If (Counter = numsamples)

New Record Request

Set Field (counter) = counter + 1

End Loop

 

add the rest of the script here

 

Could do it without the counter this way

 

Loop

Exit Loop If (numsamples = 0)

New Record Request

Set Field (numsamples) = numsamples -1

End Loop

 

only works if your numsamples is a global number field.

[J Wenmeekers]

Just on the fly, (didn't test it)

 

You need a global field that holds the number of 'records to create', which is equal the value checked in the checkbox, after you cleared the global field.

Create a script with a loop that stops when the number of created records equals the value in the global and then delete the global.

 

So you have a global field RecordCount.g.

Scriptstep

Clear RecordCount.g

(sets field = empty - this is to be sure the field is empty - you don't really need this step, I use it just use it by the force of habit and because I use this field for other things to do)

Set Field ("RecordCount.g","NameCheckboxField")

Loop

Create record

Set field ("RecordCount.g", "RecordCount.g -1")

Exit Loop if ("RecordCount.g = 0)

End Loop

Clear RecordCount.g

 

You put the buttons to activate this script over each possible value (separate) from the checkbox.

 

If you have 5 or 6 poss. values this technique can work if you have more it can be messy.

 

HTH

 

John

[Tim]

Ok, I have tried your suggestion, but there are two problems:

 

1. It only creates 1 record no matter what value I put into the "Number of Samples" field.

 

2. Not really a problem, but creating the record in wrong place. I have a different db where I would like to create these records (Sample Set Inventory.fp5) The samples also need to be grouped under a SPS Number (TM-499)

 

Script:

 

   
If ["Number of Samples <> ""]
  Clear [select, "SampleRecordCount']
  Set Field ["SampleRecordCount = Number of Samples"]
  Loop
     New Record/Request
     Set Field ["SampleRecordCount = SampleRecordCount - 1"]
     Exit Loop If ["SampleRecordCount = 0"]
   End Loop
   Clear [select, "SampleRecordCount']
End If

I hope you don't feel like I'm taking advantage of this forum and your suggestions. I just got pulled into this project. Is there a debugger in Filemaker? Just wondering, what are your views btwn FM and Lotus Notes.

 

Tim

[Tim]

Ok, I fixed the first problem - adding the correct number of records. I changed the code to what is below. Don't know the difference between what I had before. Still stuck on adding the records to another db.

 

   
If ["Number of Samples <> ""]
  Clear ["SampleRecordCount']
  Set Field ["SampleRecordCount","Number of Samples"]
  Loop
     New Record/Request
     Set Field["SampleRecordCount", "SampleRecordCount - 1"]
     Exit Loop If ["SampleRecordCount = 0"]
   End Loop
   Clear ["SampleRecordCount']
End If

[andygaunt]

Tim,

 

try to steer clear of clear steps. much better to use set field ( as then the field does not need to be on the layout). And before the boys start, the times you might want clear is to clear a date or time, as set field will not work properly.

 

To make this run in a second db, create a constant relationship ( 1 to 1) which is best done with a field in each db called Pipe (text field auto enter 1)

 

Then you can have this script in the second database and call it from the first.

 

Create this script in the primary db

 

Set field (ConstantRel::sampleRecordCount, Number of Samples)

 

Then perform the external script

 

Originally posted by Tim:

[qb]Ok, I fixed the first problem - adding the correct number of records. I changed the code to what is below. Don't know the difference between what I had before. Still stuck on adding the records to another db.

 

   
If ["Number of Samples <> ""]
  Clear ["SampleRecordCount']
  Set Field ["SampleRecordCount","Number of Samples"]
  Loop
     New Record/Request
     Set Field["SampleRecordCount", "SampleRecordCount - 1"]
     Exit Loop If ["SampleRecordCount = 0"]
   End Loop
   Clear ["SampleRecordCount']
End If

[/qb]

[J Wenmeekers]

Use the 'Perform script' step and refer to a script in the 'external' file, instead of the create record step.

 

In the second file use the New record script step to add records.

 

HTH

 

JW

[Tim]

Hey guys, thanks for all of your help. I think I got it working. There was a script already created in the other database. Don't know if there will be any problems with it, but here it is.

 

   
If ["Number of Samples <> ""]
  Set Field ["SampleRecordCount","Number of Samples"]
  Loop
     Set Field ["SampleNumber", "SampleRecordCount"]
     Perform Script [sub-Scripts, External: "Sample Set Inventory.fp5"]
     Set Field["SampleRecordCount", "SampleRecordCount - 1"]
     Exit Loop If ["SampleRecordCount = 0"]
   End Loop
End If

The Subscript

Freeze Window
Allow User Abort [Off]
Set Error Capture [On]
Go to Layout ["blah, blah"]
Enter Find Mode []
Paste [select, "SPS Number"]
Perform Find []
If ["Status(CurrentError) <> 0"]
   New Record/Request
   Paste [select, "SPS Number"]
   Set Field ["Sample Number", "Sample Number = SampleNumber::SampleNumber"]
Else
   Set Field {"Sample Counter", "Status(CurrentFoundCount"0"]
   Go to Record/Request/Page [Last]
   Duplicate Record/Request
   Set Field [sample Number", Sample COunter + 1"]
End If
Set Error Capture [On]
Toggle Window [Minimize]  

How does that look? Again thanks for all of your help.