Solved

Conditional Formatting for SO Details Grid


Userlevel 2
Badge

Hello!

I am attempting to add some conditional formatting on the SO Order Details grid where the SO Lines are viewable when looking at a Sales Order.

I have implemented a customization on the SO Order Entry graph which calculates the mark up of each line amount over total average cost of the line, which then puts this number into a custom field on an SO Line DAC Extension.

I want to highlight the SO Line rows in red where the the custom field(usrMarkupPct) is less than 0.1.

Everything I am finding says to edit the ASPX file of the Sales Order screen but I am not sure that is the best way to do this.

Does anyone have any experience with applying formatting conditionally to SO Lines?

As always, any help will be greatly appreciated!!

icon

Best answer by Denham 7 June 2023, 20:27

View original

23 replies

Userlevel 7
Badge +5

I just happened to be looking at this post on SO earlier today. I haven’t tried it:

https://stackoverflow.com/questions/70840294/how-can-i-get-row-colors-to-change-in-standard-grid-based-on-data-criteria

Userlevel 2
Badge

@Django 

I believe I was actually looking at this post earlier today as well. The thing is when I try to Edit the ASPX in the customization project editor for the SO screen, it does not seem to save my changes when I insert this script:

 

<script type="text/javascript">
function HighlightLines ()
{
if(px_all && px_all["grid"] && px_all["grid"].rows)
{
let lines = px_all["grid"].rows.items;
for(let i=0;i<lines.length;i++)
{
let currentLine=lines[i];
if(currentLine.getCell("SOLine__UsrMarkupPct").getValue() < 0.1)
{
currentLine.style = 'background-color: #FFCCCC'; // light red color
currentLine.repaint();
}
}
}
}
</script>

 

Badge +11

In my experience, the visual editor is extremely temperamental with inserting scripts.

Hopefully the new UI will resolve that.

Userlevel 7
Badge +12

I have doen this by just adding the JavaScript Control to the Details and then adding the script in there. Works perfectly.

 

Userlevel 7
Badge +8

You can do grid styling for rows and cells in C# as well. Your best reference is EP503010.aspx.cs that you can use to get the idea. Here I have copied the code but of course, you have it in your instance. 

using System;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using PX.Objects.EP;

public partial class Page_EP503010 : PX.Web.UI.PXPage
{
protected void Page_Load(object sender, EventArgs e)
{
Style escalated = new Style();
escalated.ForeColor = System.Drawing.Color.Red;
this.Page.Header.StyleSheet.CreateStyleRule(escalated, this, ".CssEscalated");
}

protected void Page_Init(object sender, EventArgs e)
{
Master.PopupHeight = 460;
Master.PopupWidth = 800;
}

protected void grid_RowDataBound(object sender, PX.Web.UI.PXGridRowEventArgs e)
{
EPApprovalProcess.EPOwned item = e.Row.DataItem as EPApprovalProcess.EPOwned;
if (item == null) return;
if (item.Escalated == true)
e.Row.Style.CssClass = "CssEscalated";
}
}

 

Userlevel 2
Badge

@dcomerford 

I am trying to add the javascript control under the Details tab, but when I drag in the control, it doesnt seem to be doing anything. Not sure if I’m doing something wrong?

If I cant get this to work, I am going to try the method @aaghaei is suggesting. 

I appreciate all the replies, this is very helpful.

Userlevel 7
Badge +8

I am going to caution you with JS especifically in grids. The browsers have different behavior in identifying and addressing controls so when a code works fine on Chrome it might not work on Firefox for example or other browsers and vice versa. I had requirements to freeze some grid columns in Acumatica to display them when the user horizontally scrolling and Firefox drove me crazy to make it work. 

Userlevel 2
Badge

Since multiple people are recommending to avoid JS, I am going to try to do this in C#. I have written the code below, and its validating successfully. Going to get it published and report back.

 

using System;
using PX.Data;
using PX.Objects.SO;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace EISOLineMarkup
{
public partial class Page_SO301000 : PX.Web.UI.PXPage
{
protected void Page_Load(object sender, EventArgs e)
{
Style lowMarkup = new Style();
lowMarkup.BackColor = System.Drawing.Color.FromArgb(255, 204, 204); // Light red color
this.Page.Header.StyleSheet.CreateStyleRule(lowMarkup, this, ".CssLowMarkup");
}

protected void grid_RowDataBound(object sender, PX.Web.UI.PXGridRowEventArgs e)
{
SOLine line = e.Row.DataItem as SOLine;
if (line == null) return;

var markupPct = line.GetExtension<SOLineMarkupExtension>().UsrMarkupPct;
if (markupPct != null && markupPct < 0.1m)
e.Row.Style.CssClass = "CssLowMarkup";
}
}
}

 

Userlevel 7
Badge +12

Drag and Drop the Javascript on to the Details or similiar dont drop it on the Grid

 

Userlevel 2
Badge

After publishing my code, it appears to not be doing anything. I am not sure if this is because it is running prior to the graph extension running (also a row selected event), so there is no data in the UsrMarkupPct field to view.

Userlevel 7
Badge +8

You should do it in your GRAPH extension. Not sure how and where you are putting your code. In Your graph ext you will need to override the graph initialization like 

 

public override void Initialize()
{
Page page = HttpContext.Current?.Handler as PXPage;
if (page != null)
{
page.Load += Page_Load;
}
}

Then in your Page_Load you develop your logic like 

private void Page_Load(object sender, EventArgs e)
{
Page page = (Page)sender;

// Define your style(s)

// Find your control by its ID (in your case your gridID) and perform binding
PX.Web.UI.PXGrid gridWithStyle = (PX.Web.UI.PXGrid)ControlHelper.FindControl("myGridID", page);
if (gridWithStyle != null)
{
gridWithStyle.RowDataBound += (object grdSender, PXGridRowEventArgs erdb) =>
{
DAC data = erdb.Row.DataItem as DAC;

// Perform Styling
}
}
}

 

Userlevel 2
Badge

@aaghaei 

Here is my code. I have tried adding the styling portion to the graph extension. But again, it does not seem to be doing anything. Any help with modifying this code will be appreciated.

using System;
using System.Web;
using System.Web.UI;
using PX.Web.UI;
using PX.Data;
using PX.Common;
using PX.Objects.SO;
using PX.Objects.IN;

namespace EISOLineMarkup
{
public class SOOrderEntry_Extension : PXGraphExtension<SOOrderEntry>
{
#region Event Handlers

protected virtual void SOLine_RowSelected(PXCache cache, PXRowSelectedEventArgs e)
{
if (e.Row is SOLine soline)
{
SOLineMarkupExtension solineMarkup = PXCache<SOLine>.GetExtension<SOLineMarkupExtension>(soline);

if (solineMarkup != null)
{
decimal? avgCost = GetAverageCost(soline.InventoryID);

if (avgCost != null && avgCost != 0m && soline.OrderQty != 0m)
{
decimal? curyLineAmt = soline.CuryLineAmt != 0m ? soline.CuryLineAmt : 0.01m;
decimal? markupPct = (curyLineAmt - (avgCost.Value * soline.OrderQty)) / (avgCost.Value * soline.OrderQty);
cache.SetValueExt<SOLineMarkupExtension.usrMarkupPct>(soline, markupPct);
}
else
{
cache.SetValueExt<SOLineMarkupExtension.usrMarkupPct>(soline, 0m);
}
}
}
}

#endregion

private decimal? GetAverageCost(int? inventoryID)
{
INItemCost itemCost = PXSelect<INItemCost,
Where<INItemCost.inventoryID, Equal<Required<INItemCost.inventoryID>>>>
.Select(Base, inventoryID);

return itemCost?.AvgCost;
}

public override void Initialize()
{
Page page = HttpContext.Current?.Handler as PXPage;
if (page != null)
{
page.Load += Page_Load;
var css = new LiteralControl("<style>.myGridRowHighlight { background-color: #FFCCCC; }</style>");
page.Header.Controls.Add(css);
}
}

private void Page_Load(object sender, EventArgs e)
{
Page page = (Page)sender;

PXGrid solineGrid = ControlHelper.FindControl("grid", page) as PXGrid;
if (solineGrid != null)
{
solineGrid.RowDataBound += (object grdSender, PXGridRowEventArgs erdb) =>
{
SOLine soline = erdb.Row.DataItem as SOLine;

if (soline != null)
{
SOLineMarkupExtension solineMarkup = Base.Transactions.Cache.GetExtension<SOLineMarkupExtension>(soline);

// Acquire the value of usrMarkupPct field through the PXCache object
var markupPct = (decimal?)Base.Transactions.Cache.GetValue<SOLineMarkupExtension.usrMarkupPct>(solineMarkup);

if (markupPct != null && markupPct < 0.1m)
{
erdb.Row.Style.CssClass = "myGridRowHighlight";
}
}
};
}
}
}
}

 

Userlevel 7
Badge +8

Why are you manipulating Cache in RowSelected? It is not recommended at all? try doing it in FieldSelecting of “usrMarkupPct” for e.ReturnValue. This way you have value in the row before being displayed. I guess this is the problem after data is loaded you start manipulating and platform can not tie it back to the controls.

A friendly suggestion on a side note. Try to avoid using complicated pointless syntaxes/structures. For example

SOLineMarkupExtension solineMarkup = Base.Transactions.Cache.GetExtension<SOLineMarkupExtension>(soline);

could be 

SOLineMarkupExtension solineMarkup = soline.GetExtension<SOLineMarkupExtension>();

 

or 

var markupPct = (decimal?)Base.Transactions.Cache.GetValue<SOLineMarkupExtension.usrMarkupPct>(solineMarkup);

could be

decimal? markupPct = solineMarkup.UsrMarkupPct;

Userlevel 2
Badge

I am heavily utilizing ChatGPT to help me write the code, as you can probably tell, I am not very experienced with this.

Here is what it generated after I entered your suggested improvements.

using System;
using System.Web;
using System.Web.UI;
using PX.Web.UI;
using PX.Data;
using PX.Common;
using PX.Objects.SO;
using PX.Objects.IN;

namespace EISOLineMarkup
{
public class SOOrderEntry_Extension : PXGraphExtension<SOOrderEntry>
{
#region Event Handlers

protected virtual void _(Events.FieldSelecting<SOLine, SOLineMarkupExtension.usrMarkupPct> e)
{
if (e.Row is SOLine soline)
{
SOLineMarkupExtension solineMarkup = soline.GetExtension<SOLineMarkupExtension>();

decimal? avgCost = GetAverageCost(soline.InventoryID);

if (avgCost != null && avgCost != 0m && soline.OrderQty != 0m)
{
decimal? curyLineAmt = soline.CuryLineAmt != 0m ? soline.CuryLineAmt : 0.01m;
decimal? markupPct = (curyLineAmt - (avgCost.Value * soline.OrderQty)) / (avgCost.Value * soline.OrderQty);
e.ReturnValue = markupPct;
}
else
{
e.ReturnValue = 0m;
}
}
}

#endregion

private decimal? GetAverageCost(int? inventoryID)
{
INItemCost itemCost = PXSelect<INItemCost,
Where<INItemCost.inventoryID, Equal<Required<INItemCost.inventoryID>>>>
.Select(Base, inventoryID);

return itemCost?.AvgCost;
}

public override void Initialize()
{
Page page = HttpContext.Current?.Handler as PXPage;
if (page != null)
{
page.Load += Page_Load;
var css = new LiteralControl("<style>.myGridRowHighlight { background-color: #FFCCCC; }</style>");
page.Header.Controls.Add(css);
}
}

private void Page_Load(object sender, EventArgs e)
{
Page page = (Page)sender;

PXGrid solineGrid = ControlHelper.FindControl("grid", page) as PXGrid;
if (solineGrid != null)
{
solineGrid.RowDataBound += (object grdSender, PXGridRowEventArgs erdb) =>
{
SOLine soline = erdb.Row.DataItem as SOLine;

if (soline != null)
{
SOLineMarkupExtension solineMarkup = soline.GetExtension<SOLineMarkupExtension>();
decimal? markupPct = solineMarkup.UsrMarkupPct; // Here

if (markupPct != null && markupPct < 0.1m)
{
erdb.Row.Style.CssClass = "myGridRowHighlight";
}
}
};
}
}
}
}

 

Userlevel 7
Badge +8

Jesus, I do not think you will be able to get an answer from ChatGPT on Acumatica. I tried to see how it responds and I got zero correct answers. It mishmashes everything and gives you some mumbo jumbo. Anything you add to it will say sorry you are right. Don’t waste your time on it. If you do not know how to code in Acumatica, then you will need to reach out to a partner or independent developer.

Userlevel 2
Badge

Being that I have a Plus subscription, I am able to direct it to search the internet for solutions.

I understand you may not believe that it is accurate, and in some cases I do agree, much of the time though I am able to get it to provide an accurate base to work off of and then hand to a developer for final touches. There are other customizations which are simpler that we have implemented which were completely written by ChatGPT.

Userlevel 2
Badge

I am heavily utilizing ChatGPT to help me write the code, as you can probably tell, I am not very experienced with this.

Here is what it generated after I entered your suggested improvements.

using System;
using System.Web;
using System.Web.UI;
using PX.Web.UI;
using PX.Data;
using PX.Common;
using PX.Objects.SO;
using PX.Objects.IN;

namespace EISOLineMarkup
{
public class SOOrderEntry_Extension : PXGraphExtension<SOOrderEntry>
{
#region Event Handlers

protected virtual void _(Events.FieldSelecting<SOLine, SOLineMarkupExtension.usrMarkupPct> e)
{
if (e.Row is SOLine soline)
{
SOLineMarkupExtension solineMarkup = soline.GetExtension<SOLineMarkupExtension>();

decimal? avgCost = GetAverageCost(soline.InventoryID);

if (avgCost != null && avgCost != 0m && soline.OrderQty != 0m)
{
decimal? curyLineAmt = soline.CuryLineAmt != 0m ? soline.CuryLineAmt : 0.01m;
decimal? markupPct = (curyLineAmt - (avgCost.Value * soline.OrderQty)) / (avgCost.Value * soline.OrderQty);
e.ReturnValue = markupPct;
}
else
{
e.ReturnValue = 0m;
}
}
}

#endregion

private decimal? GetAverageCost(int? inventoryID)
{
INItemCost itemCost = PXSelect<INItemCost,
Where<INItemCost.inventoryID, Equal<Required<INItemCost.inventoryID>>>>
.Select(Base, inventoryID);

return itemCost?.AvgCost;
}

public override void Initialize()
{
Page page = HttpContext.Current?.Handler as PXPage;
if (page != null)
{
page.Load += Page_Load;
var css = new LiteralControl("<style>.myGridRowHighlight { background-color: #FFCCCC; }</style>");
page.Header.Controls.Add(css);
}
}

private void Page_Load(object sender, EventArgs e)
{
Page page = (Page)sender;

PXGrid solineGrid = ControlHelper.FindControl("grid", page) as PXGrid;
if (solineGrid != null)
{
solineGrid.RowDataBound += (object grdSender, PXGridRowEventArgs erdb) =>
{
SOLine soline = erdb.Row.DataItem as SOLine;

if (soline != null)
{
SOLineMarkupExtension solineMarkup = soline.GetExtension<SOLineMarkupExtension>();
decimal? markupPct = solineMarkup.UsrMarkupPct; // Here

if (markupPct != null && markupPct < 0.1m)
{
erdb.Row.Style.CssClass = "myGridRowHighlight";
}
}
};
}
}
}
}

 

After some further tweaking of this code, the customization is now working.

Userlevel 7
Badge +8

simple stuff maybe but without the critical structure I provided you would never have the chance to get the right code from ChatGPT. 

Userlevel 2
Badge

You are correct. Thank you for that. I can mark your answer as best answer.

Userlevel 7
Badge +8

That was not my point. My point is that you are saying ChatGPT does the coding then you fine-tune it. It really gives nothing other than schema. Maybe in the future but not yet.

Userlevel 2
Badge

With ChatGPT alot of specific information has to be fed to it in order for it to produce usable results. It is still also useful for learning to code.

Userlevel 5
Badge +1

Hi all,

Maybe I am wrong, but I want to fix a little)) As I know FieldSelecting event is invoked only one time, when user select key field of table on UI. Also this event is triggered by GI engine, and I try customize this event very rarely.

So,

  1. MarkUp is custom, not key field. 
  2. MarkUp depends from QTY and Amount (also not key fields)
  3. if user updates QTY or Amount, the MarkUp will be the same in cache and UI
  4. First - write method GetMarkUp, that calculate new markUP and return decimal value
  5. Invoke this method and SetValue<MarkUp>(row); (without Ext) to markUp field using two events: FieldUpdating(QTY), FieldUpdating(Amout)

 maybe FieldVerifying event also will work and you could control markUp and RaiseExceptionHandling, If we have many fields for updating, it is better to use FieldUpdated(IFields) event

ChatGPT is a cool feature in IT, but with small experience😉 

Userlevel 2
Badge

Hey everyone, 

So the code below is working and rows are being highlighted correctly. The problem now is that I cant filter the grid based on values performed in the calculation. I realize that this is because the calculated values arent actually being comitted to the database.

I now need some guidance on how to get the filtering working. Not sure if the code has to be completely re-written to use a different event handler. The field has to be able to update as changes are made to any of the SO Line fields which are being used to calculate the markup percentage.

 

using System;
using System.Web;
using System.Web.UI;
using PX.Web.UI;
using PX.Data;
using PX.Common;
using PX.Objects.SO;
using PX.Objects.IN;
using PX.Objects.CS;

namespace EISOLineMarkup
{
public class SOOrderEntry_Extension : PXGraphExtension<SOOrderEntry>
{
#region Event Handlers

protected virtual void SOLine_RowSelected(PXCache cache, PXRowSelectedEventArgs e)
{
if (e.Row is SOLine soline)
{
SOLineMarkupExtension solineMarkup = soline.GetExtension<SOLineMarkupExtension>();
SOLineClearanceExtension solineClearance = soline.GetExtension<SOLineClearanceExtension>();

if (solineMarkup != null && solineClearance != null && (solineClearance.UsrClearance == null || solineClearance.UsrClearance == "No"))
{
decimal? avgCost = GetAverageCost(soline.InventoryID);

if (avgCost != null && avgCost != 0m && soline.OrderQty != 0m)
{
decimal? curyLineAmt = soline.CuryLineAmt != 0m ? soline.CuryLineAmt : 0.01m;
decimal? markupPct = (curyLineAmt - (avgCost.Value * soline.OrderQty)) / (avgCost.Value * soline.OrderQty);
cache.SetValueExt<SOLineMarkupExtension.usrMarkupPct>(soline, markupPct);
}
else
{
cache.SetValueExt<SOLineMarkupExtension.usrMarkupPct>(soline, 0m);
}
}

if (solineClearance != null)
{
string clearanceValue = GetClearanceValue(soline.InventoryID);

if (!string.IsNullOrEmpty(clearanceValue))
{
cache.SetValueExt<SOLineClearanceExtension.usrClearance>(soline, clearanceValue);
}
else
{
cache.SetValueExt<SOLineClearanceExtension.usrClearance>(soline, null);
}
}
}
}

#endregion

private decimal? GetAverageCost(int? inventoryID)
{
INItemCost itemCost = PXSelect<INItemCost,
Where<INItemCost.inventoryID, Equal<Required<INItemCost.inventoryID>>>>
.Select(Base, inventoryID);

return itemCost?.AvgCost;
}

private string GetClearanceValue(int? inventoryID)
{
CSAnswers clearanceAttribute = PXSelectJoin<CSAnswers,
InnerJoin<InventoryItem, On<CSAnswers.refNoteID, Equal<InventoryItem.noteID>>>,
Where<InventoryItem.inventoryID, Equal<Required<InventoryItem.inventoryID>>,
And<CSAnswers.attributeID, Equal<Required<CSAnswers.attributeID>>>>>
.Select(Base, inventoryID, "CLEARANCE");

return clearanceAttribute?.Value;
}

public override void Initialize()
{
Page page = HttpContext.Current?.Handler as PXPage;
if (page != null)
{
page.Load += Page_Load;
}
}

private void Page_Load(object sender, EventArgs e)
{
Page page = (Page)sender;

PXGrid solineGrid = ControlHelper.FindControl("grid", page) as PXGrid;
if (solineGrid != null)
{
LiteralControl css = new LiteralControl("<style>.myGridRowHighlight { background-color: #FFCCCC; }</style>");
page.Header.Controls.Add(css);

solineGrid.RowDataBound += (object grdSender, PXGridRowEventArgs erdb) =>
{
SOLine soline = erdb.Row.DataItem as SOLine;

if (soline != null)
{
SOLineMarkupExtension solineMarkup = soline.GetExtension<SOLineMarkupExtension>();
SOLineClearanceExtension solineClearance = soline.GetExtension<SOLineClearanceExtension>();

InventoryItem item = PXSelect<InventoryItem, Where<InventoryItem.inventoryID, Equal<Required<InventoryItem.inventoryID>>>>.Select(Base, soline.InventoryID);

if (item != null && item.ItemType == "F" && solineMarkup != null && solineClearance != null && (solineClearance.UsrClearance == null || solineClearance.UsrClearance == "No") && solineMarkup.UsrMarkupPct < 0.1m)
{
erdb.Row.Style.CssClass = "myGridRowHighlight";
}
}
};
}
}
}
}

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved