I'm having a problem with the "set field" command that doesn't make sense. I've used it many times in the past under the same circumstances without this issue, AND even withing the scipt I get conflicting go/no-go results..

 

I have two tables: "orders" and "orders line items". They're related by the fields "Order Number" and "customer number" present in both tables.

 

On a particualr layout for orders, there are global fields "qty.g", "Product Code.g", "Customer ID.g" and "Order Number.g" that are intended to be added as a new single record to the "order line items" table. All these fields are global, and defined as numbers.

 

A script first creates a new record in "orders line items", then attempts to set "qty", "Product Code", "Customer ID" and "Order Number" in the new record of "orders line items" to the global values of those four fields from "orders".

 

Only one of the four works, and it's the 3rd sequential line of those four. The other three fields are left blank. When I specify the calculated results to "1" instead of "orders::Customer number" , I get a "1" displayed correctly. This is the same for all three of the fields that aren't functioning correctly. 

 

I have tried several different combinations of which window is active with no improvement.

I've verified the FMP data visbility by typing <<orders::Customer number>> as text into the layout of "orders line items" and the correct data is displayed in browse mode.

 

I've attached the database.

 

In the "Customer Orders" layout, enter a numberic qty in the field above the qty/num/description portal, then select a product from either of the pulldown menus to the right. click the green button. The data for the line items table can be viewed in "customer Orders Line Items". It will show the last line that will match the product selected from the pulldown menu, but not the order number, customer number or qty.

 

Any help would be appreciated!

 

Scott R

925.818.5388

 

I tried to attach the file to this post, but wasn't permitted to...   :-(

 

I sounds like you are losing or not forming the relationship to properly set the fields in the line items table.

 

Suggestion: In LIne items table, form a new record.  I am assuming that each of these records have their own unique identifier, say LineItemsPrimeKey.

 

 

Either copy/paste or using a cross product relationship from line items, set a global field in Orders,  say "TempKey_g" with that identifier, LineItemsPrimeKey.

 

Go back to orders. Have a relationship established between TempKey_g and LIneItemsPrimeKey.  Use that relationship to set all of the line item data from your orders table.

 

HTH Dave

i Scott, and welcome to the Forum,

 

 

I tried to attach the file to this post, but wasn't permitted to...   :-(

Files have to be Zipped.

 

Here is a link on the steps involved to attach a file.

How to add an attachment to a reply