Copy data in one table, paste in record of another

[Ray-El]

My database has two tables; one with product information and another that is an order form.

I set it up so customers can browse items in one window and click an "ADD TO ORDER" button that copies the item number from the item database, then goes to the order form layout, pastes the item number into an item number field in a portal on the order form, and then returns to the list of items. After the item number is pasted, the order form populates with all the relevant pricing data for that item from the item database. The general idea is to have the order form act as if it were a shopping cart.

The problem I'm having is that I can't get the item number to paste into the current record on the order form. FileMaker has options to go to the First, Last or Next record, but I want it to paste into the current active record. Does anyone have any suggestions for how I can do this? Perhaps some kind of calculation?

I tried to omit all other records, but the data will still paste into a different record, even if it that record is not included in the found set. I need the customers to be able to add items to whatever order form they happen to be viewing. Each order form does have a unique record ID.

Here is the script I'm using:

Copy [select; Products::Item Number]

Go to Layout ["Product Order" (Program Order)]

// THIS IS WHERE I"M HAVING THE PROBLEM...

Go to Record/Request/Page

Go to Portal Row [select; last]

Paste [select; Line Items::Item Number]

Go to layout [original layout]

Thanks for any suggestions.

[comment]

Is this really for version 7?

[Ray-El]

No. It's version 11 Advanced. I just haven't updated my profile in a really long time.

[bruceR]

My database has two tables; one with product information and another that is an order form.

I set it up so customers can browse items in one window and click an "ADD TO ORDER" button that copies the item number from the item database, then goes to the order form layout, pastes the item number into an item number field in a portal on the order form, and then returns to the list of items. After the item number is pasted, the order form populates with all the relevant pricing data for that item from the item database. The general idea is to have the order form act as if it were a shopping cart.

The problem I'm having is that I can't get the item number to paste into the current record on the order form. FileMaker has options to go to the First, Last or Next record, but I want it to paste into the current active record. Does anyone have any suggestions for how I can do this? Perhaps some kind of calculation?

I tried to omit all other records, but the data will still paste into a different record, even if it that record is not included in the found set. I need the customers to be able to add items to whatever order form they happen to be viewing. Each order form does have a unique record ID.

Here is the script I'm using:

Copy [select; Products::Item Number]

Go to Layout ["Product Order" (Program Order)]

// THIS IS WHERE I"M HAVING THE PROBLEM...

Go to Record/Request/Page

Go to Portal Row [select; last]

Paste [select; Line Items::Item Number]

Go to layout [original layout]

Thanks for any suggestions.

Best practice is to *never* use copy paste.

This is more like it but I think we're still missing some information about what you're trying to do.

Set variable[ $productID; Products::Item Number]

Go to Layout ["Product Order" (Program Order)]

Go to Portal Row [ last ]

Set field [ Line Items::Item Number; $productID ]

Go to layout [original layout]

[comment]

I would do it this way:

PortalToPortalSimple.zip

[Ray-El]

Thanks! That set-up looks like it will work. I'll have to see if I can recreate it in my database without missing anything.

I'll also keep my eye out for any other instances where I cheated and used Copy/Paste instead of Set Variable. It seemed like the easiest and quickest way to go, but even as I was setting it up, I had a feeling it wasn't the best way to go.

[comment]

No, and neither is fiddling around with portal rows (unless you're merely assisting in user entry).

[bruceR]

I have taken the liberty of modifying Comment's excellent example to show an alternate technique that does not require layout switching. There are variations on this technique where you put the global in a utility table instead of the invoice table.

PortalToPortalSimple.fp7.zip

[comment]

What's wrong with layout switching? I'd rather switch layouts than add fields and relationships.

[bruceR]

Well, for me, lots; screen flash, spawning and control of many offscreen windows (in some developers methods of doing this). Duplication of effort - you very often need record selector globals anyway. It just seems kludgy to me but of course there are differing opinions. In cases where a session model is being used the mechanism is already set up for a utility table. Performance also.

Edited April 29, 2010 by Guest

[comment]

screen flash, spawning and control of many offscreen windows (in some developers methods of doing this). Duplication of effort

Does my file exhibit any of those?

[bruceR]

I don't know. I don't use Windows, where these issues become even more pronounced and are quite regularly complained about. Your file is a really simple example and no, it doesn't show these effects. But it also doesn't do any of the zillion other things that may need to be done in a more complete and complex solution.

[comment]

I prefer to have a problem before looking for a solution...

[bruceR]

I described list of problems; observed by many and the subject of very regular complaints by Windows users. The method is certainly not universal but is quite commonly practiced. Performance is another reason, and I've heard happy reports from people who switched techniques. If you start having to create multiple records, maybe in multiple tables, it can be quite handy. Guess you could compliment me on adding to the toolkit if you felt like it.

[Søren Dyhr]

Matt Petrowski uses this in one of his newer movies, and claims if looping is the issue ... is there a speed gain to observe?

--sd

[bruceR]

Matt Petrowski uses this in one of his newer movies, and claims if looping is the issue ... is there a speed gain to observe?

--sd

Your question is confusing. Matt claims ... what?

Already mentioned that I and others have experienced performance benefits. OK, just performed a test. Global method 5X faster. 1000 records/2 seconds w global, 1000 records/10 seconds with go layout method.

Edited May 1, 2010 by Guest

[Søren Dyhr]

Ah ...the question mark in the end sorry. But how well was the non-global scripting then done? Freeze usually makes quite a difference.

--sd

[Ray-El]

Thanks for all the tips. I went with a modified version of the first option with the layout switching because I just ran out of time to work on that particular issue. But I plan to explore the second option as well. The final result has created quite a stir, so I'm sure I'll have many more chances to play around with it.

Also, the list of items had to appear on another layout/table because my order form took up most of the screen space and a different set of items would be available for each new order based on the manufacturer. Customers can create multiple orders, but because of how the discounts are applied, they have to submit a different order for each manufacturer. So the first step to creating an order is picking or entering the manufacturer number and then they can click a link to see a new window with all the products available for that order. That is where they get to add them.

The modified version also had to take into account that some items that were part of a larger "kit" could not be ordered separately and the powers that be also wanted the customer to be able to input a quantity at the same time they were adding the item to their invoice. But they also needed to be able to add the item without a quantity. However, I didn't want any quantity the customer entered to stay in the item record and show up when the customer adds that same item to other invoices.

So here's the final scripts that seems to be working:

If ( Related Products 2::KITCODE = "C" ; 1 ; 0 )

Show Custom Dialog ["Kit Item"; "This item is part of a kit. Please select the Main Kit Item listed above it instead."]

Clear [select; Related products 2::Quantity Products]

Else

Set variable [$itemnumber; Value:Related Products 2::Item Number]

Set variable [$Quantity_Products; Value:Related Products 2::Quantity Products]

Set variable [$invoiceID; Value:Orders::Order No]

Show Custom Dialog ["Item Added To Your Order"; If ( IsEmpty ( Related Products 2::Quantity Products ) ; "The following item has been added to Order No. " & Orders::Order No & ":¶"

& Related Products 2::Item Number & ¶ &

"Go to your program order to delete this item or specify a quantity." ; "The following has been added to Order No " & Orders::Order No & ":¶"

& $Quantity_Products & " of Item Number " & Related Products 2::Item Number & ¶ &

"Go to your program order to delete this item or change quantities.")

Clear [select; Related Products 2::Quantity Products]

Freeze Wndow

Go to Layout ["Line Items" (Line Items)]

New Record/Request

Set Field [LIne Items::Order No; $invoiceID]

Set Field [LIne Items::Item Number; $itemnumber]

Set Field [LIne Items::Quantity; $Quantity_Products]

Go to Layout [original layout]

Commit Record/Requests []

End If

In a nutshell, the kit items are all labelled, so hopefully nobody will try to order them individually. But if they do, they get an error message saying they can't order the item by itself and FileMaker clears any quantity they entered and returns to the list of items.

Otherwise, FileMaker will set variables for the invoice ID, the item number and the quantity they entered. Then it will clear the quantity entered and give the user a message that says the item was added at a certain quantity, or it will prompt them to go to the invoice to add a quantity. Then it goes to the Line Items layout and creates a new record with all the variable data. Then returns to the original layout.

Once the item number is entered into the Line Items table, that triggers several other fields to pull in additional data from a products table that also populate on the order form.

I plan to post this question separately under a different thread, but if anyone has any suggestions for how I can disable the quantity field for just "kit" items (any item where the value of the KITCODE field = "C") so the customer can't enter a quantity at all for just those items, that might be preferable to having them enter a value just to have it cleared.