How to insert VARBINARY data in MS SQL for Acumatica

Hello everybody,

sometime it is needed to insert some binary information in one or another table inside of Acumatica. Quite often developers just modify existing record in table UploadFile or UploadFileRevision.

But I don't like such approach, as it is prone to errors and potentially can harm some of your existing data. That's why I propose to use cast operator of MS SQL. Take a look at following example:

insert into UploadFileRevision(CompanyID, FileID, FileRevisionID, Data, Size, CreatedByID, CreatedDateTime, CompanyMask) values
								(2, '35b15ad7-b5c3-4a19-aa77-3a24c046d689', 1, 
													CAST('wahid' AS VARBINARY(MAX)),
													4, 
													'B5344897-037E-4D58-B5C3-1BDFD0F47BF9',
													'2016-06-08 08:53:50.937',
																	0xAA)

 

Take notice of 

CAST('wahid' AS VARBINARY(MAX)),

line. It will insert into local dev instance database varbinary representation of wahid. 

Comments TODO and HACK in C#

Hello everybody,

today I want to leave one more note, which you can use for boosting your productivity.

Imagine following scenario. On the meeting with customer you've got some list of to do points. You have them on your email. You've made some research, and found that you need to make changes to 5 C# files. How not to forget about all of those files?

On rescue comes two comments in C#:

  1. TODO
  2. HACK

Leave those comments in your 5 files -> save all files -> Open Task List, and you'll see something like this:

As you can see from presented screenshot, after you leave TODO or HACK comments in 5 C# files, you'll be in perfect position of usage of code.

What is even more interesting, with help of double click you can navigate to exact comment location.

How to imitate click on Confirm shipment in Acumatica

Hello everybody,

Today I want to describe how to imiate click on menu item "Confirm shipment" in Acumatica. 

Probably your first guess will be just call method ConfirmShiment of graph SOShipmentEntry. But for now Acumatica team has another advice in order to call this action. Instead of calling method ConfirmShipment you'll need to have a bit more steps.

Code sample below demonstrates those necessary steps:

SOShipmentEntry shipmentGraph = PXGraph.CreateInstance<SOShipmentEntry>(); //Create instance of Graph
PXAutomation.CompleteSimple(shipmentGraph.Document.View);
PXAdapter adapter2 = new PXAdapter(new DummyView(shipmentGraph, shipmentGraph.Document.View.BqlSelect,
						 new List<object> { shipmentGraph.Document.Current }));
adapter2.Menu = SOShipmentEntryActionsAttribute.Messages.ConfirmShipment;
adapter2.Arguments = new Dictionary<stringobject>
{
		{"actionID"SOShipmentEntryActionsAttribute.ConfirmShipment}
};
adapter2.Searches = new object[]{shipmentGraph.Document.Current.ShipmentNbr};
adapter2.SortColumns = new[] { "ShipmentNbr"};
soShipmentGraph.action.PressButton(adapter2);
TimeSpan timespan;
Exception ex;
while (PXLongOperation.GetStatus(shipmentGraph.UID, out timespanout ex) == PXLongRunStatus.InProcess)
{ }
//Here you'll have your shipment confirmed

And DummyView looks like this:

public class DummyView : PXView
    {
        List<object> _Records;
        internal DummyView(PXGraph graphBqlCommand commandList<objectrecords)  : base(graphtruecommand)
        {
            _Records = records;
        }
        public override List<objectSelect(object[] currentsobject[] parametersobject[] searchesstring[] sortcolumns
bool[] descendingsPXFilterRow[] filtersref int startRowint maximumRowsref int totalRows)         {             return _Records;         }     }

If you wonder, why calling ConfirmShipment is not enough, answer is this: ConfirmShipment cal will confirm shipment, but it will not execute Automation Steps. Thats why all of mentioned steps are needed. Otherwise, you'll make long research on question, why something doesn't work in the same way, as it works in UI, but doesn't work from my code.

 

Pagination in custom inquiry

Hello everybody,

today I want to share with everybody, and with myself code fragment, that allows to achieve pagination in custom inquiry pages. 

Take a look on the code below:

  public class YourGraphInquiry : PXGraph<YourGraphInquiry>
  {
    public PXCancel<YourDACFilter> Cancel;
        public PXFilter<YourDACFilter> Filter;
 
        [PXFilterable]
        public PXSelect<YourDACDetails,
            Where2<
                Where<YourDACDetails.SomeidEqual<Current<YourDACFilter.SomeID>>, 
                    Or<Current<YourDACFilter.SomeID>, IsNull>>,
                And<
                    Where<YourDACDetails.SomepartidEqual<Current<YourDACFilter.SomePartId>>, 
                        Or<Current<YourDACFilter.SomePartId>, IsNull>>>>> Details;
 
        public virtual IEnumerable details()
        {
            PXView cmd = new PXView(thistrue, Details.View.BqlSelect);
            var currentFilter = Filter.Current;
 
            var s = (currentFilter.PageNbr ?? 0) * (currentFilter.PageSize ?? 0);
            int startRow = s > 0 ? s : PXView.StartRow;
            int totalRows = 0;
            int maxRows = (currentFilter.PageSize ?? 0) == 0 ? PXView.MaximumRows : currentFilter.PageSize ?? 0;
            foreach (var result in cmd.Select(new[] { currentFilter }, nullPXView.Searches,
                PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRowmaxRowsref totalRows))
            {
                yield return result;
            }
            startRow = 0;
        }
        public YourGraphInquiry ()
        {
            Details.Cache.AllowInsert = false;
            Details.Cache.AllowDelete = false;
            Details.Cache.AllowUpdate = false;
        }
 
       
  }
    
    [System.SerializableAttribute()]
    public class YourDACFilter : PX.Data.IBqlTable
    {
 
        #region SomeID
        public abstract class SomeID : BqlInt.Field<SomeID>
        {
        }
 
        protected int? _SomeID;
 
        [PXDBInt()]
        [PXDefault()]
        [PXUIField(DisplayName = "Some", Visibility = PXUIVisibility.Visible)]
        [PXSelector(typeof(Search<CWSome.SomeID>),   new Type[]{ typeof(MVP.CWSome.SomeCD), typeof(MVP.CWSome.description), typeof(MVP.CWSome.SomeCD) }, SubstituteKey = typeof(MVP.CWSome.SomeCD))]
        public virtual int? SomeID
        {
            get { return this._SomeID; }
            set { this._SomeID = value; }
        }
 
        #endregion SomeID
 
        #region SomePartId
 
        public abstract class SomePartId : BqlInt.Field<SomePartId>
        {
        }
 
        protected int? _SomePartId;
 
        [PXDBInt()]
        [PXDefault()]
        [PXUIField(DisplayName = "Some Part", Visibility = PXUIVisibility.Visible)]
        [PXSelector(typeof(Search<CWSomePart.SomePartIdWhere<CWSomePart.SomeIDEqual<Current<YourDACFilter.SomeID>>>>), 
                    new Type[] { typeof(CWSomePart.SomePartCD), typeof(CWSomePart.SomePartFullCD) }, SubstituteKey = typeof(CWSomePart.SomePartCD))]
        public virtual int? SomePartId
        {
            get { return this._SomePartId; }
            set { this._SomePartId = value; }
        }
        #endregion SomePartId
 
        #region Page number
        public abstract class pageNbr : BqlInt.Field<pageNbr> { }
        [PXInt]
        [PXUIField(DisplayName = "Page Number")]
        [PXDefault(0)]
        public virtual int? PageNbr { getset; }
 
        #endregion Page number
 
        #region page size
 
        public abstract class pageSize : BqlInt.Field<pageSize> { }
        [PXInt]
        [PXUIField(DisplayName = "Page Size")]
        [PXDefault(3)]
        public virtual int? PageSize { getset; }
 
        #endregion page size
    }
}

 

Presented code has few important features:

  1. With this code you can achieve fragmentation of reading of your data. 
  2. In order to avoid returning everything, used structure of yield return. It's efficient because allows to return not all array, but elements of array one by one
  3. In the end of details method, you can see startRow = 0. If you wonder why, reason is simple, after all elements in db will be executed, that line of code will reset reading of pages to the begining. 
  4. cmd.Select allows you to read chunk of data from db, in a very similar manner to SelectWindowed. If you check generated sql, you'll see staff like this:

SELECT TOP (3) [YourDACDetails].[Column1], [YourDACDetails].[Column2], .....

5. But for second and other pages, generated sql will not be very efficient, but on UI Side you'll not notice it. For example, when I've set Page number = 3, and page size = 5, I've got following sql:

exec sp_executesql N'SELECT TOP (20) [YourDACDetails].[Column1], [YourDACDetails].[Column2], ..... which leads to a conclusion, that Acumatica framework on later stage just throws out redundant values.

Summary

After all of those details description you may wonder, what can be the case, when you may need pagination for custom inquiry. And the main scenario, where such pagination may be useful is web services synchronization. If you want to achieve pagination for some of your data, then simplest way would be usage of mentioned pattern.

 

 

Database of Acumatica is in Recovery Pending condition

Hello everybody,

recently I've got interesting situation, when my database for Acumatica developed turned to be in Pending condtion. In order to deal with it, I've executed following SQL:

 

ALTER DATABASE YourDatabase SET EMERGENCY;
GO
ALTER DATABASE YourDatabase set single_user
GO
DBCC CHECKDB (YourDatabase, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE YourDatabase set multi_user
GO

 

and my database turned back to normal.

Update on 10/08/2019

declare @dbName nvarchar(50);
set @dbName = 'yourDatabase';
 
exec( 'ALTER DATABASE' +@dbName  + ' SET EMERGENCY;')
 
exec ('ALTER DATABASE ' + @dbName + '  set single_user')
 
exec ('DBCC CHECKDB (' + @dbName + ' , REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;')
 
exec ('ALTER DATABASE ' + @dbName +' set multi_user');

I've modified script to a bit another

Three states of fields in Acumatica

Hello everybody,

today I want to write a short note on three states of fields existing in Acumatica:

  1. Exists but is empty
  2. Exist and have value
  3. Has null

If to speak about string, it can exist like this:

  1.  
  2. Some value
  3.  

Do you see difference between 1 and 3? Not easy. As usually developers of C#, show this difference like this:

  1. ""
  2. "Some Value"
  3. null

And following screenshot with explanation shows how it works in case of SOAP contracts:

 so, while you make integration, keep that in mind

New functions for redirect in Acumatica

Hello everybody,

today I want to say few words about new functions for redirect in Acumatica, and particularly about class PXRedirectHelper. 

Classical approach from T200/T300 manual may look like this:

var currentCse = Cases.Current;
if(currentCse == null)
	return;
 
 
var graph = PXGraph.CreateInstance<CRCaseMaint>();
graph.Case.Current = graph.Case.Search<CRCase.caseCD>(currentCse.CaseCD);
if (graph.Case.Current != null)
{
	throw new PXRedirectRequiredException(graphtrue"Case details");
}

But with new function all of those lines can be simplified to this:

PXRedirectHelper.TryRedirect(Cases.Cache, Cases.Current, "Edit case"PXRedirectHelper.WindowMode.NewWindow);

With that approach you'll get the same result, but just in one line of code.

Simplest cachinng explanation

Hello everybody,

today I want to give one more explanation of how to use caching and slots for caching purposes. Also there are plenty of articles on the subject,  I want to give one more with simplest recipe. So, if you need to cache something, you'll need to follow this procedure:

  1. declare you class as something that inherits IPrefetchable
  2. Create some placeholder in your class for storing items in the cache
  3. Implement Prefetch
  4. Implement GetSlot

 

Take a look on the code below, how it can be done:

public class ArTranFetcher : IPrefetchable
{
	private List<ARTran> _arTranList = new List<ARTran>();
 
	public void Prefetch()
	{
		_configurableList = new List<ARTran>();
 
		var dbRecords = PXDatabase.Select<ARTran>();
		foreach (var csAttributeDetailConfigurable in dbRecords)
		{
			_arTranList.Add(csAttributeDetailConfigurable);
		}
	}
 
	public static List<ARTran> GetARTrans()
	{
		var def = GetSlot();
		return def._arTranList;
	}
		
	private static ArTranFetcher GetSlot()
	{
		return PXDatabase.GetSlot<ArTranFetcher>("ARtranFetcherSlot"typeof(ARTran));
	}
 
}

 

few more explanations on presented code.

  1. Placeholder will be _arTranList 
  2. GetSlot will be monitoring database ARTran ( because of typeof(ARTran) statement ). So each time ARTRan will be updated, GetSlot will be executed automatically via framework.
  3. In your code you can get cached valus via following line of code: var arTrans = ArTranFetcher.GetARTrans();

 

with usage of such tricks, you can optimize plenty of staff. The only warning would be don't cache ARTran, as it will swallow up all memory of your server.

 

 

Acumatica Page Missing under Google Chrome Browser

Hello everybody,

This one is a hot topic, recently chrome team released some changes to the Chrome Browser, so that some PAGES could get missing.

You still see Menu, still see screen list but the page itself is gone, blank, empty.

How to fix?

Just change settings in the Chrome:

1. Type chrome://flags/ in the browser address bar and press Enter.

2. You should see the list of options:

3. In the search bar type Lazy Frame or just Lazy:

4. Under Enable lazy frame loading choose Disabled:

5. Press Relaunch Now at the right bottom corner:

 

Four types of programmers

Hi.
It is first time I write on topic of management and recruitment. Question is about one way of classifying your programmers, who work with you now or will come in a future. My main thesis is: all developers, roughly speaking, are divided into 4 large types and each of these types has its own application area.
Attempting to send the wrong type to solve inappropriate tasks leads to a failure ( inefficient work, or the employee leaves the team ). If you want to know why, you are welcome. Get ready, we have a lot of information. Basicallly what I say is my perception of world of programmers.

It is important to understand that there are no "bad" types. If any of the types you think is bad, then you just do not know how to use it. Remember: for each task should be a suitable tool. I will talk about all 4 types in the form of cards, giving a brief description, indicating the background, goals and motivations, as well as talking about strong qualities and weaknesses.
Especially it is necessary to pay attention to the background, as specific type of people grows out of their background. We begin with something what is easier.