I don't know of any way to do this off the top. Searches don't have a nice way to say "find me the matching line item on this other transaction" (that I'm aware of). You might have to do something like a Suitelet that merges two search result sets.

Pretty confident Eric is right on this one. I needed to have the selling unit price on the item fulfillment for purpose of declaring value on commercial invoices for international shipments, so I added a custom column field.

JohnCCole , I tried to create a custom transaction column field, store data was off but for the life of me I could not find a field to source from (Item Rate and Amount didn't work). To make matters worse, when I had it as Store Data False, could not make it show on fulfillment form even though it was checked to be displayed.

Oh, and another interesting bit… When viewing the XML of the Fulfillment, there are 2 fields for each item the DO link back.

<orderdoc>345989</orderdoc>

<orderline>2</orderline>

Orderdoc is the internal ID of the Sales Order

Orderline is the LineID of the Sales Order

Originally posted by JCirocco

View Post

Oh, and another interesting bit… When viewing the XML of the Fulfillment, there are 2 fields for each item the DO link back.

<orderdoc>345989</orderdoc>

<orderline>2</orderline>

Orderdoc is the internal ID of the Sales Order

Orderline is the LineID of the Sales Order

Yes there is map and this probably what Eric is referring to when doing a Suitelet, but don't think these line columns are available in search.

Originally posted by JCirocco

View Post

JohnCCole , I tried to create a custom transaction column field, store data was off but for the life of me I could not find a field to source from (Item Rate and Amount didn't work). To make matters worse, when I had it as Store Data False, could not make it show on fulfillment form even though it was checked to be displayed.

Your correct the sourcing doesn't work. I just have a userevent script on the fulfillment record that uses the orderline value and finds the corresponding line on the sales order to get the price.

John, yes, finding out both…

It looks like this is in fact possible.

Every item in a Fulfillment record actually has three result rows in a Saved Search (see SuiteAnswers # 27017 for more details). In order to get both the information from the line that links back to the Sales Order and the COGS line, you need to group the results by increments of 3 using a formula such as "FLOOR({linesequencenumber}/3)".

Then you can use a formula to retrieve the amount from the COGS line e.g. CASE WHEN({accounttype} LIKE 'Cost of Goods Sold') THEN {amount} ELSE 0 END

Michoel,

Thanks. If you look at initial question, I am trying to get the Sales Amount from the Sales Order so I can calculate actual Profit at time of fulfillment.

Originally posted by JCirocco

View Post

Thanks. If you look at initial question, I am trying to get the Sales Amount from the Sales Order so I can calculate actual Profit at time of fulfillment.

That's exactly what this search should do. (The last row in the results).

Sorry, I was not paying attention. I will give it a shot…

Originally posted by michoel

View Post

That's exactly what this search should do. (The last row in the results).

That rocked! I am not sure why I could not get the Applied To working before (unless I was starting at the SO?) but it is exactly what I needed.

Thanks again and sorry for the confusion.