This example show how to use the filter text property on a sage 200 business object.
Private Function GetPOPQtyBreakCostPrice(oStockItem As Sage.Accounting.Stock.StockItem, quantity As Decimal) As Decimal
Dim oSupplier = CType(Me._AmendableForm.BoundObjects(0), Sage.Accounting.POP.POPOrderReturnLine).POPOrderReturn.Supplier
Using oPOPQTYBreakPrices As New SCITSupplierStockQtyBreaks
oPOPQTYBreakPrices.Query.Filters.Add(New Sage.ObjectStore.Filter(SCITSupplierStockQtyBreak.FIELD_STOCKITEMDBKEY, CInt(oStockItem.PrimaryKey.Value)))
oPOPQTYBreakPrices.Query.Filters.Add(New Sage.ObjectStore.Filter(SCITSupplierStockQtyBreak.FIELD_SUPPLIERDBKEY, CInt(oSupplier.PrimaryKey.Value)))
'Dim oFilterFromQty As New Sage.ObjectStore.Filter(quantity, Sage.Common.Data.FilterOperator.GreaterThanOrEqual, SCITSupplierStockQtyBreak.FIELD_FROMQUANTITY)
'oFilterFromQty.BoundParameter = False
'oPOPQTYBreakPrices.Query.Filters.Add(oFilterFromQty)
'Dim oFilterToQty As New Sage.ObjectStore.Filter(quantity, Sage.Common.Data.FilterOperator.GreaterThanOrEqual, SCITSupplierStockQtyBreak.FIELD_TOQUANTITY)
'oFilterFromQty.BoundParameter = False
'oPOPQTYBreakPrices.Query.Filters.Add(oFilterToQty)
Try
oPOPQTYBreakPrices.Query.FilterText = String.Format("{0} >= FromQuantity and {0} <= ToQuantity", quantity)
' Dim oCmd = oPOPQTYBreakPrices.
oPOPQTYBreakPrices.Find()
If oPOPQTYBreakPrices.First Is Nothing Then
Return 0D
Else
Return oPOPQTYBreakPrices.First.CostPrice
End If
Catch ex As Exception
Return 0
End Try
End Using
End Function
Wednesday, 19 October 2011
InFilter example when using Sage 200 object store builder
This example show how to use the infilter object on a sage 200 business object.
IfDisplayUnassignedUnitsOnly Then
Dim oListUnitPK As New List(Of Object)
Using oTenancyies As New SCIT.DHT.Accounting.SCITTenancies
For Each oTenancy As SCIT.DHT.Accounting.SCITTenancy In oTenancyies
oListUnitPK.Add(CInt(oTenancy.Unit.PrimaryKey.Value))
Next
End Using
Dim NotInFilter As Sage.ObjectStore.InFilter
NotInFilter = Sage.ObjectStore.InFilter.CreateNew(SCIT.DHT.Accounting.SCITUnit.FIELD_SCITUNIT, Sage.Common.Expressions.InOperator.NotIn, oListUnitPK.ToArray)
Units.Query.Filters.Add(NotInFilter)
End If
If Units.Query.Filters.Count > 0 Then
Units.Find()
End If
IfDisplayUnassignedUnitsOnly Then
Dim oListUnitPK As New List(Of Object)
Using oTenancyies As New SCIT.DHT.Accounting.SCITTenancies
For Each oTenancy As SCIT.DHT.Accounting.SCITTenancy In oTenancyies
oListUnitPK.Add(CInt(oTenancy.Unit.PrimaryKey.Value))
Next
End Using
Dim NotInFilter As Sage.ObjectStore.InFilter
NotInFilter = Sage.ObjectStore.InFilter.CreateNew(SCIT.DHT.Accounting.SCITUnit.FIELD_SCITUNIT, Sage.Common.Expressions.InOperator.NotIn, oListUnitPK.ToArray)
Units.Query.Filters.Add(NotInFilter)
End If
If Units.Query.Filters.Count > 0 Then
Units.Find()
End If
Thursday, 6 October 2011
Check Index Fragmentation on SQL server
USE CRM
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
Friday, 25 March 2011
SQL Query to find where the custome Dot net Dll's are Stored In Sage CRM
SELECT Parm_id, Parm_CreatedBy, Parm_CreatedDate, Parm_UpdatedBy, Parm_UpdatedDate, Parm_TimeStamp, Parm_Deleted, Parm_Name, Parm_Value, Parm_Component
FROM Custom_SysParams WHERE (Parm_Name = 'Dotnetdlldirectory')
FROM Custom_SysParams WHERE (Parm_Name = 'Dotnetdlldirectory')
Friday, 4 March 2011
Stop the solution appearing twice on the CRM system menu.
UPDATE Custom_Tabs Set Tabs_Caption = N'SolutionDELETE' Where Tabs_TabId <>
(SELECT MIN (Tabs_TabId) FROM Custom_Tabs WHERE tabs_Deleted is null AND
( Tabs_Entity = N'find' and Tabs_DeviceID is null and Tabs_InButtonGroup is null and
COALESCE(Tabs_Action, N'') <> N'newaccount' and Tabs_Caption = N'Solution'))
AND tabs_Deleted is null AND ( Tabs_Entity = N'find' and Tabs_DeviceID is null and
Tabs_InButtonGroup is null and COALESCE(Tabs_Action, N'') <> N'newaccount' and Tabs_Caption = N'Solution')
(SELECT MIN (Tabs_TabId) FROM Custom_Tabs WHERE tabs_Deleted is null AND
( Tabs_Entity = N'find' and Tabs_DeviceID is null and Tabs_InButtonGroup is null and
COALESCE(Tabs_Action, N'') <> N'newaccount' and Tabs_Caption = N'Solution'))
AND tabs_Deleted is null AND ( Tabs_Entity = N'find' and Tabs_DeviceID is null and
Tabs_InButtonGroup is null and COALESCE(Tabs_Action, N'') <> N'newaccount' and Tabs_Caption = N'Solution')
Wednesday, 9 February 2011
Adding new fonts to CRM
'Although editing this is not technically supported, go to C:\Program Files\Sage\CRM\CRMINSTALL\WWWRoot\Fckeditor\ fckconfig.js and add the fonts as follows:
FCKConfig.EnableMoreFontColors = false ;
FCKConfig.FontColors = '000000,993300,333300,003300,003366,000080,333399,333333,800000,FF6600,808000,808080,008080,0000FF,666699,808080,FF0000,FF9900,99CC00,339966,33CCCC,3366FF,800080,999999,FF00FF,FFCC00,FFFF00,00FF00,00FFFF,00CCFF,993366,C0C0C0,FF99CC,FFCC99,FFFF99,CCFFCC,CCFFFF,99CCFF,CC99FF,FFFFFF' ;
FCKConfig.FontFormats = 'p;h1;h2;h3;h4;h5;h6;pre;address;div' ;
FCKConfig.FontNames = 'Arial;Comic Sans MS;Courier New;Tahoma;Times New Roman;Verdana;Webdings;Impact ;
FCKConfig.FontSizes = 'smaller;larger;xx-small;x-small;small;medium;large;x-large;xx-large' ;
This will only work though if the end user also has that font installed on their machine which is why this list only has default fonts.'
FCKConfig.EnableMoreFontColors = false ;
FCKConfig.FontColors = '000000,993300,333300,003300,003366,000080,333399,333333,800000,FF6600,808000,808080,008080,0000FF,666699,808080,FF0000,FF9900,99CC00,339966,33CCCC,3366FF,800080,999999,FF00FF,FFCC00,FFFF00,00FF00,00FFFF,00CCFF,993366,C0C0C0,FF99CC,FFCC99,FFFF99,CCFFCC,CCFFFF,99CCFF,CC99FF,FFFFFF' ;
FCKConfig.FontFormats = 'p;h1;h2;h3;h4;h5;h6;pre;address;div' ;
FCKConfig.FontNames = 'Arial;Comic Sans MS;Courier New;Tahoma;Times New Roman;Verdana;Webdings;Impact ;
FCKConfig.FontSizes = 'smaller;larger;xx-small;x-small;small;medium;large;x-large;xx-large' ;
This will only work though if the end user also has that font installed on their machine which is why this list only has default fonts.'
Friday, 28 January 2011
Getting port number of a running application
There are a couple of applications that may fail to start and complaint because its port is being used by a different application. This is a quick and dirty way of knowing which application is responsible for tying up a port on a Windows box.
Open up a command prompt:
1.Start
Run
type cmd
Enter
2.Type netstat -aon
findstr “[port number]“
3.Take note of the numbers on the last line. This is the PID or Process ID.
4.Type tasklist
findstr “[PID]“ and this will return the application corresponding to that PID.
5.Once PID is determined, you can now kill it in Task Manager or kill it typing tskill at the command prompt.
For example:
1.I type netstat -aon
findstr “8080″ at command prompt (I’m on a proxy by the way)
2.I see that the PID is 3624
3.I type tasklist
findstr “3624″ to find what PID 3624 is and it points to msnmsgr.exe which is MSN/Live Messenger.
4.I can now kill MSN Messenger from Task Manager or type tskill 3624
Open up a command prompt:
1.Start
Run
type cmd
Enter
2.Type netstat -aon
findstr “[port number]“
3.Take note of the numbers on the last line. This is the PID or Process ID.
4.Type tasklist
findstr “[PID]“ and this will return the application corresponding to that PID.
5.Once PID is determined, you can now kill it in Task Manager or kill it typing tskill at the command prompt.
For example:
1.I type netstat -aon
findstr “8080″ at command prompt (I’m on a proxy by the way)
2.I see that the PID is 3624
3.I type tasklist
findstr “3624″ to find what PID 3624 is and it points to msnmsgr.exe which is MSN/Live Messenger.
4.I can now kill MSN Messenger from Task Manager or type tskill 3624
Monday, 4 January 2010
Sage 200 Transfer Stock Between Locations Example
Public Function StockMovement(ByVal oSale As StockReplenishment) As Boolean
Using locationMovementTransfer As New Sage.Accounting.Stock.LocationMovementTransfer
Try
Dim oStockItem As Sage.Accounting.Stock.StockItem = Nothing
oStockItem = ImportCSVInfomationFromMSI.StockItemLookup(oSale.SKU)
If oStockItem Is Nothing Then
oStockItem = ImportCSVInfomationFromMSI.StockItemLookup(oSale.WinPafArticleID)
End If
If oStockItem IsNot Nothing Then
locationMovementTransfer.StockItem = oStockItem
locationMovementTransfer.TransferDate = oSale.DateOfInventory
locationMovementTransfer.TransferQuantity = Math.Abs(oSale.ReplenishmentQuantity)
Dim oNarrative As String
If _ReverseAllTransactions = False Then
locationMovementTransfer.LocationFrom = ImportCSVInfomationFromMSI.BinItemLookup(locationMovementTransfer.StockItem, Tsg.Penhaligons.DataAccess.Startup.IntegrationSettings.CRMWarehouseName, "")
locationMovementTransfer.LocationTo = ImportCSVInfomationFromMSI.BinItemLookup(locationMovementTransfer.StockItem, oSale.ToStoreNumber, "")
oNarrative = "Transfer from warehouse " & Tsg.Penhaligons.DataAccess.Startup.IntegrationSettings.CRMWarehouseName _
& " to warehouse " & oSale.ToStoreNumber & "Processed By " & oSale.SageOrderID & " Posted By " & Sage.Accounting.Application.ActiveUser.UserName
Else
locationMovementTransfer.LocationFrom = ImportCSVInfomationFromMSI.BinItemLookup(locationMovementTransfer.StockItem, oSale.ToStoreNumber, "")
locationMovementTransfer.LocationTo = ImportCSVInfomationFromMSI.BinItemLookup(locationMovementTransfer.StockItem, Tsg.Penhaligons.DataAccess.Startup.IntegrationSettings.CRMWarehouseName, "")
oNarrative = "Transfer from warehouse " & oSale.ToStoreNumber _
& " to warehouse " & Tsg.Penhaligons.DataAccess.Startup.IntegrationSettings.CRMWarehouseName _
& "Processed By " & oSale.SageOrderID & " Posted By " & Sage.Accounting.Application.ActiveUser.UserName
End If
locationMovementTransfer.Reference = oSale.SageOrderID
locationMovementTransfer.SecondReference = oSale.WinPafArticleID
locationMovementTransfer.Memo = oNarrative
Using oTransaction As New Sage.ObjectStore.PersistentObjectTransaction
Try
locationMovementTransfer.Validate()
locationMovementTransfer.Post()
If oNarrative.Length > 60 Then
oNarrative = oNarrative.Substring(0, 60)
End If
Dim Urn As Int64 = PostNominalJournal(oSale, oStockItem, oNarrative)
If Urn > 0 Then
oTransaction.Commit()
Return True
Else
Return False
End If
Catch ex As Exception
Throw ex
End Try
End Using
Else
LogFailedInformation(oSale.RawLine)
Throw New Exception("Invalid Stock Code or SKU for stock exchange/transfer.")
End If
Catch ex As Exception
LogException(New ImportExecption(ex))
End Try
End Using
End Function
Tuesday, 8 December 2009
Nominal Ledger None Taxable Receipt Example
Private Function NoneTaxableReceipt(ByVal oItem As ImportLine) As Boolean
Using oInstrument As Sage.Accounting.CashBook.BankReceiptJournalInstrument = Sage.Accounting.CashBook.BankReceiptJournalInstrumentFactory.Factory.CreateNew
Try
With oInstrument
.Bank = oItem.CashBookAccount
.InstrumentNo = _JournalInstrumentNo
.InstrumentDate = oItem.TransactionDate
.SecondReferenceNo = ""
.InstrumentItemNarrative = oItem.Narrative
If .NominalSettlementJournalInstrumentItems.Count = 0 Then
Dim oInstrumentItem As Sage.Accounting.NominalLedger.NominalSettlementJournalInstrumentItem = .CreateNewSettlementJournalInstrumentItem
oInstrumentItem.NominalSpecification = oItem.NominalCode.NominalSpecification
oInstrumentItem.NominalSpecification = oItem.NominalCode.NominalSpecification
oInstrumentItem.InstrumentDate = oItem.TransactionDate
oInstrumentItem.GrossAmount = oItem.DebitAmount
Else
.NominalSettlementJournalInstrumentItems(0).NominalSpecification = oItem.NominalCode.NominalSpecification
.NominalSettlementJournalInstrumentItems(0).NominalSpecification = oItem.NominalCode.NominalSpecification
.NominalSettlementJournalInstrumentItems(0).InstrumentDate = oItem.TransactionDate
.NominalSettlementJournalInstrumentItems(0).GrossAmount = oItem.DebitAmount
End If
'.ChequeGoodsValue = oItem.DebitAmount
.Validate()
.Update()
Return True
End With
Catch ex As Exception
Throw ex
End Try
End Using
End Function
Friday, 4 December 2009
Sage 200 Write Off Stock Example
Public Function WriteOffStock(ByVal oSale As StockExchange) As Boolean
Using oTran As New Sage.ObjectStore.PersistentObjectTransaction
Using oGoodsInMovment As New Sage.Accounting.Stock.StockWriteOffMovementActivity
'Dim x As Sage.Accounting.Stock.StockWriteOffMovementActivity
Try
Sage.Accounting.Application.AllowableWarnings.Add(oGoodsInMovment, oTypeMaxStockLevel)
Sage.Accounting.Application.AllowableWarnings.Add(oGoodsInMovment, oTypeMinStockLevel)
With oGoodsInMovment
Dim oStockItem As Sage.Accounting.Stock.StockItem = Nothing
oStockItem = ImportCSVInfomationFromMSI.StockItemLookup(oSale.SKU)
If oStockItem Is Nothing Then
oStockItem = ImportCSVInfomationFromMSI.StockItemLookup(oSale.WinPafArticleID)
End If
If oStockItem IsNot Nothing Then
.StockItem = oStockItem
.Location = ImportCSVInfomationFromMSI.BinItemLookup(oStockItem, oSale.FromStoreNumber, "")
.ActivityDate = oSale.DateOfInventory
.Quantity = Math.Abs(oSale.TransferQuantity)
.CostPrice = oStockItem.AverageBuyingPrice
.IssuePrice = oStockItem.AverageBuyingPrice
.SupplierBuyingPrice = oStockItem.AverageBuyingPrice
.Reference = oSale.SalesPersonID
.SecondReference = oSale.WinPafArticleID
.DefaultNarrative = oSale.WinPafArticleID + " " + oSale.SalesPersonID + " " + oSale.FromStoreNumber
.CalculateValues()
.Validate()
'.PLNominalCode = GetStockWriteOffNominalCode()
GetStockWriteOffNominalCode(oGoodsInMovment, oSale)
'.SourceAreaType = Sage.Accounting.SourceAreaTypeEnum.EnumSourceAreaTypeWriteOff
.Update()
oTran.Commit()
Return True
Else
LogFailedInformation(oSale.RawLine)
Throw New Exception("Invalid Stock Code or SKU for stock adjustment out.")
End If
End With
Catch ex As Exception
LogFailedInformation(oSale.RawLine)
LogException(New ImportExecption(ex))
Finally
Sage.Accounting.Application.AllowableWarnings.Delete(oGoodsInMovment, oTypeMaxStockLevel)
Sage.Accounting.Application.AllowableWarnings.Delete(oGoodsInMovment, oTypeMinStockLevel)
End Try
End Using
End Using
End Function
Tuesday, 27 October 2009
Planet X Nibiru - Planet X Video
If you are interested in planet X Nibiru please check out this video
Planet X Nibiru - Planet X Video
Planet X Nibiru - Planet X Video
Tuesday, 20 October 2009
Visual Studio code examples useful sites
Here are a few sites that you may find useful when looking for visual basic code examples.
MicroSoft's 101 code examples.
http://www.microsoft.com/DOWNLOADS/details.aspx?FamilyID=87951cb9-5aeb-4f46-9bf0-2b3e3664be77&displaylang=en
VB .Net Heaven
http://www.vbdotnetheaven.com/
VB code Source
http://www.vbcodesource.com/
Free VB Code
http://www.freevbcode.com/
Code Guru
http://www.codeguru.com/
MicroSoft's 101 code examples.
http://www.microsoft.com/DOWNLOADS/details.aspx?FamilyID=87951cb9-5aeb-4f46-9bf0-2b3e3664be77&displaylang=en
VB .Net Heaven
http://www.vbdotnetheaven.com/
VB code Source
http://www.vbcodesource.com/
Free VB Code
http://www.freevbcode.com/
Code Guru
http://www.codeguru.com/
Sage 200 Revalue Stock Item Example
This example is in Visual Basic
Public Function RevalueStockItem(ByVal oStockItem As Sage.Accounting.Stock.StockItem, _ ByVal NewAverageBuyingPrice As Decimal) As String
Using oRevalueItem As New Sage.Accounting.Stock.StockHoldingRevaluationAdjustment
Try
With oRevalueItem
'oStockItem.AverageBuyingPrice = NewAverageBuyingPrice
oStockItem.StandardCost = NewAverageBuyingPrice
oStockItem.Update()
Try
.StockItem = oStockItem
Catch ex As Sage.Accounting.Exceptions.BuyingPriceIsZeroException
Throw ex
Catch ex As Sage.Accounting.Exceptions.StockLevelTooLowToRevalueException
Throw ex
Catch ex As Exception
Throw ex
End Try
.NewPrice = NewAverageBuyingPrice
.Narrative = "Cost Price Update " + Date.Today.ToShortDateString '+ " " + Date.Today.ToShortTimeString
.AssetNominalCode = oStockItem.StockItemNominalCodes(NominalUsageTypeEnum.EnumNominalUsageStock).NominalCode
.IssuesNominalCode = oStockItem.StockItemNominalCodes(NominalUsageTypeEnum.EnumNominalUsageIssues).NominalCode
.Post()
Return String.Empty
End With
Catch ex As Exception
Return ex.Message
End Try
End Using
End Function
Public Function RevalueStockItem(ByVal oStockItem As Sage.Accounting.Stock.StockItem, _ ByVal NewAverageBuyingPrice As Decimal) As String
Using oRevalueItem As New Sage.Accounting.Stock.StockHoldingRevaluationAdjustment
Try
With oRevalueItem
'oStockItem.AverageBuyingPrice = NewAverageBuyingPrice
oStockItem.StandardCost = NewAverageBuyingPrice
oStockItem.Update()
Try
.StockItem = oStockItem
Catch ex As Sage.Accounting.Exceptions.BuyingPriceIsZeroException
Throw ex
Catch ex As Sage.Accounting.Exceptions.StockLevelTooLowToRevalueException
Throw ex
Catch ex As Exception
Throw ex
End Try
.NewPrice = NewAverageBuyingPrice
.Narrative = "Cost Price Update " + Date.Today.ToShortDateString '+ " " + Date.Today.ToShortTimeString
.AssetNominalCode = oStockItem.StockItemNominalCodes(NominalUsageTypeEnum.EnumNominalUsageStock).NominalCode
.IssuesNominalCode = oStockItem.StockItemNominalCodes(NominalUsageTypeEnum.EnumNominalUsageIssues).NominalCode
.Post()
Return String.Empty
End With
Catch ex As Exception
Return ex.Message
End Try
End Using
End Function
Monday, 19 October 2009
Post build Macro for Sage 200 2009
Plance this macro in your post build event in visual studio 2008.
The Macro is for Sage 200 2009
copy "$(TargetPath)" "c:\program files\sage\sage200\$(TargetFileName)"
When you are developing for sage 200 2009 make sure that you set the working directory, otherwise you may get errors when loading sage 200 2009 and debuging your application.
This Macro is for Sage 200 5.1 and earlier
copy "$(TargetPath)" "c:\program files\sage\MMS\$(TargetFileName)"
The Macro is for Sage 200 2009
copy "$(TargetPath)" "c:\program files\sage\sage200\$(TargetFileName)"
When you are developing for sage 200 2009 make sure that you set the working directory, otherwise you may get errors when loading sage 200 2009 and debuging your application.
This Macro is for Sage 200 5.1 and earlier
copy "$(TargetPath)" "c:\program files\sage\MMS\$(TargetFileName)"
Subscribe to:
Posts (Atom)