Loading Inventory Opening Balances

There could be two scenarios under this topic based on when the chart of accounts is live or not.

1. When the company is going live and the chart of accounts also has to be imported. In this case we know that the inventory account in the COA will also have some balance which would account for the inventory cost. Now when the inventory journal is passed with some positive adjustments then the inventory account will get updated in the GL which mean the opening JV should not update the inventory again else the inventory amount would double. So this is how it is done
1. Post the positive adjustments which will update the quantities in the inventory sub-ledger and the stock adjustment account with a credit of an equivalent amount.
2. When posting the opening JV the debit of inventory is posted to the stock adjustment account instead of the actual stock account.
   
3. For clarity sake we can created a new suspense account and use that instead of the regular stock adjustment account
2. The second case is when the chart of accounts has been already imported and the inventory cost is already sitting in the respective accounts. In this case we only want the quantities to be imported and we do not want to have any effect on the GL. This is how it would be done
1. Temporarily for the sake of posting the opening inventory map the GL accounts for inventory to an interim account.
   
2. Map the stock adjustment account to the same interim account.
3. Post the Inventory JV now the debit and credit would both go to the same account and nullify.
4. Revert back the account mappings to the original settings.
   

Automating the Parameters in a Crystal Report

Well the advantage with using the parameters is that is reduces the traffic from the server to the client. If a condition is used in the record selection with formulas then the filtering is done on the client which is not the best way when the data on the underlying report is too much i faced this issue and has to resolved to use the parameters collections of crystal.

Well as i was using a crystal reports control and did not want the ugly parameter box popped by crystal as it does not provide features like browse etc which i had build on my request form. I then realized that there is a way to this although easy but had to really look around to get he syntax right :-) anyways here it is: -

ParameterFields:
This property specifies the default value of the specified parameter field.When the prompting dialog box appears for the parameter field, the value specified with this property will be the default value that appears it can also be skipped as we will see later. This property is not available for subreports.


Syntax
[form.]Report.ParameterFields(ArrayIndex)[=”ParameterName;NewValue;SetCurrentValue”]

Remarks:

The parameter, SetCurrentValue can either be set to TRUE or FALSE.

If set to TRUE, the parameter value is passed to the current value in the report; the user is not prompted to enter the parameter value.

If set to FALSE, the parameter value is passed to the default value for the parameter; the user is prompted to enter the parameter value, with the value you set showing as the default value.

The default value for SetCurrentValue is FALSE.

Use a separate line of code for each parameter field for which you want to change the value.

The order of values in the array must conform to the order of parameter fields in the report.

The first parameter field in the report is array index (0), the second is (1), etc.


For example:

To change the value of the first parameter field in a report (parameter1) to “red” use the following syntax (user will not be prompted to enter a value):
CrystalReport1.ParameterFields(0) = “parameter1;red;TRUE”


To change the value in a Date parameter field use the following syntax (user will not be prompted to enter a value):

CrystalReport1.ParameterFields(0) = “DatePar;Date(1998,12,7);TRUE”


To prompt the user to change the value of the third parameter field in a report (parameter3) use the following syntax (user will be prompted to use the default value set using the NewValue parameter below - “blue”):

CrystalReport1.ParameterFields(2) = “parameter3;blue;FALSE”


Note:
Even though one might use parameters the filters will not be applied on the server if the parameters are used within conditions in the record selection formula.

I was trying to use if conditions in the record selection formula to have dynamic record selection as below :

local booleanvar showrecord;

showrecord := True;
showrecord := showrecord and ( {vw_transport_log.tr_closed} = 'N');

if {vw_transport_log.from_location} <> '' then
showrecord := showrecord and ( {vw_transport_log.from_location} = {@from_warehouse} );

if ({@to_warehouse} <> '') then
showrecord := showrecord and ({vw_transport_log.to_location} = {@to_warehouse});

if ({@transporter} <> '') then
showrecord := showrecord and ({vw_transport_log.transporter} = {@transporter});

if ( {@from_date} <> Date (1900,01,01) ) then
showrecord := showrecord and ({vw_transport_log.start_date} >= {@from_date});

if ( {@to_date} <> Date (1900,01,01) ) then
showrecord := showrecord and ({vw_transport_log.start_date} <= {@to_date}); showrecord; then i realized that in cases like this the filters are applied at the client so to force the filters at server the record selection formula should be modified as follows: and ({vw_sagya_movements.dated} >= {?FromDate})
and ( {vw_sagya_movements.dated} <= {?ToDate} ) and ({vw_sagya_movements.from_location} = {?FromWarehouse} );

which means "no if conditions"; the way around this could be to apply extreme values to the from and to parameters, in case a filter is not required. For eg if date filter is skipped then i apply a date filter like 1900-01-01 to 2099-01-01 it is as good as the filter is not applied.

SQL 2005 Startup Options

Well struggled for this a lot although quite silly, Well we all know that SQL 2005 to work with Navision requires that the trace flag 4616 is turned on. To enable this the best way is to specify this as a startup option for the SQL Service. This option can be set using the SQL Server Configuration manage here one needs to right click on the SQL service and select the advanced tab and use the startup parameter to specify one.

The only confusion is that traditionally we have been using multiple command line parameters separated by a space but in this case the parameters are delimited by a semicolon (;) and there should not be any space between two options.

this is a typical value for the startup parameters option

-T4616;-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf