Create related records over multiple relationships

[Chris Good]

I have many tables in my db but the three I am concerned with are Invoice, Orders and Products. I use orders to specify each line item on the invoice and products obviously contains the products for each line item in the orders table. Invoices is related to Orders via an invoice number field and Products is related to Orders via an item # field.

I have a portal on the invoices layout showing records from Orders. I have a value list for Item number that shows the contents of the Item # field in products. I like that but I would also like the user to be able to type in a new number in this field to create a new products record.

I have checked the Allow record to be created option in the relationship between Order and products on the products table.

Any ideas if I can do this?

[Ender]

Hi Chris.

It doesn't work that way. You either need to create the Product ahead of time, or use a script to create Products if they don't yet exist.

[Chris Good]

Can you give me an idea of a script I could use to copy two fields in a portal row to a new record in my products table?

I have tried a few things and I can get the first field in the portal row to copy but not the second one.

[Ender]

Use globals to temporarily hold the Description and Price. I would imagine this would be part of a loop that would check each Order line item after the Invoice is completed. Something like:

Set Field [ gDescription_Temp ; "" ]

Set Field [ gPrice_Temp ; "" ]

Set Field [ gProductID_Temp ; "" ]

Set Field [ gPortalRow ; 0 ]

Loop

   Set Field [ gPortalRow ; gPortalRow + 1 ]

   Exit Loop If [ gPortalRow > Count(Orders::RecordID) ]  

   Go to Portal Row [ gPortalRow ]

   If [ isempty(Orders::ProductID) ]

      Set Field [ gDescription_Temp ; Orders::Description ]

      Set Field [ gPrice_Temp ; Orders::Price ]

      Commit Record [ No Dialog ]

      Go to Layout [ Some layout based on Product ]

      Enter Browse Mode []

      New Record/Request

      Set Field [ Description ; Invoice::gDescription_Temp ]

      Set Field [ Price ; Invoice::gPrice_Temp ]

      Set Field [ Invoice::gProductID_Temp ; ProductID ]

      Commit Record [ No Dialog ]

      Go to Layout [ original layout ]

      Go to Portal Row [ gPortalRow ]

      Set Field [ Orders::ProductID ; gProductID_Temp ]

      Set Field [ gDescription_Temp ; "" ]

      Set Field [ gPrice_Temp ; "" ]

      Set Field [ gProductID_Temp ; "" ]

      Commit Record [ No Dialog ]

   End If

End Loop

Commit Record [ No Dialog ]

It's a bit rough, but that's what I had in mind for a scripted approach. Basically create a Product with the info from the line item, then grab the ProductID (this would have to be an auto-entered serial number of some type,) and put that in the original line item. All the fields starting with g must be globals.

[Ugo DI LUCA]

Here's one possible setup that shouldn't be too hard, even if not conventional.

Check my post in the Relationship Forums about unexpected lookups and record creation wows.

I had it done before with a random_key then realized it was feasable this way too. And lately, Bruce Robertson showed me someother way I'd study in time.

Just a quick test. Hoping it works for you.

Invoicing_clean.zip

[Ender]

Not bad Ugo. It's interesting that that relationship works with the empty key gMagic.

[Chris Good]

Thanks for this idea. Ugo's is way over my head at this point. I'm still prety new to all of this.

When you speciffied gDescription_temp, and the other gFields. Do I need to create new fields for these, or should I make the current field I have global fields. I think I get what the script is doing, just not what specifically the fields you specified are. For example, what is gPortalrow.

Thanks for your help on this.

Chris

[Ender]

The globals I suggested are temporary storage locations that can be accessed from any record in the Product table (because they are global.) These should be new fields, as you current fields are probably used for other things. gPortalRow is just for remembering the portal row number the loop was on before navigating to the Product layout and back.