Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Smile Unanswered: Creating Related Records in Separate Databases

    I haven't used FileMaker in a long time and clearly I've become a bit rusty!

    I am creating a workflow system at work using FileMaker Pro 5.5 (because it's a cross-platform network of Macs and PCs). Basically, there is a Job Ticket database that has a unique, serialized Job Number. What I want to have happen is that when a new Job Ticket (record) is created in the Job Ticket Database, I'd like a corresponding record to be created in a separate Shipping Label Database and also a new record in a Packing List Database. I have relationships set up to each using the unique Job Ticket number, but it doesn't automatically create the records in the Shipping Label DB or the Packing List DB. The best I've come up with is to have a button for each DB on the Job Ticket DB that copies the Job Ticket Number to the clipboard and brings up the appropriate DB, and when you click a "Select" button in it, a FM Script pastes the clipboard into Shipping Label or Packing List DBs' and when you tab off of that field, a lookup occurs and fills in the info.

    There must be a better way than this! Any helps would surely be appreciated. Thanks!

  2. #2
    Join Date
    May 2003
    Posts
    10

    Re: Creating Related Records in Separate Databases

    I dont think you need those seperate databases just add layouts to your Job Ticket Database that show the information you want...
    There are ways to create those records using the set field command in scripts just set field to the database you want then the lookups will do their job.


    Originally posted by hsdajr
    I haven't used FileMaker in a long time and clearly I've become a bit rusty!

    I am creating a workflow system at work using FileMaker Pro 5.5 (because it's a cross-platform network of Macs and PCs). Basically, there is a Job Ticket database that has a unique, serialized Job Number. What I want to have happen is that when a new Job Ticket (record) is created in the Job Ticket Database, I'd like a corresponding record to be created in a separate Shipping Label Database and also a new record in a Packing List Database. I have relationships set up to each using the unique Job Ticket number, but it doesn't automatically create the records in the Shipping Label DB or the Packing List DB. The best I've come up with is to have a button for each DB on the Job Ticket DB that copies the Job Ticket Number to the clipboard and brings up the appropriate DB, and when you click a "Select" button in it, a FM Script pastes the clipboard into Shipping Label or Packing List DBs' and when you tab off of that field, a lookup occurs and fills in the info.

    There must be a better way than this! Any helps would surely be appreciated. Thanks!

  3. #3
    Join Date
    Sep 2003
    Location
    San Francisco
    Posts
    70
    There are several ways of doing this. I'll describe the method I use:

    This exampe assumes you have three files:
    Main.fp5
    Parent.fp5
    Child.fp5

    Main.fp5 should have the following fields:

    zkg_newID (global number)


    Parent.fp5 should have the following fields:

    zkp (primary key, number, auto-enter serial)
    zkg_newID (global number)
    field1 (any data field)


    Child.fp5 should have the following fields:

    zkp (primary key, number, auto-enter serial)
    zkf_parentID (number, foreign key to parent table)
    field1 (any data field)


    Parent.fp5 should have the following relationship:

    Relationship Name: Main.Constant_NOT
    Related file: Main.fp5
    Left field: zkg_newID
    Rigth field: zkg_newID
    ** When you create this relationship, you'll get an alert warning you that this relationship will not work. This is OK.

    Relationship Name: Child.New
    Related file: Child.fp5
    Left field: zkg_newID
    Right field: zkp


    Child.fp5 should have the following relationship:

    Relationship Name: Main.Constant_NOT
    Related file: Main.fp5
    Left field: zkg_newID
    Right field: zkg_newID
    ** When you create this relationship, you'll get an alert warning you that this relationship will not work. This is OK.


    Child.fp5 should have the following script:
    Script Name: "NewRec (external)"
    Script Steps:
    Allow user abort [Off]
    Set Error Capture [On]
    Enter Browse Mode []
    New Record/Request
    Set Field [Parent.constant_NOT::zkg_newID, "zkp"]

    ---

    Now that you've created an infrastructure, we can create related records and set values in them easily.

    From Parent.fp5, lets create a script, "CreateChild":
    Script Name: "CreateChild"
    Script steps:
    Allow user abort [Off]
    Set Error Capture [On]
    Enter Browse Mode []
    #
    # Create a child record and get a pointer to it
    Perfom Script [Sub-scripts, External: "Child.fp5", "NewRec (External)"]
    Set Field ["zkg_newID", "Menu.Constant_NOT::zkg_newID"]
    Exit Record/Request
    #
    # Set attributes for the new child record:
    Set Field ["Child.New::zkf_parentID", "zkp"]
    Set Field ["Child.New::field1", "field1"]
    Set Field ["Child.New::field2", ""any data here""]
    Set Field ["Child.New::field3", "field3"]
    ... etc ...


    ---


    This methodology is convenient because it allows you to easily set as many fields as you like in the child file -- and the changes are limited to a single script. Also, by avoiding the use of copy/paste or setting globals in script and then reading them in another, you make the code more compact, less error-prone, and easier to debug.

    As long as you're careful to maintain the infrastructure by including the "zkg_newID" field, the "NewRec (external)" script, and the "Main.Constant_NOT" relationship in every file, you can easily create a record in any file at any time without having to add fields, scripts, or relationships.

    Hope this helps!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •