The global field sounds to me like the right way to go, but you need a relationship to use with it in order to check to see if a given product has been added to the OrderDetails table for that Invoice.
Define this relationship:
Products::gCurrentOrderID = SelectedOrderDetails::OrderID AND
Products::ProductID = SelectedOrderDetails::ProductID
SelectedOrderDetails is an additional occurrence of OrderDetails that you create in Manage | Database | relationships by selecting OrderDetails, then clicking the button with two plus signs to duplicate it. You can doubleclick OrderDetails 2 to bring up a dialog box where you can rename it to match my example.
The script you use to open the Product List window can also use Set Field to copy the current order's OrderID into the global field. (gCurrentOrderID, must be defined in Products and must have global storage selected in field options.)
Your conditional format expression can then be:
Another, similar approach you may want to think about is to list the products in a portal on your Orders layout. That way you can include a search field above the portal and use scripting to filter the portal by the text entered into the search field. This can be a useful way to search out products by a keyword in their description field. (The list can still be sorted to your specifications and the conditional formatting discussed here still works.)
I'm Soo sorry I didn't reply any sooner.
These mails arrive in my hotmail and I rarely ever look in there.
I missed this one and I had almost given up on this problem.
I implemented your solution and it workd really great.
I only have a small imperfection now.
My products list opens up in a new window so you can see your invouce in the background.
The idea is you can add products and see them as they are added on the invoice.
Only the portal is not updating as I add the products.
I have to manually go back to the invoice and click in it somewhere to update it and see the products pop up in the portal.
I would like to find a way to update that automatically.
I would be tempted to ude "refresh window" and "Flush cashed join results"
But I recently read an article that talked about using a cartesion join instead of a cashe flush.
Because it would work better and faster on a network.
P.S. Thanks again for your help!
As a test I've added some "Refresh Window, Flush cashed join results" steps in my two scripts.
- One script to open the new product list window.
- one script to add a new item
Now when I add a product in the new window list I see it pop up in the portal in the background immediatly. So that problem is solved.
Only if I delete an item from my portal. And i go back to my new window products list that item is still highlighted.
When I check in the Orderdetails table the recrds are correctly removed. But it seems like the change doesn't take effect immediatly.
Anyone have any thoughts?
Using two windows here does complicate the interface. I had imagined both portals on the same layout.
What conditional format expression are you using to highlight the selected items?
Actually there is no reason to use a new window when the highlighting works. So Maybe it's indeed smarter to just let all of this happen in the same window.
The conditional formatting expression I use is simple:
TblProducts::ProductId = SelectedOrderDetails::ProductID
The reason however I'm using a new window is because my invoice layout has a few tabs.
When I use a new window the user doesn't lose the current tab.
(I haven't found a script step yet that allows you to open a layout and open a certain tab.
Not saying you shouldn't use a floating window for this, it's just that this is a detail that requires some extra care when using so you have to design your scripts accordingly.
BTW, if you given each tab panel an object name, go to object can be used to bring a specific tab panel to the front.
With your current setup, you delete an item from a portal in the order window and the selection window doesn't update to show the change?
You may need to script the delete portal row button to run a script that deletes the portal row, then includes this code to update the other window:
Select Window [//select the window of products]
Refresh Window [//use flush cached join results if you need it, but I don't think you do here]
I just tried this and it works like PERFECTLY :)
How did you become such a FM genious?
And thanks for the trick on using GoToObject. That's an interesting one :) Will use that for sure :)
I didn't select "Flush Cashed Join Results" and it looks like it works this way.
I am a little hesitant to use it since I read an article on using a cartesion join to update calculations etc. The author points out that flushing does have an impact on databases served on a network.