4 Replies Latest reply on May 5, 2008 12:55 PM by Brett Hamel

    Additional Expenses with formatted searches

    Brett Hamel

      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
        ELSE 0.00

      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)

        • 1. Additional Expenses with formatted searches
          Matt Roberts


          We actually had the people who implemented SBO create, as part of a custom add-on, something to automatically populate additional expenses with shipping.

          We use UPS almost exclusively for our ground packages, and UPS actually has an XML-based service where you can send the weight, zip code, and service type (Next Day, Ground, etc.) to them and receive back exactly how much this will cost. So we have a button that says "Calculate shipping," and it will tell us how much it costs to ship via UPS and automatically populate this into the Add'l Expenses line - before the sales order is added.

          This would probably also work for you to put things in the Invoice add'l expenses before it is added. Of course, it is dependent upon you having correct weights for all your products in the item master file, or you'll really give yourselves the shaft with shipping expenses.

          USPS does not have this, and I am not certain about FedEx or any other shipping services.

          Alternately: if you are making an invoice...hasn't a delivery already been created? And doesn't the tracking number link up with that delivery (there is a place for TrackNo on both ODLN and OINV)? If so, couldn't you just pull the calculation for the current (not-submitted) invoice based on the tracking number for the base delivery note? I know that we currently upload all our tracking numbers and number of boxes shipped from UPS to a UD table, and every evening this table then transfers these to a view which links to the TrackNo fields on ODLN and OINV (through a series of complex joins which is actually linked through the pick list number, since we use Pick & Pack Manager).

          • 2. Additional Expenses with formatted searches
            Brett Hamel

            Thanks for your response Matt!

            We also had our implementors install a 3rd party (B1Dev?) add-on for those same UPS shipping estimates, but we found that estimates don't work well for our product. About 70% of our orders go out in multiple packages, and ~20% end up being oversize. Predicting how the shipping guy will package up a shipment turned out to be nearly impossible over half the time.

            So we need to bill the customer based on what the shipping cost actually was, after the shipment has happened.

            Your alternate suggestion is also a good one, but we don't currently do delivery documents because of the added hassle. Even if we did though, I don't think the Base ODLN document would be any more "accessible" from Additional Expenses (now "Freight Charges" in SP01) than the Base ORDR document was. And pulling the UPS Shipping cost into a delivery note would entail all the same problems I'm having with pulling them into Invoices.

            Any other ideas for pulling a value into a marketing document's Frieght Charges area, based on data from a base marketing document -- without resorting to a costly and complicated SDK based solution?

            • 3. Additional Expenses with formatted searches
              Matt Roberts

              Yes, but you don't need to store the cost of the shipment in the ODLN table to get at it from the invoice.

              It's quite possible to make a UD table where you have UPS Worldship send in the data for the shipment (whatever you want: tracking number, # boxes, actual shipment cost, etc.).

              Of course, if you make a user-defined table, you MUST create a view of that table for Worldship to access - UPS Worldship will not allow you to connect to an ODBC data source that has an "@" symbol in it.

              You should then be able to access this UD table for whatever purposes you want, and if you've exported the order # or some other meaningful reference number for you into whatever reference number for the package, you should be able to draw that back out and relate data for your invoice to it.

              There are also some commercial solutions, like Ibolt, that have some built-in connectivity between stuff like Worldship and SAP B1, but that may be like telling you to buy a bulldozer to move a table across the room - Ibolt is extremely powerful (so far it's working great for us), and may be a bit pricey just for this one thing.

              • 4. Additional Expenses with formatted searches
                Brett Hamel

                Matt, I'm pretty sure I understand what you're suggesting, but pulling data (ANY data) into the Freight area of an Invoice (or delivery) just isn't working for me. At least not through using a Formatted Search.

                We already have the UPS UD table & view in place, through a 3rd party solution (CDI?). It's working quite well, the data is definitely accessible from SBO keyed off of the Sales Order number. So the UPS side of things looks prety solid. My issue is the Freight / Additional Expenses side of things. I can't pull ANYTHING useful into additional expenses. Even static values ("10", "15", etc. . .) only work if I press Shift-F2 in the field.

                And since my UD Table of UPS Shipment data is keyed off of Sales Order # (or tracking number, if that's easier), I need to be able to "know" which Sales Order I'm referencing when I'm in an Invoice. In normal fields and User Defined fields, I can access that just fine through $[http://OINV.whatever|http://OINV.whatever] or $[http://$2.1.0|http://$2.1.0] type variables -->
                . . .  But that doesn't work for me in the Freight area. I'm about ready to give up on the Freight area, but it's the only place to keep proper track and separation of Freight charges.

                At this point, I'm kind of resigned to believing that Freight can only be updated via database values through the SDK or by direct SQL Database manipulation AFTER the document has been completely entered. It cannot be updated through the UI using Formatted Search - only through manual data entry. I wish it weren't so, and I consider it a BUG, but I don't think there's much else I can do.

                In that vein, I'm taking a look at iBolt to see if it'll add enough value for us to be worth the investment. :)