[JCirocco Contributions: 0Level 1☆]

Banging my head against the wall here. Trying to find a way to have a search return the Selling Price from a sales order as well as the actual COGS cost from a fulfillment. No matter which option I have tried I either get the Sales Order total amount ("Created From… Amount") or NULL (Applying or Applied To Transaction… Amount.

I even tried creating a Custom Transaction Column field that sourced from the sales order but if will not show on the fulfillment line.

Is this even doable? Should I do the Search from the Sales order to start?

Appreciate all the expert help.
This is a cached copy. Click here to see the original post.

[erictgrubaugh Contributions: 0Level 1☆]

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.

[JohnCCole Contributions: 0Level 1☆]

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.

[JCirocco Contributions: 0Level 1☆]

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.

[JCirocco Contributions: 0Level 1☆]

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

[JohnCCole Contributions: 0Level 1☆]

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.

[JohnCCole Contributions: 0Level 1☆]

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.

[JCirocco Contributions: 0Level 1☆]

John, yes, finding out both…

[michoel Contributions: 0Level 1☆]

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

[JCirocco Contributions: 0Level 1☆]

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.

[michoel Contributions: 0Level 1☆]

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).

[JCirocco Contributions: 0Level 1☆]

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

[JCirocco Contributions: 0Level 1☆]

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.

[Author]

Posts