Hello, I'm trying to use a formatted search in the Additional Expenses area (window?) of an A/R Invoice, and it's not working for me.
I'm trying to pull a "shipping" amount from a user-defined table based on the Invoice's Tracking Number (OINV.TrackNo) field. I haven't yet figured out how to make any reference to the Invoice that the Additional Expenses are based on. I've tried using $http://OINV.TrackNo or $http://$251.1.0 but both of those variations give an error. I can't use WHERE OINV.DocNum = 12345 because the invoice hasn't been "added" yet by this point, and I also can't figure out how to get the Invoice number (12345) from inside the Additional Expenses. INV3.DocEntry is also not populated by this point, so I can't match that to an OINV entry either.
In a related vein, I haven't been able to force any of my Additional Expenses lines to automatically update or run a formatted search when another field changes. I've set a formatted search on the Amount column, with "Search by Saved Query", picked a query, and then ticked Auto Refresh, chosen "When Field Changes" and selected the "Tracking Number" field, and selected either "Refresh Regularly" or "Display Saved Values" but none of the values in additional expenses will update when tracking number (or any of various other fields I've tested with) is changed.
I've used this basic Query as a Formatted Search to pull values into the Amount column based on which row the cursor is in, but it only works if I open Addtional Expenses, place the cursor in a column, and hit Shift-F2:
SELECT Amount =
CASE $http://$3.1.0 -- <-- this works to tell me the expense type for the cursor row in additional expenses<BR> WHEN 1 THEN 0.00
WHEN 2 THEN 3.50
WHEN 3 THEN -3.50
WHEN 4 THEN (SELECT T0.BaseRef FROM INV3 T0 WHERE T0.DocEntry = $http://INV3.DocEntry) -- <-- doesn't work; NULL<BR> WHEN 5 THEN 15
WHEN 6 THEN 30
Any help on working with Additional Expenses (and automatically populating them) would be greatly appreciated! If you need clarification I can do that too, I wasn't sure how best to describe the problem. I'm using SBO 2005a SP00.
-Lance Rocker (for Brett)
(edited for formatting)