How to use the Sage 200 Progress form VB.net

Private Function ValidateTransactions() As Boolean

        Dim oProgressForm = New Sage.Accounting.Common.Forms.ProgressForm

        Try

            '

            Sage.Accounting.Application.ProgressIndicator = oProgressForm

            With oProgressForm

                .CancelButtonVisible = False

                .Title = "Validate EPOS Import"

                .Visible = True

                .Maximum = 100

                .Current = 0

            End With

            ' Loop Code here 

            While 1 = 1

                oProgressForm.Description = "Validating " + "Transaction "

                oProgressForm.Current += 1

            End While

        Catch ex As Exception

        Finally

            oProgressForm.Visible = False

            Sage.Accounting.Application.ProgressIndicator = Nothing

            oProgressForm.Dispose()

            oProgressForm = Nothing

        End Try

    End Function

SQL Script to rebuild all indies on a database

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT

SET @fillfactor = 90DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases   WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database  WHILE @@FETCH_STATUS = 0  BEGIN

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''

   -- create table cursor
   EXEC (@cmd)
   OPEN TableCursor

   FETCH NEXT FROM TableCursor INTO @Table
   WHILE @@FETCH_STATUS = 0
   BEGIN

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' +CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
       ELSE
       BEGIN
          -- SQL 2000 command
          DBCC DBREINDEX(@Table,' ',@fillfactor)
       END

       FETCH NEXT FROM TableCursor INTO @Table
   END

   CLOSE TableCursor
   DEALLOCATE TableCursor

   FETCH NEXT FROM DatabaseCursor INTO @Database  END
CLOSE DatabaseCursor   DEALLOCATE DatabaseCursor

How to launch a bill of materials or manufacturing form from sage 200 2011.

     Using tK_Form As TrialKittingForm = DirectCast(Sage.Manufacturing.Application.Instance.FormLauncher.Fetch(GetType(TrialKittingForm)), TrialKittingForm)

      

                     Sage.Manufacturing.Application.Instance.FormLauncher.ShowDialog(tK_Form, Me)

       End Using

FilterText property example on a Sage 200 business object

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

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

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

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')

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')

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.'

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

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

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

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

YouTube - Planet X Special Report No. 1: Where is Planet X? (1/7)

Another helpful video on plant X

YouTube - Planet X Special Report No. 1: Where is Planet X? (1/7)

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

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/

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

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)"