Best Of

Re: GL Plug in Transaction Saved Search

@Richard James Uri-Oracle - TY for your response. I did find the Custom GL Plug in table in saved searches. This actually pulled the data I needed.

KNason76

1

Re: Closing Year End Journals and P&L Accounts movement in GL and TB (detailed)

Thank you Angela for the detailed insight and highlighting the points in the article. 😊

Hi @Elina Kourouzidou, kindly check out Angela's comments above and let us know if you have further inquiries :)

Clarisa De Torres-Oracle

1

Re: Date/Time Math in SuiteQL

Thank you very much for your engagement and collaboration in keeping this thread going!

Sequel Developer

1

NSC | Transaction Saved Search > Work Order > Shows Assembly and Components in the same row

Scenario

User needs to create a Transaction Saved Search for Work Orders and needs to show the Assembly Item (Main Line) and Component Items (Item Line) in the same row per Work Order. Using the Item field (field ID: {item}) pulls both the Assembly and Component lines in separate rows.

Solution

1.) Navigate to Lists > Search > Saved Searches > New

2.) Select Transaction

3.) Add the Criteria:

Type = Work Order

4.) In the Results tab, add the following:

Document Number | Summary Type: Group
Formula (Text) | Summary Type: Maximum | Label: Assembly Item
- Formula: CASE WHEN {mainline} = '*' THEN {item} || ' (' || {quantity} || ')' END
Formula (HTML) | Summary Type: Maximum
- Formula: REPLACE(NS_CONCAT(CASE WHEN {mainline} != '' AND {iswip} = 'F' THEN {item} || ' (' || {quantity} || ')' WHEN {mainline} != '' AND {linesequencenumber} > 1 AND {iswip} = 'T' THEN {item} || ' (' || {quantity} || ')' END),',','<br>')

5.) Enter a Search Title, then click Save & Run

The Results page should show something like this:

Notes:

If you export this as a CSV or XLS file, the <br> code will be exported.
You can also modify the formula to show another Quantity field as necessary. Example, use {bomquantity} instead of {quantity} to show the value for the BOM Quantity column instead of the Quantity column.

Related Threads:

-----

If you find this information useful, let us know by reacting or commenting on this post!

Ivy Lopez-Oracle

1

Re: CASE WHEN and formula issue applying transaction type used.

Hey there @User_NG5TG,

Kindly try this:

SUM(CASE WHEN {item.type} = 'Inventory Item' THEN {estgrossprofit} ELSE 0 END)/NULLIF(SUM(CASE WHEN {item.type} = 'Inventory Item' THEN {amount} ELSE 0 END),0)

Also, for your formula GP% for Non-inventory Items, I noticed in your screenshot that you used a capital letter I. It should be a small letter only, since the formula is case sensitive. Hence, the formula for Non-inventory Items is should be:

SUM(CASE WHEN {item.type} = 'Non-inventory Item' THEN {estgrossprofit} ELSE 0 END)/NULLIF(SUM(CASE WHEN {item.type} = 'Non-inventory Item' THEN {amount} ELSE 0 END),0)

Let me know if this works for you. 😊

Ivy Lopez-Oracle

1

Re: Can we set the NACHA File and Positive Pay File to be locked or only certain people can download it?

Hi @Jervin Nicholas Teopengco-Oracle I will be testing this in my demo account and will update you. Thank you!

User_Q3A12

1

Re: MPN not pulled into Purchase Order

Yes, the one used in our Purchase Orders is the custom MPN (searchable), whose data seems to be stored somewhere other than the Item file. What we want is to be able to have the data from the Item file's MPN field pulled in and any changes to it to be saved to the Item file

Ken Davies

1

Re: MPN not pulled into Purchase Order

Hi @Ken Davies,

Based on your screenshot, the MPN (searchable) field is a Custom Item Field which means it's only displayed within the Item record. Since the Store Value field is checked for this field, the values for this is not sourced. It's entered by the User (Manually via UI, CSV Import, Workflow, etc.).

As for your Purchase Orders, if this is the field you are referring to (MPN):

This is not the MPN (searchable) field. You can check this field via Customization > Lists, Fields, & Records > Transaction Line Field. If I'm not mistaken, this is the field instructed by Tris in the previous comments.

Please correct me if I'm wrong, by Item file, do you mean Item record? If yes, the values of the Item record cannot be updated by changing the values on the Item sublist of the Purchase Orders. Since all changes to the Item must be changed in the master record.

Thanks!