create related record

[grumbachr]

Would like to be able to create a record in a table based on a related record. I'll try to explain things as best I can.

This is an inventory database. I have a table for CPUs and one for displays. Both tables represent 1 workstation. In the CPU table the field for workstation is an auto-enter serial number.

I want to enter info for a CPU that can be either a desktop or a notebook. Then if the workstation has the need for a display record I want to click on a button to take me to the display table/layout related record and automatically create a new record for that workstation if there is not one already present.

The CPU and Display tables have a Workstation number field and I have assigned a relationship to that field. (the workstation field in the display table in NOT an auto-enter serial number like the CPU field is)

Any suggestion would be greatly appreciated.

If I can provide anymore details please let me know.

FileMaker Version: 7

[Ender]

Not sure what your question is. Though I would suggest having your displays, CPUs, printers, and any other equipment all in the same table--It's easier to print intentory reports this way. In this scheme, if you want to link a display with a particular CPU, just have a field that you enter the System CPU serial number into. Create a self join relationship from System CPU serial # and the Serial number of the record.

Hope that helps.

[RalphL]

If I understsnd the original question the follwoing will create a new record in the related file. Start by adding a global field of the type as the workstation serial number. In your script do the following:

Set the global field to the serial number

Go to the layout for the display

Create a new record

Set the Workstation serial number to the global field.

Enter other data and return to the other layout.

[grumbachr]

Thanks your suggestion worked perfectly. But now I need to make it find if there is already a record before it creates a new one. I only want to create a new one if there is not one already present.

Guess I need to move this question over to the Scripting area.

Sorry for posting it in the wrong place.

grumbachr

FileMaker Version: 7

Platform: Mac OS X Panther

[RalphL]

Try going to the related record first. If you don't get an error then record exsits. I f you do get an error, 101 as I remember, then create a new record. You will need to trap errors in your script.

[-Queue-]

Or to make it easier (and quicker), test for Count(rel::id), IsValid(rel::id), or rel::constant = 1.

For example,

If [not rel::constant = 1]

Perform Script [External, Create new record]

End If

[Karma]

Here is a script that i created that tests to see if a record is already created

(it is probably the same as Queue, but ahh well...

Create a global field, doesn't matter if it is in your primary table or on it's own.

the first step is to set the global field to match the id field that you are creating the record from. then test to see if the record exists, using an IF statement...

lets say that:

the global field is Global::Transfer

the record id in the current table (table A) TableA::recordID

The related record that you want to open/create (table : is TableB::RelatedID

Set Field[Global::transfer; TableA::recordID] ---// the first is where to copy to and the second is where to copy from

Go to related record[from table:"tableB"; Using Layout "Table B"(TableB)] ---// you can choose to open a new window here...

IF [isValid(TableB::RelatedID)] ---// if it is valid then you don't need anything more to happen

Else ---// if the record does not exist then...

--->New Window ---//optional

--->Go to Layout [TableB]

--->New record

--->Set field [TableB::RelatedID; Global::transfer] ---// this then copies the id from the global field to the new record

EndIF

If there is a better way to do this let me know... but this seems to work well for me.

[Robert Kidd]

As an addition to Ralphs suggested method of creating a related record you could link your button to a script parameter and use the

Get ( ScriptParameter ) and avoid the global field completely

[-Queue-]

Here is a v7 sample of a method described in this thread. I'm curious if it works correctly. I'm still on the trial version at work and can't get the related serial number to populate. So I'm wondering if it works for 7.0v2 or if this technique is not doable anymore.

Main.zip