How Do you ADD A NEW RECORD in a related table??

[Neon123]

Hi,

I am working on a DB Solution that is PROJECT BASED, You create PROJECTS ( JOB ORDERS) in the MAIN JOB FILE and then you add various other info through different tabbed layouts which appear under the JOB INFO at the top half of the layout, for example You add a job, and then related to that job you can add many Quotes, Purchase Orders, Invoices etc.

Each JOB ( Project ) can have more than one INVOICE, QUOTE or PURCHASE ORDER, I have Set Up the Relationships so far using the "SEPARATION METHOD" and the Table structure as such..

JOB ORDER ---> INVOICE MAIN ---> INVOICE ITEMS

JOB ORDER ---> QUOTE MAIN--->QUOTE ITEMS

JOB ORDER ---> PURCHASE MAIN--->PURCHASE ITEMS

On one particular layout ( Which is set to JOB ORDER) as the current table, I have a "PORTAL" of Items that belong to the "QUOTE ITEMS" Table for example. I wish to display the QUOTE ITEMS from Different QUOTE ORDERS that are created for a particular JOB ORDER.

I have a "POP-UP" list ( Above the portal) which can display the QUOTE NUMBERS for a particular JOB ORDER ( Project) so that I can filter the QUOTE ITEM RECORDS in the Portal.

I have a Button that says "CREATE NEW QUOTE" on this layout

My question is..

How do I make script (linked to the button "CREATE NEW QUOTE"), that can add a "NEW QUOTE NUMBER" in the related QUOTE MAIN table from this layout?

FM7 will ONLY allow you to add "NEW RECORDS" to the main file! ( in my case the JOB ORDER Table). How Can I tell FM7 to ADD a New QUOTE ORDER to the QUOTE ORDER TABLE" using a script?

I have attached a sample of my layout for viewing.

Thanks,

Dave M

[Vaughan]

Freeze Window

Go to Layout [ any layout for the Quote Order Table ]

New Record/Request

Go to Layout [ original layout ]

[Neon123]

Thanks Vaughan,

I forgot about the "FREEZE Window" Function,

I also Added a step to paste the current job number in the Quote Main Table so I can reference them later.

Thanks for the quick, useful and easy answer!

Dave M

[-Queue-]

If you're in a multi-user situation, it's better to use a calc of serial_1 (serial & "_1") rather than just 1,

so that one user doesn't affect another's accidentally, if both are running the script at the same time.

[smorr]

One technique I often use is the concept of a creation relationship

create in destination table a number field called "Create" --

In start tabe, create a calc field "k_ONE" with calc value 1

create a relationship between two tables based on k_ONE = Create. set the destination of relationship to beable to create a record.

Write your script such that:

Set Field [Create_in_DestTable::Anyfield; DataToSet]

# in setting the data it will create the record.

# Note Create_in_DestTable is a TO or the destation table at the end of the relationship above.

Set Field [Create_in_DestTable::WhateverElse; MoreData]

# Other set fields...

# Set a currentRelatedRecord if desired

Set Field [ThisTable::gCurrentRelatedRecord; Create_in_DestTable::recordID]

Set Field [Create_in_DestTable::Create; 0] // Releases the creation relationship.

There as several nice benefits of this technique:

1) you can very easily create records in tables in other databases without having to run external scripts or jump around relationships.

2) You can also very easily populate data without having to have globals to hold information used to populate the new record (ie making a partial duplicate of an existing record)

3) You can also build your relationship to be predicated on more than just one key (the k_ONE =Create).

By doing so, you can file in many pieces of data at the point of creation.

The downside to this is the possibility that a stale "unclosed" creation exists... (ie a record has the 1 in the create field before the creation will result in overwriting existing data rather than creating a new record. but this can be taken care of by an if scriptstep and loop beforehand:

if [isvalid(Create_in_DestTable::recID)]

loop

Set Field [DestTable::Create; 0] // Releases the pre existing creation relationship.

exit loop if [not isvalid(Create_in_DestTable::recID)]

end if

[James McIlwrath]

Freeze Window is not web compatible, is it? Can I assume that this solution wouldn't work in a web application?