If you didn't know this already, MYOB Advanced version 2019.1 has various upgrades for Generic Inquires, including Conditional Formatting. The video below will explain more about what is Generic Inquiry and it's enhancements for MYOB Advanced version 2019.1.
There are a number of times where we would like to be visually highlighted to a figure on screen. This month, we will be discussing how to highlight low stock levels on the Inventory Look up screen using Conditional Formatting on a generic Inquiry.
Before we get started on how Conditional Formatting can be used to highlight certain aspects on Generic Inquiry, let's discuss what Generic Inquiry and Conditional Formatting is, so that we start on the same page.
Generic Inquiry is an easy way to get to your data and manipulate information to find the answer you are looking for. Generic Inquiry can be sorted, filtered and saved for future use or saved in excel. It can be configured to appear in a side panel so that you can access all important information on one screen.
For example, when viewing sales orders, you can click on the side panel to view customer information for the selected order. In MYOB Advanced, you have the ability to add multiple screens in the side panel through 'Navigation'. Watch the video above to understand how you can add multiple screens to the side panel of Generic Inquiry.
Having so much information on one screen is powerful (see below). However, so much information in the grids can be confusing and overwhelming to look at. This is where Conditional Formatting comes in. Using Conditional Formation, you can highlight rows or cells on a Generic Inquiry in a range of colors. A formula can be used to highlight each row by editing the 'row style field' in 'Results Grid' tab in Generic Inquiry.
Let’s say, for example, you’ve got a column that says, “Margin of Profit.” In the past, you would have to go down the screen and look at all the numbers to find, as an example, anything which is less than 10% gross margin. You would have to carefully scan through all the lines because some results could be 11%, which looks very much like 10% when you are in a hurry. Or you could apply filters, but then you don’t have a complete view of the information as a whole.
Conditional Formatting will help you highlight rows or columns of your choice. For example, gross margin with less than 10% can be highlighted in red, results which are between 10-15% can be highlighted in yellow and everything above 15% may not need a color at all because you don't need to focus on this aspect. You can now visualise your results on one screen and focus on aspects that need attention.
So now that we are on the same page, how do you highlight low stock levels on the Inventory Look up screen? Let's find out!
Please note: A user with incorrect security roles assigned will not be able to carry out the following steps. You also require an understanding of Generic Inquiries to perform the following steps. If you or your team requires training in this aspect, please give us a call on 1800 155 999.
Open up the Stock Items List and then open the Generic Inquiry
This will open up the correct Generic Inquiry for the List Selection to add in the Conditional Formatting.
Select the Results Grid Tab, as we need to add this into the Style Column in the new 2019.1 version of MYOB Advanced.
We can now add in a rule for the Conditional Formatting below is a sample of the colour you may use for this.
When you open up the Style option you can now add in the condition.
We need to add in some code for this. For this scenario, we are setting the Quantity Available is below 100, set this as bad, else leave this as normal. =IIf( [INSiteStatus.QtyAvail] < 100, 'bad', 'default')
When you select the Stock Item List you will now see this as per the below if the Quantity on Hand is below 100.
Please contact us if you require any assistance or should you have any question on MYOB Advanced capability. If you have other topics you would like us to discuss, please let us know by clicking below. We hope to hear more from you soon.
Cloud Factory is an ERP specialist, helping you implement ERP systems such as MYOB Advanced, SAP Business One, Microsoft Dynamics 365, Microsoft Dynamics 365 Business Central, Wiise, PowerPlatform and Korber K.Motion Warehouse Edge.