Are you stuck with Sage 200? Need to know “How To” do something?
You may be an end user unsure on how some functionality works or a consultant who has come accross a problem. Don’t be shy!! Let me know.
If you have any query relating to Sage 200, please send a message or add a comment. Alternatively tweet @talkingsage200.
If you would like to know more about Smith Cooper System Partners or are looking for a new Sage partner to support you, please feel free to contact me or visit http://www.smithcooperit.co.uk
Talking Sage 200 
Hi Ctayts,
I’ve got a question about Sicon DMS. If I view an attachment there it the pop up DMS window shows an indexed filename. I would like to use the file name (or better still the file path, but I’ve not located this on the UI in safe 200c). Do you know if it is possible to reference the indexed file name in a report? Trying to create a NL transaction report with hyper links to document files.
Any help is appreciated.
Thanks
I’ve managed to find the field and join it in the report designer so I can make it a variable but I am now getting duplicate lines…
Hello,
In the Sales Ledger (“SL”), I made a contra entry with an invoice from the Purchase Ledger (“PL”). Then I noticed a difference to the bank receipt of £0.30. This was caused by an error in the PL invoice. I reversed the allocated receipt, corrected the PL invoice amount, and made a new contra entry. When I tried to reverse the first contra entry a message pops up saying “the selected transaction cannot be reversed or corrected”. The bank receipt also does not appear on the bank feeds in the column “transactions from your bank”. Is there any way to remove the incorrect contra entry and allocate the payment/receipt which was reversed previously?
Hi ctayts,
I am trying to import sales invoices with mixed VAT rates into the sales ledger. I split the transaction by VAT rates in the customer transaction import but used the same TransactionReferences however these still come through as separate invoices in Sage200. Is there a way to import these mixed VAT rate invoices as one sales invoice (similar to the way that you can manually input this information)?
Many thanks,
George
Hi George
Absolutely, you should have multiple columns for each VAT Rate.
The VATValue column is the total. Then further along after Nominals you will have about 6 VAT columns with a /1 then anther with /2 etc.
Cheers
Hi ctayts
Is there a way to export (as one download), the P&L nominal the accruals have been posted to? Trying to do a rec but we’ve 54 journals going to the accrual code. I would prefer not to export 54 journals to reconcile what we’re holding where!
Yes you could go into the enquiry screen for that Nominal and Right Click > Send to excel.
Use the Nominal Transaction Listing and filter to run for the one account and from preview select Data to Wxcel.
You could also use the Nominal Transactions Workspace and filter by the nominal account.
Cheers
Hi Ctayts, we have Sage 200 on premise, I am tying to add a print option to the sales menu, I see I need to use Menu Design Mode, but need to add it to a role to access Menu Design Mode, can’t find it anywhere in Admin
Hi Matt, to add the Edit Menu you need to amend the individual user. In System Administration, right click the user, select properties and there is an Edit Menu option.
Thanks Ctayts, I have the Edit Menu ticked, just can’t find the Menu Deisgn Mode anywhere?
Understand, in the main UI there is a pen/pencil icon in the top right of the Tree Menu section. Select this.
ctayts
Thanks Ctayts, found that, can you tell me how to link the report to the new menu item please.
Different matter, do you know how to change the text in the body of the e mail for e mailed invoices?
Ok, so you need to add the report as a Feature, link to Role(s) and then add to menu.
If you open the layout in Report Designer. Select the Report menu (at the top) then Email Settings.
Hi, I am new using sage 200 and I have two issues: 1.- I cant write the cents when I am trying to book an invoices. 2.- I cant downloand any report (Like a trial balance)… The system doesnt do anything. How Can I resolve these issues? Thank You in advance!
Hi Catyts,
I think my last comment got lost. So I post again.
one of our customer use Sage UK 200. We try to export financial data like opening balance and transactions from tables directly into our system. However the opening balance values seem to be wrong. We use the following query (summerized transactions NLPostedNominalTran and NLHistoricalNominalTran ) to get the opening balance of year 2020. This query has worked for our other customers who also use Sage 200.
Do you have an idea what might be wrong? Or are there any other tables which store the opening balance? Any suggestion is welcome. Many thanks!
Do yu
SELECT
“AC”.”AccountNumber” AS “Dim_GLAccount”
,”RC”.”NLAccountReportTypeID” AS “BalanceIdentifier”
,”SLAC”.”CustomerAccountNumber” AS “Debitor”
,”PLAC”.”SupplierAccountNumber” AS “Creditor”
,”CC”.”Code” AS “Dim_CostCenter”
,SUM(“Trans”.”GoodsValueInBaseCurrency”) AS “ValueInBaseCurrency”
,”Trans”.”DocumentCurrencyID” AS “TransactionCurrencyCode”
,SUM(“Trans”.”GoodsValueInDocumentCurrency”) AS “ValueInDocumentCurrency”
, “Dep”.”Code” AS “Dim_Department”
FROM “dbo”.”NLPostedNominalTran” “Trans”
LEFT JOIN “dbo”.”NLNominalAccount” “AC”
ON “Trans”.”NLNominalAccountID” = “AC”.”NLNominalAccountID”
LEFT JOIN “dbo”.”NLDepartment” “Dep”
ON “AC”.”NLDepartmentID” = “Dep”.”NLDepartmentID”
LEFT JOIN “dbo”.”SYSAccountingPeriod” “Period”
ON “Period”.”SYSAccountingPeriodID” = “Trans”.”SYSAccountingPeriodID”
LEFT JOIN “dbo”.”SYSFinancialYear” “FY”
ON “FY”.”SYSFinancialYearID” = “Period”.”SYSFinancialYearID”
LEFT JOIN “dbo”.”NLCostCentre” “CC”
ON “CC”.”NLCostCentreID” = “AC”.”NLCostCentreID”
LEFT JOIN “dbo”.”SLPostedCustomerTran” “SLTrans”
ON “Trans”.”UniqueReferenceNumber” = “SLTrans”.”UniqueReferenceNumber”
AND “Trans”.”NLNominalAccountID” = “SLTrans”.”SLCustomerAccountID”
LEFT JOIN “dbo”.”PLPostedSupplierTran” “PLTrans”
ON “Trans”.”UniqueReferenceNumber” = “PLTrans”.”UniqueReferenceNumber”
AND “Trans”.”NLNominalAccountID” = “PLTrans”.”PLSupplierAccountID”
LEFT JOIN “dbo”.”SLCustomerAccount” “SLAC”
ON “SLTrans”.”SLCustomerAccountID” = “SLAC”.”SLCustomerAccountID”
LEFT JOIN “dbo”.”PLSupplierAccount” “PLAC”
ON “PLTrans”.”PLSupplierAccountID” = “PLAC”.”PLSupplierAccountID”
LEFT JOIN “dbo”.”NLAccountReportCategory” “RC”
ON “AC”.”NLAccountReportCategoryID” = “RC”.”NLAccountReportCategoryID”
WHERE
YEAR(“FY”.”FinancialYearStartDate”) < 2020
AND "Trans"."Reference" ‘YEAR END’
GROUP BY
“AC”.”AccountNumber”
,”SLAC”.”CustomerAccountNumber”
,”PLAC”.”SupplierAccountNumber”
,”CC”.”Code”
, “Dep”.”Code”
,”Trans”.”DocumentCurrencyID”
,”RC”.”NLAccountReportTypeID”
UNION ALL
SELECT
“AC”.”AccountNumber” AS “Account”
,”RC”.”NLAccountReportTypeID” AS “BalanceIdentifier”
,”SLAC”.”CustomerAccountNumber” AS “Debitor”
,”PLAC”.”SupplierAccountNumber” AS “Creditor”
,”CC”.”Code” AS “CostCenter”
,SUM(“Trans”.”GoodsValueInBaseCurrency”) AS “ValueInBaseCurrency”
,”Trans”.”DocumentCurrencyID” AS “TransactionCurrencyCode”
,SUM(“Trans”.”GoodsValueInDocumentCurrency”) AS “ValueInDocumentCurrency”
, “Dep”.”Code” AS “Dim_Department”
FROM “dbo”.”NLHistoricalNominalTran” “Trans”
LEFT JOIN “dbo”.”NLNominalAccount” “AC”
ON “Trans”.”NLDeletedNominalAccountID” = “AC”.”NLNominalAccountID”
LEFT JOIN “dbo”.”NLDepartment” “Dep”
ON “AC”.”NLDepartmentID” = “Dep”.”NLDepartmentID”
LEFT JOIN “dbo”.”SYSAccountingPeriod” “Period”
ON “Period”.”SYSAccountingPeriodID” = “Trans”.”SYSAccountingPeriodID”
LEFT JOIN “dbo”.”SYSFinancialYear” “FY”
ON “FY”.”SYSFinancialYearID” = “Period”.”SYSFinancialYearID”
LEFT JOIN “dbo”.”NLCostCentre” “CC”
ON “CC”.”NLCostCentreID” = “AC”.”NLCostCentreID”
LEFT JOIN “dbo”.”SLPostedCustomerTran” “SLTrans”
ON “Trans”.”UniqueReferenceNumber” = “SLTrans”.”UniqueReferenceNumber”
AND “Trans”.”NLDeletedNominalAccountID” = “SLTrans”.”SLCustomerAccountID”
LEFT JOIN “dbo”.”PLPostedSupplierTran” “PLTrans”
ON “Trans”.”UniqueReferenceNumber” = “PLTrans”.”UniqueReferenceNumber”
AND “Trans”.”NLDeletedNominalAccountID” = “PLTrans”.”PLSupplierAccountID”
LEFT JOIN “dbo”.”SLCustomerAccount” “SLAC”
ON “SLTrans”.”SLCustomerAccountID” = “SLAC”.”SLCustomerAccountID”
LEFT JOIN “dbo”.”PLSupplierAccount” “PLAC”
ON “PLTrans”.”PLSupplierAccountID” = “PLAC”.”PLSupplierAccountID”
LEFT JOIN “dbo”.”NLAccountReportCategory” “RC”
ON “AC”.”NLAccountReportCategoryID” = “RC”.”NLAccountReportCategoryID”
WHERE
YEAR(“FY”.”FinancialYearStartDate”) < 2020
AND "Trans"."Reference" ‘YEAR END’
GROUP BY
“AC”.”AccountNumber”
,”SLAC”.”CustomerAccountNumber”
,”PLAC”.”SupplierAccountNumber”
,”CC”.”Code”
, “Dep”.”Code”
,”Trans”.”DocumentCurrencyID”
,”RC”.”NLAccountReportTypeID”
I have just been migrated to Sage 200 from Sage 50. My supplier and customer accounts were migrated but there are fields that haven’t gone over. I have tried updating them via the import accounts options but I can only get the company tab to update, anything on the contacts page just doesn’t get updated. That’s a real issue because the email address isn’t on the company tab. I really don’t want to have to manually enter the email address for 250 suppliers and 300 customers. Is there a way to import these contact details? Thanks
Hi Karen
It depends what email address you are trying to update as the Export / Import & Update only updates the email against the Default contact. Unfortunately as standard there isn’t an import routine for contacts.
You BP may be able to update using a sql script if you provide the details in a spreadsheet. This is something I have done before.
Thanks
Ctayts
I can’t get an email address in at all. The only place I can see for it to go is on the contacts tab.
Hi Karen
I have run a test and it doesn’t look like the Update Import updates the contact details (the export doesn’t export either). I think this is probably a bug.
Ctayts
When exiting the order value discount setup form why would I receive the message that “discounts are inconsistent?”
Are you in the Invoice / Order Value Discounts set up screen in Price Book?
This message would suggest that the Discount Value is not incrementing consistently or is perhaps reducing. It should still allow you to save and use.
Hi
We use Sage 200 in our company.
We have a high volume of customers and use the auto allocation function in Sage to allocate receipts against customer invoices. When carrying out this auto allocation process the date for the auto allocation must be manually entered (eg) 31.07.2020.
Unfortunately the manual date for mth end July 2020 was entered using the wrong year (ie) 31.07.7020 as opposed to 31.07.2020. This has had a knock on effect to our debtors aging reports. Our current external sage support team advise this only solution is to manually amend each customer’s allocation, this is an option but we have thousands of customer and will be a very time consuming fix.
Is there any other fix that could be applied?
regards
Hi Bernard
Strictly speaking your support are correct. Through the standard UI you would need to use Amend Allocations. However, it would be possible to make an update directly in the SQL database. For such a specific error it should be fairly easy to pick up the correct transactions based on the obvious date currently entered.
This isn’t something I’d expect and end user to administer and obvious backup and testing should be completed before applying to Live.
ctayts
I agree with ctayts.
This is an easy fix which can be performed in the database directly, but I would expect it to be a chargeable activity from your BP, if they’re willing to do it. Some aren’t comfortable modifying SQL data tables, and it’s also important that they deal with the Nominal Ledger transactions too.
Is your July period closed, as this will also need to be considered, and likely reopened in order for the correct monthly balances to be calculated.
Hi Ctayts
I have admin rights, how do I give them to another user?
For access to the System Administration the user will need to be a member of the Sage200Admins user group on your network and then install the System Administration from the Installers directory.
You would need to l
Hi ctayts
We have Sage 200c Professional. We do our banking with Barclays.Net. We’d like to have all bank transactions fed to the Cash Book with no manual re-keying. How can we make this happen?
VT
Hi,
Again many thanks for any advice provided.
I’m looking to import nominal transactions but seem limited in the amount of data I can import.
I would like to import the transaction value as well as the base and also the currency but don’t seem to be able to. Any way of adding extra columns to a template file.
Hi Hayder
What is the transaction for? If you are using the Nominal transactions import then this is only a base transaction value as that is all that can be posted to the Nominal. Nominal is only Base Currency.
There isn’t a way of adding additional columns to any of the standard import routines without some form of development.
Thanks
Ctayts
Hi,
Once again thanks for replying. I’m having rounding issues on the purchase transaction import template. Is there anywhere in sage I can set the rounding.
Many Thanks
Hayder
Hi Hayder no sorry!
Hello,
Many thanks for your previous help. I have a question on suppliers and customers. Is it true due to FX handling in sage you need to create duplicates of suppliers and customers in the different currences they invoice you in?
Many Thanks
Yes that is correct, can only have one trading currency. But can enter payments and receipts in any currency. Could even make payments / receipts with different currency to bank and supplier / customer.
E.g. Bank = USD, Debtor = EUR, Receipt = GBP
Hi,
I have quick question on supplier transactions and imports. I have been able to import a supplier transaction successfully but I want to import 1 invoice with many analysis codes. I don’t seem to be able to do this.
Can you help please?
Hi Hayder
For supplier transactions using the PL transactions import this would mean the transaction analysis codes would need to be used. These are specific to the Nominal Analysis . You would be able to specify a NominalAnalysis TransactionAnalysis code per Nominal account specified.
Hi,
Quick question is there any data migration tools for sage200c. I want to reverse transactions on the subledger. There is quite a lot of transactions and doing this manually will not be possible?
Kind Regards
There isn’t anything as standard which will do this. There are imports type tools like Eureka Data Exchange, but this won’t allocate.
At Smith Cooper System Partners we have our own API which we would utilise for this sort of thing.
My sage 200 will not close down ! It is stuck on a report screen
This was a common issue in older versions. You will need to End Task on Sage 200 via the Task Manager
Hi. I’m using sage 200 and I’ve accidentally deleted transaction on a customers account. How can I reverse this error and reinstate the invoices
How did you delete it?
Transactions aren’t deleted in Sage 200, they are Archived or Puged so the balance remains as per the transaction.
Was it allocated?
Hi How can I downgrade my sage 200 to an older version that works fine for me
Which version were you thinking about? 2015 is end of life and 2016 has been put into extended support only. So only 12 months left.
You would have to create a data set from scratch with opening balances to down grade. But I don’t see how anything would be so drastically different between in support versions.
What is your actual issue?
Hi Ctayts
Is there a report in Sage 200 that gives delivery addresses by post code, and can be exported to Excel?
Thanks, Matt
Hi Matt, there is now a SOP Customer Delivery Addresses Report in SOP > Reports > Maintenance .
This is Grouped and so sorted by Customer Account Number then CustomerSeliveryAddressID but could easily be amended so the grouping is by PostCode instead of ID.
Ctayts
Hi Ctayts, is it possible for me to migrate data from Sage 50 into a new company on Sage 200?
Hi Matt
Yes there is a Migration Tool which your business partner will have access to. This is a BP tool though.
The data will need to be validated through this processes and I would always advise that this process is not always possible and will depend on the validity of your Sage 50 data.
If this tool does not work then there are standard import routines which can be used. This will allow the import of static records such as Nominal, Customers, Suppliers, Stock etc. And also imports for Opening Balances.
Ctayts
Hi, I wondered if you could help with workspace designer using linq.
I have approx 30 bespoke tables which have been compiled and added to our sage 200 extra system by professional devs, which include various columns in each.
I know how to list the data using var q = from and so on, but I am struggling to link 2 or more data contexts using a join statement, as not all the data context tables have matching primary keys.
Please could you show me an example of how to list and show data from 2 contexts.
Below is an example of what I am aiming for, and your help would be greatly appreciated:
var q = from SOP in cxt.SOPOrderReturns
from WF in cxt.I8TFWorkFlows
select new
{
SOP.SOPOrderNumberID,
SOP.SLCustomerAccount.SLCustomerAccountID,
SOP.DocumentNo,
SOP.DocumentDate,
DocumentStatus = SOP.DocumentStatus.Name,
SOP.SOPOrderReturnID,
WF.I8TFWorkflowID,
WF.ColourProofRequestedDate,
WF.ColourProofApprovedDate,
WF.ColorProofSentDate,
WF.AmendmentToOrder,
};
return q;
Hi guys. When I select a single works order to print in Sage 200c it previews every single one, is there a setting I can change so it only previews the one I have selected?
Cheers
Stuart
Stuart,
Are you using Sage Manufacturing or Sicon Manufacturing?
Either module shouldn’t do this, but it could be a layout issue.
Thanks,
Tony
I’m using Sage Manufacturing. I can access Report Designer but what setting to change has foxed me! Preview while using report designer also returns every works order ever created. I am trying to print/edit job sheet and operation card which have been messed with before by an ex-employee. Pick list and Route card seem to work fine, only previewing the selected works order.
Any help appreciated!
Stuart
If I am understanding correctly then you will need to enable a Criteria
Hi All we are running Sage 200c Professional and we are having an issue where users cannot save changes when they deselect icons along the top by right click and removing the tick, I have checked admin and “edit menu” has been selected, yet it is not remembering the when we close and reopen sage 200. Not a big issue but would like to get it fixed
Hi Tony
In Sage 200 2015 when we un-ticking the option at the navigation bar sage 200 would remember which icon we did and didn’t want to show even when we shut down sage 200 and reopened it, I will look in to remove their permission in order to get around this issue. From what I have heard this issue has been fixed in the newest version of sage 200 (which was released a couple of weeks after we upgrade to the newest version at the time) and if we want to upgrade to the new version to fix this issue it will cost us a day pay for our business partner and a day down all to get some back that we had before we upgraded. Sage really needs to start a “quality of life” program to fix little issue and start turning unhappy customers to happy customers again……however many thanks for you reply on this issue Tony
Kind Regards
Shane
Hi Shane
As Tony said, as standard Sage 200 doesn’t allow you switch off the icons on the list views separately from the menu. So if the feature is enabled for the user the icon will display on the list view.
Not sure if you have had some bespoke?
Not sure on your system but one day to upgrade sounds low. Does this include testing?
Ctayts (@talkingsage200)
Hi Shane
I am not sure if that functionality, it’s certainly not a function I have come across or that is shown/highlighted within the system.
You can access the the Sage community and the ideas hub. You are also able to report Issues and their business impact via your Business Partner.
Also I’d expect your Business Partner to be available if Functional issues which really are causing Business Impact. They should be supporting you closely if you are not happy.
I need to set the precedent order for selecting stock items for manufacture. I know the default setting is FIFO, I want to select by item expiry date. Can someone point me in the right direction?
HI Mike,
If you go into Stock Control/Stock Records/Stock Order Fulfilment.
Here you can change groups of items, all items, or just individuals by using the Windows Shift+Click or Ctrl+Click options.
Thanks,
Tony
Hi, I am trying to close 2017/18 year and start posting to 18/19. But i will have some audit adjustments for 17/18 soon. Once i close 17/18 i cannot post audit adjustments to that year. So my final accounts will have to be done, off Sage 200 & any audit adjustments will have to be posted on to April (18/19). Then when i run my 17/18 P&L, it will not match the final audited accounts.
I am wondering whether there is a way around this. Is it possible to post audit adjustments to a 13th period?
Any thoughts?
Oh well, I found the answer…
There is a “Previous year journal entry” option under transactions entry. We could post any audit adjustments using that option.
Yes correct, often people will copy the dataset as a year end company for the auditors to work with before close off and then post the adjustments as you have suggested.
Hey, I am selecting sales orders and choosing print invoices – they usually appear I the spoiler but they aren’t?! Everything else I send to print is appearing but not the invoices I’m trying to raise or credit notes?
When I select print again it says there’s nothing to print…..
Hi
I’m scratching my head how to use advanced conditional formatting to alter the background colour of text based on an analysis code value. I can alter the text colour, that’s given in the examples but I can’t find what code to use for the background instead, is this possible?
Thanks
https://talkingsage200.wordpress.com/2012/06/06/using-conditional-formatting-in-reports/
Hi Ctayts
We are looking at Sage Paperless options, Sage recommend Paperless Europe, our business partner wants us to use Draycir Spindle Document Management, do you have any recommendations?
Definitely I recommend Draycir. I have not had any experience of Paperless so cant comment about their product.
Spindle is definitely a Great solution though.
Is it possible to e mail invoices directly from Sage?
Yes you can Matt, if it is simple email and you will email the whole of a batch.
Workiing on sage200 and trying to add new menu item however no targets are listed?
Can yo anyone help please
Are they in set up as features in System Administration?
Hi ctayts
I know how to remove a disconnected log in, but on the same dialogue box is a users tab, which show who is logged in, if a highlight a user that is logged in, the log off button stays blank, meaning I can’t log them off the system, is there any way of enabling this, this box also has a column for activity, but shows all users doing stuf at the same time, do you know what this column actually represents?
Again, many thanks
Hi Matt
You cannot Force the log off of someone. To much scope for causing data issues should they be entering a transaction.
There are times that users show as active within System Administration but are not. In this scenario there are ways within the database of removing these but you need to ensure that the sensing is genuinely not in use. Therefore I suggest you have your BP involved here.
Are your suppliers correctly configured in terms of their trading country details?
Can someone help on Intrastat on S200. we only import over from europe goods which have the Stock set up to correct commodity codes , accounts set up with country origin codes for Europe , boxes ticked in POP and PL to allow Intrastat – all seems to be correct however when trying to generate only one supplier being picked for reporting – ?? Doe anyone have idea why my other 4 suppliers not generating data? Help Please 🙂
Hello,
I am new to sage and as a stock controller I have been used to searching for items by location on a previous stock system.
But with sage i have to find the item then the location. Is there a way i can get all locations with stock.
Even if i could add the bin column to the stock list in sage but it does not give this option.
Can you help?
Thank you
Adrian
Hi Adrian, you should look at the Workspaces available within Sage 200. You can filter on the columns which should allow a location search.
Hi
Using Sage 200 Extra 2016, is there anyway of getting the product weights to appear on picking notes?
Thanks
Matt
Hi Matt the Weight field is available in the StockItem table. If you use the report designer the Picking List can be amended.
Hi, I want to downgrade from Sage 200 back to Sage 50c as it’s far easier to use and better suited to my business, but i am being told that all my data will be lost as Sage will transfer data for an upgrade, but not for a supposed downgrade. Can this be done? I have a lot of stock and large customer base. Thanks
Hi Sarah there is not a migration tool to take convert the data back in to Sage 50 (there is from 50 to 200).
The data can be transferred using imports so you are looking at static data and opening transactions. Call us at smith cooper system partners if you want any assistance with 200 and if you still decide to downgrade we can help with this also. ctayts
Hi I am using OCDB to extract data from Sage 200. However I am having trouble showing the Purchase Ledger by Project code. This is because I cannot make the appropriate link between the two databases. DO you know if i have to go through an alterative database to make the link or how I could achieve this ?
Hi pedro,
The links between Project Accounting and the oyher modules aren’t great at all. However they can be done.
Not an easy one to describe online, so please do drop me an email and I can call to discuss.
Best regards,
Tony
Hi .
we having an issue when running the month end report for SOP invoices.
We are using Sage 200 SP1 with a Win10 client with Intel iCore 3 . 8GB RAM to generate the report and we keep getting the preview message .
The Report could not be generated .
Out of Memory : The report being generated is too large for the amount of memory currently available .
I have looked at all sorts of information sayings its possibly the default printer driver or the Sage PDF printer driver.
Have you come across the is issue before ?
Thanks Michael.
Hi Michael,
Which version of Sage 200 are you using?
If I’m honest, the core i3 processors are not great at processing data, so this could be your limiting factor. With 8Gb RAM this should be enough.
How many invoices are you reporting? Perhaps there’s a better way to report on this data? Perhaps using some direct from Sage, Excel based reports?
What is your default printer? I’ve not come across this causing an issue before, and I’ve been doing this for some time now (17 1/2 years!).
Best regards,
Tony
There shouldn’t be any issue with that spec.
Is it limited to one machine?
Have you tried the server at all?
How many invoices are we expecting? Is it just that report?
Hello,
Do you know why, on a new Sage 200 Extra 2016 install, the navigation area/menu would be completely blank?
The homepage loads, the banner loads, even the searchbox loads, but nothing down the left hand side. I’ve tried it on an old company and a new test one.
I can’t find anything on the Sage help centre about this issue.
Many thanks,
Have the Features been enabled against the Role(s) of the user ?
Hi Craig,
Thank you, had a feeling it would be something simple I’d forgotten. Thanks again.
Hi,
We have have Sage 200 install which for the most part is working fine. One user however is experiencing signficant delays when trying to do a Print Receipt Run. Printing just 20 receipts can take as long as 10 minutes to produce on our high-end Sharp Network Printer. The particularly peculiar part is that he can print other documents in perfectly timely fashion. Other users on the Windows Network don’t have this problem. Any suggestions what might be causing this?
Thanks!
Andrew
Good afternoon,
I have 2 questions which I hope you can assist with.
1) With Report Designer, is there any good way of working with it for exporting into excel. The guidelines on the help data from Sage is severely lacking and you tend to be stuck with Merged cells or “####” as the cell is too small to show the values.
2) Is there anyway on Sage200 to batch multiple reports to run in 1 go rather than having to run each one individually, I believe this was possible on Sage50.
Thanks
Simon
In the Report properties you can choose the sections the export.
The reason you will get merged cells is if fields overlap on the report, make sure fields are aligned or spaced even in adjacent sections.
Sage 200 doesn’t batch run Report Designer reports (I am not aware of Sage 50 doing this either)
Good morning
Firstly thank you for your response.
Things I have tried that have yet to succeed-
1) Spacing the fields out evenly.
2) Joining the fields together side by side.
3) Shrinking/Stretching the field size.
4) Alignment of any kind.
I currently have 3 fields with the exact same dimensions/formatting yet 2 are not merged and 1 is. Is there something outside the row footer/group beak footer itself that might be causing this? – Perhaps I am focusing too much on the footer information.
Thanks
Sage can’t seem to find the networked printer any more. It was working yesterday and today one of the ladies said it doesn’t print. Get this though it’s just one report that isn’t working ever other report does work. Any Ideas? what file do I have to edit to get this report working I can’t delete the report so that is out. Is there a place I can call that report up and edit the location of the network printer?
Hi there,
I have designed my own debtors report and would like to pull through the below memo details however I can’t find these to add into my report from the report data field options:
Date
Text
Please can you advise how I do this if possible?
Thank you
Louise
Hi Louise
You will need to add the SLTranMemo table via the Join editor. Link to SLPostedCustomerTrans using a Parent join on the SLPostedCustomerTranID.
Then you will need to add a section for the transaction details (SLPostedCustomerTrans.SLPostedCustomerTranID), move the transaction details into here and add the Memo info to the Details section.
ctayts
When following the above and adding the trans memo data, if I then run the report for all aged debtors is detail it will only show the lines with a memo, not all the lines (if they have not memo). Is there a way around this ?
Hi,
In our company we have users who have Sage 200 installed on laptops. When in the office they typically have a monitor attached. So as the monitor is significantly larger than the laptop screen they will do most their work in the attached monitor. The Sage seems to remember where the print spooler window was located last time it was open. The problem is that when users do not have the second monitor connected and the print spooler was last open on the second monitor the window does not seem to open. What I suspect is happening is that the print spooler window is being opened at the same co-ordinates as previously however that location is now off screen.
Is there any (user friendly) way to get the print spooler window to move to the current screen.
CTRL+Alt+SpaceBar .Then use the arrow keys to select ‘M’ove, and use the arrow keys to move the screen where you want it to be.
Hope this helps
On my screen when I want to log off at the end of the day I have the following message regularly appearing.
You have one option still running please close all remaining options before exiting.
I know they are hidden but how do I close them ?
If version 2015/2016 it should specify at the bottom how many, and which screens are open. Here you can select ‘Close All’
Hi,
I’m a simple AP clerk who has to find out what accrual has been made to a GL. How do I do that, please?
Thanks!
Hi
I’m trying to export a dispatch note from an order into a .csv excel file. However, every time i export and save the file, the worksheet is just blank when opened. Any ideas why it would do this?
Hi,
I wonder if anyone has seen similar issues to us? We recently had an upgrade to SAGE 200/2016 financials. We experience really slow performance from desktop machines for virtually any operation. However we have a terminal server which runs 10 times faster, using the same IIS server. I have checked available memory, CPU usage, network traffic, database transactions and statistics on each server. All of these are pretty much idle, but still any operation from the desktop crawls along. What I do notice in the IIS logs is that every single transaction between the PC and the IIS has two requests, the first with a 401 status then the second with the authentication header and which takes about 200ms to complete. It strikes me that the client app should store some credentials so that there is not an authentication round-trip in every single communication. I wonder if there is something slow in authenticating each message.
I have asked our SAGE partner what things we should measure to try to get to the bottom of this issue, and their response is just that SAGE don’t give any performance analysis documentation to give us a clue what to measure to try and address it.
I don’t know if there is some incorrect config on IIS?
Thanks in advance!
This may, or may not help, but if you go to Control Panel–>internet Options–> check the security settings for the local and intranet sites. Make sure the last option has ‘logon using current credentials’ selected. This should remove any authentication issues that you may be experiencing
Hopefully you can help!
We are using Sage 200 v2011 and have being using it for a couple years without an issue. We use the project accounting timesheet import functionality to allocate data to projects, which in the last few months has started to error for no apparent reason. The format of the text files are the same, we aren’t using or doing anything different, but the import file hangs and then doesn’t import, but neither does it show an error. There is nothing specific about the number of rows or anything related to a specific project. It just errors most of the time and we have to split the file up in to lots of small files to get it to import. The files contain low thousands of rows and sometimes less than a thousand rows.
We’ve tried checking when no-one else is logged on to Sage 200 doing anything. We’ve tried doing it at different times of the day, but to no avail. The database load is minimal and we are stumped.
Can you give us any pointers as to what to look at? Our BP is also stumped!
It sounds strange, is there a set number of rows that it is allowing you to import at one time?
Has a service pack or anyone been installed/changed?
No set amount of rows. It is different each time. It isn’t the same type of rows that causes issues or the same project number, so it is really strange.
We haven’t added / installed any service packs either since it has been happening.
Hi, I am importing csv “sales transactions” into sage 200 and the help files are vague.
I have a fair understanding of it but wondered is there an example of importing a sales invoice where the full amount needs to be split into varying tax codes and nominal codes.
So I have a sale invoice for joe bloggs for say £100 but there are 4 x products with 4 different nominals and 4 different vat codes..
The example files should help. Basically you add the additional fields for each different VAT Rate and Nominal. Columns for VAT Analysis /1 and then for 2 etc. Same for Nominal Analysis /1 and then 2 etc.
Hi
We have uograded from Sage 50 to Sage 200. Was wondering if you can advise best way to handle posting monthly credit card transactions for reconciliation. We have a lot of transactions and need to allocate to various nominals, in 50 we were populating an excel file (with vlookups etc) and then using the transactions import file so it posted to nominals and projects. Very time consuming now entering manually as we are told cannot import into credit card. Thanks in advance if you can help!
The issue is it posting to Projects as well as the Sales/Purchase/Nominal transaction imports do not analyse to projects.
Hi Guys!
I need you help with Sage report designer.
do you know how I could Combining rows of data into a single value like the following sample?
I have a data in rows like this for id 1
id1 a1
id1 b2
id1 c3
id1 d4
id1 e5
I like to combine it as “a1b2c3d4e5” into a single value for id1. Thanks in advance.
Hi, I read on your recent post that there is a new API within Sage 200 Extra. Is this only available to developers who have subscribed to the SDK or is it accessible to anyone? If the latter, could you post a link that shows more detail? Are there any open source APIs for Sage 200? I saw something called SData2.0 and then saw something that said it had been superseded by developers.sageone.com (but not sure if that covers Sage 200). Thanks!
Hi,
Just asking the question to see if anyone else has encountered this:
Has anyone come across Speed/Performance problems with the Spooler in Sage 200?
I know the spool structure changed in Sage 200 2013, but more so since 2015, the more items that build up in the Spooler the slower it is for the spool window to open up (anywhere up to 60 seconds to open)
Sage Support are aware of this and said they have had calls on this, but the standard reply is its the new setup/structure and basically don’t use the spool to store documents…
(I can see the point about not letting the spool build up, but in busy production environments like lot of BOMS/SOPS/ Despatches etc… having to maintain the spool is not always an option)
Curious to see if anyone else has comer across this.
Thanks,
Paullyie
Hi
Looking for some help.
Is there anyway to import cash allocations to sales ledger invoices using xls or csv?
Thanks
Hi Pymmie
There isn’t a standard routine that can do this I am afraid.
ctayts
Hi
Is there any way to run a report of the latest buying price of all goods we have purchased? Or at least a column we can insert on the Stock control page in Sage?
Hi,
I am a new employee at a company that uses Sage pro 2011. I signed in to it. But there is no icon at the bottom of my screen. I go back into the program and it just shows a log in page. I put my info in again and enter but it says that I am already logged in. It doesn’t show in task manager. Any suggestions????
Hi Iv recently deleted something from my spooker is it gone for good?
Hi
I am just wondering if it is possible to create a notification window (a note of instructions) that would appear when I begin to process a sales order on certain customers. If not, do you know of any alternatives that could be set up similar to this. It is needed to pass along specific instructions to people unfamiliar with customers criteria whilst processing orders.
Cheers!
Not as standard, we have created this as bespoke for a number of our customers .
Any luck with this one? Can you print a returns note in SOP, not the credit note but a returns note to use as collection paperwork?
Hi Matt
No specific option like with Sales Orders, however a layout could be created and made available as a report on the menu.
Ctayts
Hi, we have a problem.. We look after a Sage 200 customer along with the rest of the network. We use Hyper-V to replicate all their servers back to our site.
On the customers live SAGE 200 server, they have a main DB and they query it with BI. The problem is that the live database is growing at quite a rate, looks like approx 10GB a month.. This, along with the server is replicated back to our office every 15 mins using standard Hyper-V replication. I have ask the company who support the Sage installation if there is any way that some kind of housekeeping can be run on this DB so as to bring the size of it down as replicating is starting to creek a bit (it is currently 28GB and growing). They tell me that there is no way to limit the size of the DB and it will just keep growing as if they housekeep it all the BI query’s will not work… So, the answer seems to be that this file will just keep growing and there is nothing in the world we can do about this.., it will just keep going, 100GB, 200GB etc…
The question is, is this true..?, as we will have a major problem on our hands soon as the size of the replication will just bury us.., and we will need to add more and more disk space to the server..
Any help and advice is very much appeciated..
Hi, wow that sounds uniquely large. Is it the BI database (Normally DW)? Or the Main sage DB?
Either way, at the end of the day they are SQL databases and therefore normal SQL administration can be applied.
I am certainly not a SQL expert but are maintenance plans in place to Shrink Database or Clean up history etc? Also are DBs set to Simple Recovery mode? This will clear logs during Full Backups (again maintenance plans to run Full backups in place?).
Ctayts
Hi there, thanks for your feedback, it is the DWH db.. What the Sage 200 specialist company are saying that this DB cannot be shrunk or limited as it will case the BI queries to fall over.. My problem is that this DB is growing at 10GB a month, and it’s already 28GB.., they say there is nothing you can do…, it could get to 100GB or 200GB and there is nothing you can do, which I find hard to believe..
Can you print a returns note in SOP, not the credit note but a returns note to use as collection from site paperwork?
Hi
My question is Sage/SQL related. I am trying to build a view that has all necessary Purchase Order information. I want to include Reference and Second Reference from Transaction History table (When we book the PO in we key in warehouse GRN number in one of the references). Is it possible to link POPOrderReturn table or POPinvoiceCredit table to TransactionHistory table in SQL? Is so which tables link them?
Thanks
Hi,
I was wondering if you could by any chance help.
I have a client who uses sage 200 and has never came across any issues in the past. I have ran a TB and now this doesn’t balance. I am assuming this has happened by someone making a posting and the computer has crashed and therefore the full entry hasn’t been posted.
The amount is so small that I am happy to write it off but just don’t know how I would go about doing a ‘one sided journal’.
Is there anyway I can fix this without having to restore and old back-up as it would be quite a lot of work to have to re-enter.
Any help would be greatly appreciated.
Thanks
Run a Prove Ledger Balances (Accounting System Manager > Utilities > Prove Balances > Financials) on the Nominal. This will make a balancing posting to the nominal which you can write off using a normal journal.
Can I amend pricing after I’ve printed sales acknowledgement and re print? If so how do I do this?
Yes, amend sales order.
Hello,
I have just had to complete a reinstall of Sage 200 2010 after a virus hit the files. I’ve installed it, but we have no modules available. The only one that is available in the Navigation Pane is Acounting System.
How can I get back the modules that we had?
Thanks
Nat
Hi Nat
If you created a brand new Configuration database then you will need to enable the required features for the Role(s) assigned to users. This is within System Administration.
If the virus on impacted the program files then you should have been able to point to the original Configuration database.
ctayts
Hi Ctayts,
Thanks for your reply. I have enabled all of them but there is still no success. I also received an error when trying to access workspaces (as they are available). The error can be viewed here –> https://www.crosbys.co.uk/downloads/1457697159Capture.
Thanks
Nat
It’s difficult without being able to look at the machine. Is this happening on the server, all clients or just this one client?
i figured that would be the case. This is the server and the client as they are both on the same machine. It’s only used for reference hence the lack of urgency.
In Sage 200, we have some sales ledger accounts with zero balances but still showing acitivity, i.e. 1 sales invoice and 1 receipt but they are not allocated. How can we clear these down without adding a sales receipt?
As you say, you have a receipt. So you need to allocate the Invoice to the receipt via Sales Ledger > Enter Transactions > Allocations
Hi,
I am having trouble creating a report. We have Sage 200 V2015, i am trying to add a filter into my report that will only pull back details if the AUDDIS Submission date (direct debit bolt on module) is over 5 days from todays date. I have tried using the NOW function but no joy. Any idea how i can create a filter that has date differences in?
Try [DateField] > NOW() + DAYS(5)
so, good start, thanks. It works until i add a criteria of only selecting a particular due date (was going to use this report as a validation of upcoming direct debits)
I get a message ‘No Applicable method ‘Now’ exists in type Dynamic class 5′
Hello,
I am trying to change some stock items to Phantoms. I have been removing any stock associated with the items via the Internal Issue. But sometimes when I try to do this I get the error message ‘The quantity is greater than the total stock holding at the location’
Any help would be much appreciated
Thanks
Jonathan
It may be worth running a prove balances on stock to validate the stock control module.
Hi. I am trying to take a Copy of a Live Sage 200 database as I would like to link this to a “Play” company we have set up on Sage 200. I have installed Microsoft SQL Server Management Studio, but this doesn’t appear to have “Copy Database” functionality. Could you please assist? many Thanks
You need to right click the database, Tasks, Backup. Then do a restore to a New database. Have you got the correct type of SQL? Can’t be Express.
HI
I’m currently having an issue with allocations. One of our stock items is stating it is fully allocated, but I am unable to find anything it is allocated to, meaning it is then unusable for other actions. When going into the amend allocations window, and then entering the item, it doesn’t show any active allocations, so I’m unable to clear/amend this phantom allocation. Is there anyway to fix this at all?
Could it be allocated to something other than SOP? Stock Allocation or BOM (if used). There is an allocations report in Stock which will help you to identify.
If still nothing to show then you should run a Prove Balances validation on Commercials. This is found within Accounting System Manager > Utilities.
Just run the validation option and it should identify any issues and state of it able to fix it. Suggest you get guidance from your BP and take a backup before running the fix option.
Ctayts
Hi Craig
Is it possible to add the username of the person who printed a Picking List to the printout (not the name of the person who created the Sales order but the person who actually printed the paper version)?
Hi Gordon
I don’t believe this would be possible without some sort of bespoke. There is no specific picking list record as it takes details from the order and allocation details.
Craig
We have recently taken up Sage 200 Extra Online and want to create our own reports. One problem we have is that we want to use one of the analysis fields in the sales ledger as a criteria but we need it to have the default value as “In” which is not available. This was something that could be fixed in Sage 50 but have no idea how to do so in Sage 200 online. Any feedback would be welcome.
Hoping you might be able to help!
I posted a journal dated 30th Nov 15 and set it to reverse out on 1st December 15. It was made up of 8 lines (so 16 in total including the reversing entries). So far so good. Now I’m just in the process of posting my December numbers and I’ve noticed that the journal has not completely reversed out. If I go into one of the relevant nominals and double click on that journal entry, I get a message that states ‘The set of nominal transactions is incomplete’. When the journal is presented on the next page, it shows only 15 lines of the 16 line journal and in the top left ‘value’ box, it shows the amount that the journal doesn’t seem to balance by. I ran a TB at 31st Dec expecting it not to balance, but it does…. So the posting must have gone somewhere! I then ran a nominal activity report for the year to date and searched for the missing amount (expecting it to be in a suspense account somewhere) – again no such luck. A colleague suggested the amount was ‘stuck’ as a deferred posting so to post any deferred postings, but this didn’t seem to work either. I’m really at a loss! It’s quite a significant amount so I can’t just ignore it!
Any help appreciated!
Is there anything in waiting postings with the same Journal number?
Incomplete normally suggests that part of the journal has been archived. However your figures would still be correct.
It could have been archived and if so will be in Historical Transactions (assuming archiving is enabled).
Has anyone managed to install Sage 200 2015 release version onto a Windows 10 machine?
I have a machine that had Sage 200 2015 SP2 installed onto the Windows 8.1 O/S and then upgraded this to Windows 10 and everything was fine except for the Sys Admin MMC dll error that I fixed…
What I am struggling with is putting a fresh install of Sage 200 2015 Release build onto a Windows 10 machine from Scratch… The install fails and ends prematurely after about 25 seconds.
I have tried this on 3 clean windows 10 machines now with the same thing happening on each and even tried as client install from a Server from the installers folder…
Anyone seen this situation or had a similar issue?
I haven’t experienced any issues like this I’m afraid. Presume you have correct permissions! Are you running from the Installers folder too?
Sorry read again and have used Installers
Hi Craig,
Have a niggly query on the BOM hope you can help a wee bit.
On previous versions (Pre 2010/2011 before Manufacturing Manager for costs was introduced)
It was simple to create a cost item, give that cost item a charge rate and add it to your BOM with a QTY of that charge rate
(i.e. Labour item “Packaging” at £10.00/hr, then a QTY of 0.5 on the BOM for this item – would cost £5.00 for this item)
On the newer versions (2010 onwards)
It does not appear to be possible or as simple to just add the BOM Operation with a QTY;
am I right in saying you can only add the BOM and you need to specify a Time (Hr/Min/Sec) instead of a QTY
It appears that the operation will not be costed on the BOM if there is no time entered
But there is a Quantity field that becomes available if using “Piece Work” but this calculates the BOM cost as Charge rate divided by Quantity, (I’m looking for this to work as Charge Rate multiplied by Quantity)
Not sure if I’m missing something, hope I have explained it correctly above
Thanks in advance,
Paullyie
Just in case it is of any use to another user;
Does not look like this is possible with the newer format of BOM/Operation Costs since 2010
So left with only two options:
1) Change the charge rates to match the new BOM format
2) Use miscellaneous stock items,
Add Miscellaneous Stock Items as components instead of Operational Cost Items, Miscellaneous Stock Items will allow you to use the old format of Qty
(Only downside is they also appear on picking lists and build reports etc..)
Hi
I’m new to Sage 200 and I’m looking at reporting a sales divisions results for the Month, Quarter and Year to date against plan, forecast and prior year.
In my old SAP life I had to export into Excel and link the results to various files etc, is this the case with Sage 200…
Just looking for advice on management reports (both numerically and in graphs) against forecast to produce at Board Level.
Thanks
You should be able to set this up with Business Intelligence. I assume the divisions are set up as part of the NL structure using Cost Centres or Departments?
Sage 200 BI will do what you need but if you have more extensive reporting requirements it may be worth looking at something like PanIntelligence which have a decent BI add on for Sage 200.
Thanks for the quick response.
Confirmed the divisions are set up using separate cost cetres etc.
I think the company is currently forecasting and planning offline – rather than using Sage – would it be possible to upload these into Sage to give comparable data? Or is there a better way of doing this?
Thanks
Hi, Im hoping you can help.
We are trying to create statements for Overdue accounts only to show debit and credit balances, or debit balances but to not send to accounts with Credit balances past 30 days.
We have set Account balances to “from £0.01” so accounts in credit don’t receive statements
however we cant seem to find a criteria to show Overdue or past 30 days with an overdue balance of £0.01, we have tried setting Due dates and having ‘Outstanding Value’ from £0.01 but that just takes the credit notes off the statements leaving the debit values only,not remove the actual statement.
Sorry if im not making myself clear its a bit of an odd one to put in to words..
basically we want to send overdue statements to accounts with debit & debit/credit balances over 30 days but not to accounts which have either accounts in credit or to accounts with only Credit values in 30 days + and debit values in current.
hope this makes sense,,
Thanks
Hello
In process of creating new Windows Server 2012 install of Sage 2013 R2 and have followed all guidelines etc but for whatever reason when I go into Sage Admin it will not Allow me to connect to a database as all of the options are greyed out.. Had no problem on Windows Server 2008
Have you previously come across any issues like this and is there any guidance you can provide me in order to progress our install
Many thanks
Alan
Which options specifically are greyed out?
Hi,
At the moment, I am using sage 200 v6 with data exchange. We are using the data exchange to import the invoice payment file into sage 200. My question is if customer has one invoice and one credit note(not allocated to invoice as yet) on his account and he paid the difference amount.
Is there any way, I can do the automatic allocation of the credit note to the invoice using csv files.
Hi, Just upgraded to Sage 200 Extra (2015) from Sage 200 2011 and found an annoying bug.
On all lists especially works orders the list does not auto refresh after an action.
Previously the status would update in the list through Allocated/Issued/Completed etc. however now the process if Allocate – Refresh List – Issue – Refresh List – Complete – Refresh List. I have read a previous comment which states there is a setting in manufacturing but I cannot see it??
Thanks in advance for your help.
Hi ctayts,
I hope you can help with a spooler issue I am having at my site. When people click Print Reports it can take between 20 seconds and 30 minutes, depending on the user, to display the list – before they even think about printing something. My support provider has recommended creating a new spool folder which does cure the issue (I have done it in the past) however this means that we have to revert to the old folder every now and then to get old reports.
Is there a way of fixing whatever is corrupt rather than starting again? The current spool folder (which was started about a year ago) is 3.4GB in size and contains 17,000+ files – if that makes a difference.
Any help would be appreciated.
Alwyn
Hi Alwyn
I’d start by ensuring all users have ‘house kept’ their own spoiler via the system and deleted any old / unwanted spool files. (They should do this on a regular basis in any case).
Then (this can be tricky) if you can confirm a date where no files should exist too, ensure files up this date are not within the Spool directory. If they are simply delete (or move for a time).
Failing the it may be that the directory is corrupt and recreating is the only option.
Thanks for coming back to me so quickly.
I have already deleted as many spool files as I can through Sage. I may try deleting the files from the spool share to a certain point in time, but our internal auditors may get upset if I delete certain files, so it might have to just replace the folder and revert back when I am asked to do so.
Sage makes me sad. :o(
Thanks for the info.
What version are you on? It shouldn’t make you sad it can be awesome if set up and maintained correctly 🙂
It is Sage 200 2013. Although you wouldn’t have thought you would need to maintain the spooler folder. It’s been an issue for more than a year, you would think Sage would be keen to fix the issue. In all other areas it seems to run just fine.
Although I have thought of another question; the SQL server process never uses more than 8GB of memory (it is not restricted within SQL Server Manager), is this normal? We only have around 30 users, so it’s probably not going to push the server, I’m just interested.
Hi ctayts, we have recently upgrade from 200 5.1 to 2015 SP1 – all is great with the exception of running filters from the audit trail – these are not running consistently, usually fall over with a database timeout error. We have to crash out of the system to get back in. It’s a terminal server with upto 20 users. We followed all of the recommended server specs. Do you know what aspect of server or sage setup is likely to be causing this problem. Thanks, Matt
Is this something that occurs on the Sage server directly as well? (Presuming as separate Terminal Server).
Hi, I was wondering if you could help. Sorry my request is more of a trouble shooting one….
My Sage 200 keeps crashing mainly when I try to print a Sales invoice and in certain instances while running/exporting reports, posting journals etc. When it crashes I get the following error message :
” Sage200 Desktop has stopped working ” A problem has caused the program to stop working correctly. Windows will close the program and notify if there is a solution.
We have tried re-installing Sage 200 and even the IT as well as support team people seem to not know what is causing this error.
I was just wondering if you knew if there are some instances of Sage clashing with some settings/updates in Windows 7.
Thank you
George
Hi George
A few things to check.
Is this just your machine?
Does the same occur if someone else logs onto your machine?
Does you machine have enough memory?
Are you printing to Spooler? Clear this down.
Try clearing down the temp files Start > Run type %Temp% and press Enter. The files that appear or all temporary files. Select all and delete (there may be a few that can’t delete as they are in use. This is fine.
See if this makes any difference.
ctayts
Hi ctayts,
Thanks a lot for your help. I have tried what you had suggested and I have printed a few times and it has not crashed . Hope the drill worked.
Thanks for your help
Hi,
How do I reprint remittances using Sage 200?
Thanks
Hi Jessica in the Generate Payments screen of the Suggested Payments menu, there is a tick box to re-print payments. This will only re-print if the payments file has not been deleted. It is not possible to print for previous payment runs once have started a new run.
The best options are to keep Remittances in the spooler, save to an area of the network or with the use of Spindle, archive the remittances to supplier specific folders on your network.
Can you please help me to find Currency table and Sales Person table name in Sage 200 UK?
There isn’t a Sales Person table, are they specified as analysis codes against the Customer records?
hi we are using sage 200 , and having issues with sub assemblies coming through on MRP, they are in the BOM but just recently we have had major issues.
we do get the odd time where if a part is in stock it wont com off, but we are now getting alot that are nor coming through, any ideas
hi ctayts, can you help on this , thanks
Is the issue that Sub assemblies are not being built or suggested for build? Have you checked the settings for how to treat sub assemblies?
hi, yes all checked, and should be ok, it seems that when we transferred our data over to 9.02.001 some of our data is not working, if we add new parts the subs come through but any old parts that were on the system previously just don’t come through. It has been passed back to sage , but we are really stuck with this , thanks
Is it possible to add an message field in a report and input an message to the report before it is run. The message may be different every time this report is run. Thank you!
Not really as a free text upon generating.
Thank you very much, your post is very much helpful in Highlighting the important information on my report. Thanks again.
Hi.
Looking at integrating Sage 200 with a couple of carrier companies.. booking consignments on seamlessly from Sage upon invoice creation. I have done a bit of reading and contacted a company called Orbis who seem to have a solution. Would you recommend any other solutions?
Try emailing chris@tilted.co.uk. He has integrations for Royal Mail, DPD and also a Metapack integration. Very helpful 🙂
Generally I have used bespoke development that companies I have worked for developed. Orbis are a respectable Sage partner but not a solution I have used.
chris@tilted.co.uk is another option and is a developer I and the company I work for (Smith Cooper System Partners) have worked with.
Would you please demo the use of Conditional Formate as I want to show a Binitem.Binname to Blue colour when it is starting with X or x
Here is a previous post I have done, hope this helps https://talkingsage200.wordpress.com/2012/06/06/using-conditional-formatting-in-reports/
Hello ctayts.
We currently use a statement written withing Sage 200.
We are trying to write a crustal report outside of sage to replace this.
Can you please give us some idea to the tables we need to look at? Its basically a list of all unallocated / not paid transactions per customer.
Many thanks
Ian
SLCustomerAccount, SLCustomerLocations, SLPostedCustomerTran, SLAgedTransactions
Cheers
Is there any way of unallocating stock items allocated to a corrupted order in sales order processing. Sage crashed and there is now a sales order with a nonsense sales order number which has stock items allocated against it. Is there anyway of purging or deleting incomplete sales orders?
I assume the order number begins with hash’s (#). You will need to get your support to connect up and amend the order in the background.
wouldn’t disconnecting logins work?
Not if the order has the #s as this will have been committed. But these should be cleared also.
Hi, I’m currently having a problem I hope you might be able to help with. Currently in the sales ledger, for customer addresses (‘amend account details’), only the customer name, address line one, town and county are printing out on customer invoices. Therefore all addresses which have just been entered on address line 2-4 (and without the town and county being written in the named ‘town’ and ‘county’ lines are not showing up for invoicing (only line 1 prints). We have over 10,000 customers and are having to manually move each address to the other address lines. Is there a way we can change sage so that the addresses print? or that Sage can change where the addresses have been processed?
Any help would be hugely appreciated!
The layouts sound as though they were setup with the wrong address fields. If the address fields start with C_ then these will ignore blank fields.
Hi I am using sage 200 construction. Can i duplicate a Purchase order?
Sorry Cecilia I do not have any knowledge of Construction. It’s no longer a Sage supported module and is essentially add on supported by Eque2.
ok thanks anyway 🙂
Hi
I am new to sage 200 and didn’t check the version I’m working on…
Not had much training but was tasked with setting up a few repeat sales order templates – what I’ve managed to do is transpose different order numbers into the same repeat order template and then they cannot be moved!!! Can you tell me where I went wrong as now I have to try and set up a new batch of repeat order templates and I need to save each one to use again!!!
hi,
just upgraded from line 50 to sage 200, when processing works orders, is there any way to ‘auto refresh’, when allocating/issuing/completing, rather clicking on refresh and returning to the list view.
thanks
rob
Yes, there is a setting Manufacturing settings. It’s only applicable to Manufacturing though and not the other Sage 200 modules.
Hi,
Recently upgraded to Sage 200 Extra and this is a big issue for me and I cannot find the correct setting.
Having to Allocate -Refresh List – Issue -Refresh List – Complete – Refresh List is very time consuming and confusing!
Hi
Not sure this on the SOP list? You only need to hit refresh at the point you specifically want to read the information on the list. The database itself is updating real time as each transaction is saved.
Hi – this is on the Works Order list. I know the database updates but having to refresh the list to see the current staus of each WO is a pain. This did not happen in the previous version!
Where is the manufacturing setting and I will try that?
Hi Mark I am afraid this setting was removed for 2015!
Ah man – ok thanks anyway – how about this one?
I cannot enable the Allow Multiple Issue setting in Works Order (I have ticked Allow Multiple Allocate and Allow Multiple Complete) and they work fine but Issue is greyed out – any ideas?
Hi,
We’re using Picking Lists in SOP. Is it both possible and straightforward to do the following (and if so, how do I?) change the details used on a picking list. At the moment it uses product code: I’d like this to be the product name. If we print the picking lists manually as a batch they’re all grouped together, is it possible to split them to 1 page per customer?
Many Thanks
The product name field will need to be added in place of the code. You will need to group by customer and then specify to page break after the customer account footer. It’s very simple if you know the report designer
Hi, Is there any way to use Report Designer to insert the gross profit percentage beneath the gross profit value in a P&L report?
Thanks
Luke
There is a percentage option on the Financial Statement layout (last column). Change this against GP line. Then use the percentage reports available.
I have just started with a new company and they produce their Sales Invoices via the Project Accounting option. I need to change the company name and logo on all documentation including these sales invoices. I have tried using the report designer legacy reports, but this just changes the sales invoice layout using SOP and not the ones in Project Accounting.
Can you advise me on how to do this please?
Thanks.
Angie
Hi, the Project Accounting layouts start INV (as default). But you can check the layout being used in File > Choose Layouts and select INVInvoice.
Hi Ctayts
In report designer I’ve added the below to the despatch note -> item description -> conditional formatting. My problem is that it only works with ‘First Class’ and ignores the ‘Expedited’ text. I guess that’s because I need to do something different, can you help?
if Contains(SOPOrderReturnLines.ItemDescription,”Expedited”) THEN Formatting := FormattingStyle(“Highlighted”);
if Contains(SOPOrderReturnLines.ItemDescription,”First Class”) THEN Formatting := FormattingStyle(“Highlighted”);
Thanks
Marcus
Hello,
I am just starting out with Sage 200 having never touched it before starting my new job. I working in a warehouse environment in a manufacturing company. Some of the part manufactured parts get sent off to a sub contractor for work which gets despatched and comes with an attached advice note but I am unable to figure out how to book the stock back in when it returns. the advice note that is printed when we despatch is linked to the works order and also the purchase order for the sub contractor but sometimes we have for example 100 items on the purchase order but we despatch 20 at a time.
When I look at the attached purchase order number to receive the items back in purchase order processing there are no items in the list and everything seems to be greyed out.
Thanks in advance for your help
Aaron
Is there a way of adding the due date to the invoice layout ?
I understand that the due date is calculated as part of the posting process and so would not be available as a field in the tables at the point of printing, but is their another way of showing it?
Hi mate
It must be an older problem but still no solution.
I have red crosses appear on the user`s screen and is temprorary fixed with reset desktop.
The thing is after a while they re-appear. Sage sent me a reply about memory but this doesn`t seem to be the thing since he has 8Gb and his use in 1-3%
Any ideas?
Thanks
Ioannis
We are on 2011
Obviously the SP has been released so the fields work differently and you are able to show fields for what would be paid if discount taken.
Afternoon
I am trying to configure our reports to be ready for the Early Settlement Discount changes next week. I am planning to remove the figures from the settlement discount % and days in Sage and put them into a customer account analysis code instead until we can upgrade to 2015.
What I would then like to do is to change field on our report that shows the amount to be deducted from the invoice if paid inside the terms to Invoice Gross * (100 / Analysis Code %). I have tried
SOPInvoiceCredits.InvoicedGrossValue+(100/CustomerAccounts.AnalysisCode19)
But I get an error come back.
The figure I have in the AnalysisCode19 field is 05 to test how this works.
Can you offer any help on this please?
Thanks
Hi Sam, the issue with the Analysis code is that it is not a numeric field. So the calculation will not work on it.
What version are you on?
https://talkingsage200.wordpress.com/2015/02/24/early-settlement-discount-legislation-changes-for-sage-200/
Looks like I have found an option this morning online….
(StringToFloat(SLCustomerAccounts.AnalysisCode19)/100)*SOPInvoiceCredits.InvoicedGrossValue
I have done this as one expression and then a second expression
RoundDP(Expr1,2)
I have run through a handful of invoices and it seems to work 🙂 On one invoice there was a rounding issue of 1p, but that I can live with 🙂
That’s cool, thanks!
I have accidently “post invoice” on sage 200 without entering the relevant information needed matching up my purchase order and invoice. Is there any way I can amend or retract this to start again?
Not really, but what info did you enter incorrectly?
Hi
can you tell me how to extract information from my SopOrderReturnline into excel please thank you.
Michael
From a specific order or all orders?
There are reports at order line level which you could export to excel.
If you are on a version with workspaces you could right click and send to excel.
Otherwise you would be best to use ODBC connection and extract.
found the problem was someone decided to alter the company name and included a / in the name. All sorted. but much appreciated the swift response.
cant see anything on the MRP log, but on the screen shows an error beside calculating lowest usage levels
when running MRP in Sage 200, i am getting 1 error for calculating lowest usage level. My MRP log isnt showing what this is. Any ideas would be much appreciated.
thanks
What is the error?
Hi, in SOP trying to allocate stock to an order, if there is insufficient stock we have to use amend allocations, is there any way of forcing the allocation of negative stock automatically? Thanks
If your item (product group) allows negative stock you can allocate on the order. However the auto setting will only enter what it can (based on amount available). But the user can change the allocate qty before saving the line.
Hey! Hoping you can help me out with a few BI headaches (Sage 200, 2010 issue I think). Our finance person left and now I’ve been left trying to sort it out.
My first headache is on a profit and loss sheet, we have an account (I thiink that’s the right name) showing up as a child of another one. E.g. under the row of IT, Marketing is showing as a drilldown row so it’s values are showing in the IT total when marketing costs are completely separate. INo idea how to move it to be it’s “own” row. Is that in Sage itself or BI? It almost looks as if someone has dragged the row and dropped it in the wrong place, but I couldn’t see anyway of moving it, or anywhere where “Marketing” is mentioned on its own. Can find marketing salaries etc, but not the top-level marketing account. I’m probably looking in the wrong place though!
My other headache is balance sheet. If I run the Sage Balance Sheet report it gives me the current organisational surplus/deficit. If I run the Sage BI Balance sheet one, that value is blank. If change the period it occasionally appears, but is also blank several times. Any idea why?
Any help would be great as I’m working in the dark here!
BI still uses the Financial structures, therefore you need to make sure the relevant Nominal codes are using the correct Report Category. It may be you need to create a Marketing report Category and add it to the Financial Structure.
Check the report category against the account. create Report Categories and Financial Statements in Nominal Ledger > Utilities > Ledger Setup.
Hi, I am trying to amend the Trial Balance report (prior year) in report designer. The system report currently displays lines even if there is no values in the Debit /Credit /Balance. I want to hide the ‘blank’ lines. So if there is no values for any of the six columns for Debit / Credit / Balance (Both this period and YTD) then hide that full line in order to make the report less pages. I want it to be like Trial Balance (Detailed) for the current year as this does get rid of the lines.
I have attempted to hide the rows by using conditional formatting which hides the field data e.g. account name but its still a big document as it doesn’t get rid of the line.
I have attempted expressions but my knowledge of this is poor and I haven’t got very far.
Any ideas how I can fix this thanks.
Kristina
Hi Kristina
You need to add a Report Filter.
Within the Report Go to Report > Filters
The Filter should be amended to read as:
NLAccountTypes.Code “m” AND (NLAccountTypes.Code “P” OR NLNominalAccountPriorYears.DebitPriorMonth 0 OR NLNominalAccountPriorYears.CreditPriorMonth 0 OR NLNominalAccountPriorYears.DebitPriorMonthYTD 0 OR NLNominalAccountPriorYears.DebitPriorMonthYTD 0) AND SYSFinancialYears.YearRelativeToCurrentYear = -1
thanks
ctayts
Thanks for the quick response – I have put this in but it doesn’t seem to like the M?? I have put before it to remove the red line underneath but it will not let me save…….any ideas why.
thanks
kristina
Have you got the Quotation marks (“M”) around it (it should be Green to denote its TEXT)?
If you hover over the red line it will message what is wrong.
Thanks – yea I had this in “” but I typed it in manually and it worked. However when I run the report it doesn’t seem to get rid of all the blanks
Buildings costs 0010 01 has a value for YTD debit and Balance.
Buildings costs 0010 02 has no values for any columns
Buildings costs 0010 03 has no values for any columns
and so on……..
I should have mentioned costs centres – does this affect it??
thanks
Kristina
can you copy and paste your filter so I can see it?
Sure its NLAccountTypes.Code “m” AND (NLAccountTypes.code “P” OR NLNominalAccountPriorYears.DebitPriorMonth = 0 or NLNominalAccountPriorYears.CreditPriorMonth = 0 or NLNominalAccountPriorYears.DebitPriorMonthYTD = 0 or NLNominalAccountPriorYears.DebitPriorMonthYTD = 0) and SYSFinancialYears.YearRelativeToCurrentYear = -1
Sorry I didn’t notice that the DOES NOT EQUAL () signs were removed when I pasted my filter. Should read with the sign DOES NOT EQUAL 0
Fantastic – thank you so much. 🙂
Hi
Firstly, awesome blog 🙂 Sage are not the easiest people to get “sensible” information out of, your blog fills a gap 🙂
We have been having some problems for the past 4-6 months with users getting a database timeout error when inputting and trying to Sage orders. It appears to happen in particular to one SL account that we have set up more than most.
I know its a bit of a long shot, but is there anything you can suggest that I look into?
Kind regards
Sam
Should have said that we are running v 8.00.0017
Hi Sam
Its bizarre that it is one particular account. Is there anything that is different about this account?
Any bespoke involved?
Cheers
Nope. Nothing. It is an account that has 5-10 orders a day with up to 100 lines per order on, but that is the only way it differs to most of our other accounts
Thanks
Have you looked at Archiving orders? Unfortunately you can’t do per account but you would still have access via the Archived reports and Enquiries.
It may be worth creating a copy of you data set and running an Archive one night.
Regards
Craig
Hi
I am having an issue with Workspaces (sage 200 2010) with newer PCs. (windows 7)
Older Pcs are still running workspaces OK
Sage 200 has been installed on the client and is working OK via the standard screens,
however either attempting to use existing Workspaces or setting up new ones Sage crashes with the following message
An unexpected exception occurred
Number (none)
Message
Exception has been thrown by the target of an invocation
Inner Message
Item has already been added. Key in dictionary:
Stack:
followed by a long list of message lines
All Pcs are Windows 7 with Office 2010
Testing has been done with both IE 10 and IE11 (no differences)
If anyone has any idea on what is causing the Issues with the newer PCs I would be very grateful
Best Regards
Ken Cooper
Hi, We have recently had Sage 200 v2011 migrated to a new server by our business partner. We are forced to use v2011 due to us having an MIS system which links to Sage 200 and v2011 is the most recent version that supports this link. We have a database server running Windows Server 2012 and SQL 2012 and a web server running Windows Server 2012 with the Sage folder. Since the migration Sage 200 is extremely slow at opening up, it takes 2-3 minutes after entering your log on details until it actually opens. Otherwise it is working normally. Our business partner say it must be our network. We cannot find any issues and I have had people look at our networking who can’t see a reason. Before the migration which was also Sage 200 v2011 but an older version without recent SP’s running on Windows 2008 R2, it was slow but we are talking around 30 seconds to fully open not the 2-3 minutes we have now.
Have you come across something like this before and can give any ideas on where I can start to look to try and solve this?
Thank you
Nick
Hi Nick is everyone experiencing this?
Any Anti Virus or UAC controls enabled?
Is it slow to open on the server?
Are named pipes enabled with in SQL Configuration Manager
Although 2-3 min’s to open, does it run fine once logged in?
Thanks
ctayts
Hi sorry for the late reply.
Yes all users are experiencing this.
We are using Avast Anti Virus but I have disabled it for testing and it didn’t make any difference.
On the server it opens instantly.
Named pipes are enabled.
The users have said it gets slow once several users are in the system. But they have not complained really so I assume it runs more or less the same once logged in.
Thanks
Nick
Hi, I was wondering if there is an event in sage 200 when an account is placed on stop, so then I can automate emails to this customer via .net programming
There’s no specific event to catch on-hold status change
You would probably need a form mod to read the account before a user amends its status then check and catch it on the save event
or
create your own form to update the account and fire your own even then
Hope this helps
Hi – picked up some customisation script from Sage 5.1 and we need to implement the same functionality in to Sage 200 2013. I’ve put the vbs file into the common customisation folder but cant seem to find the button that it should generate – whereabouts in the application should it be appearing. The key bit of code is below:
Dim oProject as Sage.Accounting.ProjectCosting.PCProject
oProject = CType(Form.BoundObjects(0), Sage.Accounting.ProjectCosting.PCProject)
Hi Andrew
This is a completely different version (4 or 5 versions). The bespoke would need to be recompiled for the 2013 version.
ctayts
In addition to ctayts post (sorry Craig not hi-jacking 🙂 )
For 2013 Sage insist that Scripts now be deployed via add-on only (no longer copied from the common folder)
Hi
Having a problem with Sage 200 7.00.0042 running on new PC’s/Laptops.
Workspaces are crashing giving .net errors and application exception errors.
I have checked against all the usual suspects IIS and .net setups as per deployment documentation
and nothing seem to works.
Any Ideas on where I should look
If you need any additional info please shout
Best Regards
ken
What are the errors?
Are you getting them on the server, clients or both?
Hi there could you possible help me with sage report designer? I am trying to modify a statement to show delivery address line1 instead of goods/services I’m pretty sure this is possible many thanks
You are going to have to link back to the SOP tables.
You will need to use the join editor to add SOPInvoiceCredit linked to SLCustomerPostedTrans (join DocumentNo to TransactionReference). Then add SOPInvCredDelAddresses, linked to SOPInvoceCredit (join InvoiceCredit ID to InvoiceCreditID).
The only potential issue is if there is a customer who happened to have the same Invoice and Credit Note number (i.e. Invoice Number 1 and Credit number 1). As this will possibly cause an issue using the first join.
ctayts
Hi, I’m trying to close a completed works order but this message keeps on coming up: ‘There are component issue entries with unconfirmed costs on the works order’. How do I fix this? Thanks in advance.
Hi Brett
This suggests that there are uncompleted Purchase orders and the system is still awaiting a committed cost for an item used on a Works order.
ctayts
I have created an Excel workbook that looks at the stock count and tells us what to order. We are doing a physical stock count twice weekly on the items in question. I would like to be able to compare that to the number we have on Sage using “Sage 200 Excel” integration. However, we have two warehouses and I can’t find a formula that allows me to return the value for a specific warehouse. Any ideas?
Hi, I am having an issue when updating standard prices of already present stock items… I have created a csv file as I normally do each year, but since updated to 2013R2.. I get an error when validating: if it is an error? “an item with the same key has already been added”.. this is just running the normal import prices routine in price book. Any ideas why its not importing. I am just using the standard prices with stock item in the headers of the CSV; as I normally do. ??? cheers, Simon.
Is this a price list with items that are ticked as Use Standard Price?
Hi Pal.. I have since discovered this is a known issue. I have had to strip out of the file all of the part numbers that are do not exist on our system… the import works perfectly. However in the past I have run an import such as I described and sage popped up an error report *after* importing; showing all parts not able to import – this then prompted me on the “new” numbers I had to create….
typical sage….
answer to your question.. yes we only use standard prices.
Hi, Great blog and some nuggets of pure gold here but i wanted to ask a quick question.
Do you know of any way to add a modified date to the sales order?
Cheers
Mark.
hi
we are using sage200 2013
we are having major issues running mrp, 1. we all have to log out to run,2. no sub assemblies are coming through
any ideas
trying to add the TransactionHistories.Quantity to my report but it is not returning the correct figure how do i get it to show the qty on sales orders for the date in teh criteria?
Hi Ctayts
In POP, is there any way of rectifying the following example, apart from not making the mistake in the first place:
10 items ordered, 10 booked in, 11 invoiced, this leaves the order as live on the system
Thanks
Matt
Hi Craig,
We have Sage 200 with some eureka add ons installed by a 3rd party. It’s only a small network with no virtualization so I’d like to have our backup server ready and waiting if our main server (inc SQL Server and Sage) died. I have differential SQL backups running every 15 mins and replicate them via DFS to a local backup server and remote backup server …. so all the files are sat there ready and waiting in the event of a disaster.
So to my question, is there a good guide to install/move/copy Sage 200 to another server inclusive of add ons and any customisations so nothing is missed ? (I’m not an experienced Sage guy but have lots of experience with SQL, Windows Server, Networking, Development etc)
Many thanks
Hi Maurice, apologises as I have only just seen this post.
Essentially you would need to install exactly the same way as any install. The eureka add-ons are easy enough as they are am SDBX add on file, which would need to be run via System Administration.
You would perhaps want to make sure the Sage share is being backed up so that this can be moved over. This way Reports, Layouts, Spool files, attachments can be moved.
The main thing to be aware of, is that in the event of having to switch servers, the client machines would need to be pointed to the new server and also the SSL certificate exported from the server and imported to the clients.
Hi
We’re trying to help a client with an integration. We’re not a Sage partner, and it’s not our area to deal with, but they’re having trouble getting the full info themselves.
The question: Regarding Sage 200, how can you get timestamps recorded on orders and order line items, so that when we integrate the CRM (not Sage) to Sage 200, we can select just the deltas to come back?
We have the tool to do the integration between the CRM and Sage 200, but we need to know how to enable the functionality of recording timestamps on orders and order line items in Sage 200.
Is it something to do with OpLock (I’ve seen that regarding account change timestamps).
Thanks in advance
Michael
Hi Ctayts
Is there any way to search by customer delivery address or postcode in Sage 200?
Thanks
Matt
Hi, wounder if you can help me with a Sage 200 / SQL problem. We have a number of views in SQL that output data to excel spreadsheets using the Sage database. We use these for day to day production planning as the graphical planner cant cope. The problem comes when certain operations are been completed in Sage ie tracking of a works order to completion and it updates all the stock tables, and if someone refreshes there spreadsheet, Sage times out and the spreadsheet gets stuck in a loop also. I have looked at replication and mirroring the sql Database to use as the reporting database as it seems that the load on the server is minimal, but more of a suspected issue with trying to access records at the same time. Can we use replication or mirroring? I have been told no from our current it providers as it will break Sage?
Darren
Do you know if there is a module or functionality that would allow a BoM to be built whereby you are able to select different colour/ finish specification.
i.e. we have a bench system that could come in different colours and finishes, but the component are always the same just with a different finishes. We currently have to create multiple different BoMs for each finish variation, ideally we are thinking something like a tick or drop down box for the finishes options.
Ellen
Hi Ellen
Is it just BOM or Manufacturing that you have?
Your best with standard Sage 200 is the Estimating module that comes as part of Manufacturing.
Here you could essentially build the required “BOM” as part of an estimate and then create a Sales order and Works Order to build the item.
The estimate could use a BOM record, use a BOM but amend specific to the estimate or be a Unique build without needing to create a BOM.
Ctayts
Dear ctayts,
I know that the question of stock reconcilliation is a bit of a minefield, and have read your post and document on the subject, but still want to know if there is a procedure for stock reconcilliation anywhere in existence which we can follow to correct an error that we believe exists!?
Regards,
David
There is no set procesure other than the checks and functions within the document. In terms of the error it will depend where and how the issue arose. NL can be amended by NL Journal. Stock by Stock adjustment (watching where NL postings are analysed to).
Ctayts
Potentially a simple one: Is it possible to create/generate a list of products which have no supplier added to them, or even to add no suppliers added as a search filter/parameter?
Thanks
Hi Jay, there is a standard report to show Items with Supplier details. This could be amended to lost the stock items, abd a Filter applied that only includes items without a supplier. Filter would be StockItemSuppliers.StockItemSupplierID = NULL
Ctayts
Hi, I wonder if you can help me please? In sage 200, sales order processing tab, new order, we don’t seem to be able to change the A/C ref once it has been input/saved. Sometimes our customer decide to change the invoicing address and we have to create a new SOP for this. Any advice/tips? Many thanks
Hi Tan
The Invoice address comes from the customer account. It can be amended here.
Is your scenario where a Head Office is involved and you are trading with Branches? If this is the case then there is Head Office functionality where each branch is set up and specified as a Branch, these can then be linked to the Account specified as the Head Office.
This allows for you to Invoice each Branch separately but send statements to the Head Office. The downside of this functionality is that there are multiple accounts for Credit control.
If this is an issue then I recommend the Head Office functionality from Eureka, which is a Sage 200 add on which extends the Standard Head Office feature to post allow invoices for a Branch to post against the Head Office account and make credit control easier.
I hope this helps
ctayts
Looking for some help with report designer and the standard picking list report.
Would there be a way of getting the first SOPOrderReturnLines.ItemCode into the document footer?
At the moment, when SOPOrderReturnLines.ItemCode is called in the footer, the last value to be called in the SOPOrderReturnLineID footer 3 section is called.
I have tried the Index function, but this does not seem to help.
Hi Craig,
I have a question on BI, hoping you can shed a bit of light for me (v2011)
I haven’t played much with BI just getting into it,
But is there any reason why the Aged Debtor/Creditor BI reports wouldn’t be working out of the box with Sage.
The DW has been created, cubes refreshed, all other reports are working with exception of Aged Debtor/Creditor
I had a look in the DW database and the DWAgeing tables are empty so its like during the refresh these are not populating
Could I be missing something?
Thanks,
Paul
Think I have found the answer on this one
Turns out the ageing cubes are “as at” cubes so the data will only start to appear only from when you first set up BI and refresh the cubes,
So they are unlike the other cubes that already utilise the full historical data
Yes sorry i hadnt got back to you. Thats exactly what the reason it. “As at” cubes need to build up over a period in order to beable to make use of their function ie to compare with this time last year.
This may be a very basic question, but… We currently run Sage 200 2009 version on a single Windows SBS2003 server and 10 workstations running Windows XPSP3. We are planning to upgrade all the hardware next year, and have been told that Sage 200 2013R2 needs two seperate servers, and will not run on a single server. Is this correct?
Many thanks,
Hi John
To be honest I am surprised there were not difficulties when 2009 was installed. Older versions were not supported (off the top of my head until about v5.0) on a single server. It was then classed as supported but there were lots of config files to amend manually (during installation) and often difficulties occurred.
From about 2011 to present Batch files were introduced by sage so the manual changes to config files were replaced and less problems occurred.
However, I would always recommend Sage 200 has its own dedicated server. This can include SQL.
But what it means is the whole of your company is not reliant on one server, the resources are spread out and where SBS is involved Microsoft recommend that a separate server is used for SQL in any case. This is because SQL (express) is installed as part of SBS and conflicts can occur.
Ctayts
Hi Ctayts
We run basically a builders merchant, most deliveries are ex stock, but some are direct from supplier to customer, we want to keep the direct stock transactions away from the ex yard ones, would setting up a new warehouse be the right thing to do to achieve this, and can we duplicate our current stock codes into that warehouse
Thanks
Matt
Hi
Following on from the above, I have set up a new warehouse, I also exported all our product codes into an excel csv file, I then entered D/ before each product code [all 4000 of them] when I try to import the amended codes into the new warehouse, I get an error, no codes imported, product group does not exist?
Not sure this is the best way for what you are trying. If the item is the same then a different Warehouse can be defined with Multiple Locations enabled. The item would remain same. However the Direct to Customer fulfilment method is perhaps better. In terms of the import though, the Product Group field will need to be the code of a Product Group that has been created.
Ctayts
Sorry Matt, are you planning to use the Direct to Customer fulfilment method? As this uses a system defined ‘Direct Delivery’ Warehouse.
Hi Ctayts
I am trying to keep the direct to customer stock separate from the ex yard stock, for valuation and physical stock availability reasons, we have a back to back direct to customer development installed
Ok, but I would have thought of it was direct to customer you would never hold the stock.
Anyhow, a separate Warehouse location should be fine. Add to the items and you will will be able to see for Product A, x amount in Whouse1 and y amount in WhouseDD.
Cheers
Hi. Please can you guide me as how to create a development sage 200 crm database on the same server as the live one? Currently we have a live sage 200 crm db but I would like to have a development one so new staff members can play with the system prior to going on the live one.
Many thanks
Julie
Hi Julie,
From what I can remember on CRM this is tricky.
From a sage support point of view this would not be possible
As Sage do not support multiple versions of CRM on the same server
(Unless you are running two different versions – i.e. 7.1 and 7.2 can be installed on same server)
But even at that you would need a separate license per separate install
So best bet would be to try set up a test/development CRM on another server or workstation if possible
On a side note:
If CRM is integrating into Sage 200 it will most likely have to be a separate server and a separate sage 200 test DB too – so the integration services to do not conflict and you are not updating the live sage 200 DB from a test CRM DB
Hope this helps,
Paul
Hi Ctayts
Using Sage 200, I have entered a quotation, but used the wrong customer account, is there any way of changing the account or copying the quote and changing it, or do I have to start again?
Thanks
Hi Matt, you can’t amend the account, but you can copy a quote. Select Enter New Quotation, at the bottom of the screen is a ‘Copy Quotation’ button.
Ctayts
Many thanks for that, saved me a lot of time, much appreciated
Hi, your “talkingsage200” page is a long time coming!! finally! Thank you! Anyway, I have a niggle that is winding me up no end… 2 business partners later and I still can’t get to the bottom of it! When printing a picking list from a SOP that has a message line.. the message line always prints on a separate page. Business partners are telling me its hard coded into the layout and cant be changed… if you would be good enough to confirm this for me. I believe its because picking lists are based on Warehouse location… however a “message line” doesn’t have a location. A message added to a sales item is printed on the same sheet.
Hi Simon
Your right, it’s due to the layout being grouped by Location. The layout can be amended though it might cause issues with other parts of the picking list (I.e. You want a separate page per location). Is the case that you want to report by location, but show the comment on each part of the report?
Thank you for your reply; We only have one location anyway, so that wouldn’t be an issue. Is there something in the report I can take out to keep the “locations” on one page?
If you remove Warhouse Group Section then this will Group the report by Customer.
In the Report you have a Page Header followed by a Name Header. The Name header is the Warehouse section (Warehouse NAME).
If you remove this via the x then this will work as above.
Note that this is assuming you are running the standard report. Also note that the Description, Date and Time fields are within this section so you may want to move these.
Ctayts
Hi Pal… just to let you know; it worked… might be worth handing over your BP details so when I come to renewal; we go with someone who has inferior knowledge! haha! thanks once again… one big head ache sorted.
Nice one, glad to have been of help. Smith Cooper System Partners. Would be happy to to take your site on!
one thing this seems to have thrown out of sync is when printed, it prints the products in order they were entered into the SOP.. when a user enters a sop, they sometimes change the order of the items by clicking the “move up” and “move down”… this now isn’t reflected on the picking list print and I cant work out what controls this.
I wouldnt have expected removing the Warhouse Section to impact on this. The control will be the SOPOrderReturnLine.PrintNumberSequence. Adding a Sort by this may solve depending on Groupings and other Sorts (will need to look at it confirm).
spot on… its the finer things that count. thank you again.
Hi, I have a Sage 200 query. I want to run off a simple sales anaylsis report, but I cant see one anywhere – can you assist please? Im using Sage 200 2011
What detail do you require? There are a number of reports at order and invoice level within SOP. Trading reports in Sales Ledger and Business Intelligence provides reports too!
Hi
Do you or anyone else have experience of Construction for Sage 200 Accounts & how useful you find it? I have been looking at this together with the Project Accounting module.
Thanks
I am not a construct consultant myself. I do know Project Accounting though.
If there are Subcontract/CIS requirements then Construct is your option really. Project Accounting is transactional Analysis per job/project, allowing you to view Profit/Loss of individual jobs as well as budget variance.
You are able to record Timesheet and Expense claims, Bill directly from Project Accounting and choose module integration.
Hi ctayts
I’m using SOP in Sage 200 to generate invoices and email them out for me, initially I thought I had success using the following instructions; https://support.sage.co.uk/help/sage200/Content/General/Email%20SOP%20invoices.htm
However, the PDF’s that are attached to the emails don’t have the company logo (header) and footer, which show fine on all other versions of the same invoices. I wondered if there is a setting I can adjust so these have all the correct details, or whether this is just a failing of the email options built in to Sage? (I know we were originally told it wasn’t possible to raise as batch and email out without purchasing additional software).
Any help would be most appreciated.
Thanks
Niki
Hi Niki
You need to make sure that the correct group is selected to split by (Page 4 of the document). This is normally the last Group listed.
Ctayts
Hi,
Is it possible to have multiple instances of the Sage 200 client installed on a single machine, each connecting to a different server?
Hi Craig
No. Whether it would install at all I am not sure, but it certainly wouldn’t be supported.
You could have databases on different SQL instances and point Companies to there relevant instance.
Obviously you can restrict user access to companies also.
Ctayts
Hi,
I was wondering how viable it is to update the sage 200 database from a third party application and then have the data accessible on sage for other people to view.
I am in the process of designing a program and i want to be able to add a goods return number and associated return data to sage directly from my program without having to add the information via sage – in essence i don’t want to have to enter the data twice.
Does this sound viable or am i going to encounter run time and permission errors and ultimately slow down the database. I would imagine that my program will need to check the database first to check that the specific returns number is free before it saves the new record. Is this idea feasible or am i going to need extra development modules? Has anyone done anything like this before? If so any idea what i will require?
Thanks for your time,
Andy
You will certainly need extra development or to obtain Sage Developer accreditation in order to do this in a supported environment.
Ctayts
Hi
We are using Sage 200 BI to create our management accounts in excel. The BI link works well and brings through the information we need, however there is a real issue with speed. We have ten separate BI reports in the same excel workbook (different group companies). Each time the spreadsheet is opened, we are asked if we want to update reports etc. If we click no, the whole workbook grinds to a halt and it can take 30 seconds to just move between tabs. If we click yes, then it takes up to fifteen minutes for the reports to be updated – the spreadsheet then works fine, however sometimes it decides to ask if we want to update reports again and then we have the same issues as above.
Any thoughts as to whether we can speed things up gratefully received (my preference is not to have the reports in separate workbooks and then link the workbooks separately if possible).
Many thanks
Pauline
Hi Craig
Guessing as my previous message has come up straight away my message from last night didnt come through.
Apologies if this seems a silly question I have just started a new job where I have been asked to produce reports from sage 200. Yesterday i have been playing around with Sage 200 and reading the bits of literature i found online, but not having much luck. I am a complete newbie to sage 200 and the department where i work also is.
Can you help any books, online links where i can self learn.
Would be ideal to find a tutorial which can explain from start to finish how to produce reports. or is there a book in the dummies collection on sage 200 have not personally come across one myself yet.
Thanks in advance
Faz
Hi Faz
I replied as below:
There is little in the way of documentation and guides out there as Sage 200 is supported and sold through the Business partner channel.
I have created this blog to help. But in terms of courses/training you will need to contact your business partner.
If you haven’t got a Business Partner let me know.
Ctayts
Hi not sure if you recieved my message yesterday ? or should i send again?
Hi
I’m very perplexed by sage 200 Bi tool, when i select a bi report im told that the excel addin feature cannot be found. I also have tried lookng in excel to add it, but no luck. I’ve tried repairing the sage installation, and no luck either. Is it a compatabilty problem? Im using sage version 8.00.0016 which i believe is 2011 and excel 2013?
Any help on this matter would be really apreciated!
Leon
Hi Leon
Excel 2013 isn’t supported for Sage 200 2011 and I haven’t seen it work.
You could try in the first instance Start > Programs > Sage Tools > Sage 200 > Enable BI
Also check that Addon’s are not disabled within Excel.
Ctayts
Hi,
Our Accountant has an annoying problem. When ‘delivering’ multiple purchase orders he has to ‘ok’ every single preview (which he has set up already). I have scoured the web looking for an option, script or file to change in order to fix this but can’t find anything?! Please help!
Sorry, not sure I understand the what the problem is.
Is it some sort of report that is being generated and this is previewing?
Hi,
We have have provided our clients with EDM systems that allow them to file purchase invoices away indexed with data extracted from the invoices, inv numbers, po numbers totals etc.
With Sage 50 they can import this data using the “import audit trail transactions” function built in.
We have a client who uses Sage 200 , is there an easy way to import these purchase invoice details as there does not seem to be an import function?
Thanks in Advance
Hi Alan
There is a standard import routine for Purchase transactions within Purchase ledger > Utilities > Imports > Import transactions
ctayts
Hi ctayts,
We have Sage 200 v2011 at our workplace, and I am trying to set up some Workspaces. I’m very interested to read earlier up the page that with Workspaces you can access data from many sources.
I would love to do that…however I am stuck on the first rung of the ladder:
At the moment, the only Data Store which shows on the Sage 200 Query Editor window is “Sage 200 Accounts”, which is configured in our System Administration under Information Management > Models.
However there are also 4 other models listed there (Sage 200 CRM, Sage 200 Transient Data and Sage Manufacturing Transient Data). It is the Sage 200 Transient Data (which I think is the tables in the Views part of the database) which I would like to access via Workspace Designers, however I can’t find out how to add that to the list of Data Stores in Query Editor.
Do you know how I can do this?
Many thanks in advance,
Kind regards,
Andrew
Hi Ctayts,
Sorry to trouble you again, we have another massive bug bear in the order processing team that sales team will never understand!
When we send an order to one of our suppliers under direct to customer option in the sales order, and then confirm the direct delivery goods received. If someone then requests they want said goods from another supplier, the only way around it is a Purchase return and a sales return and as well completing the order. We then have to put a new order on.
Is there a way around this?
Regards,
Charlie
Hi Charlie
I am not sure I understand the process.
If you have a Sales order and generate the PO, by receiving the goods you are specifying that they have been delivered to the customer. So why would they then change supplier?
If the goods have not been Received, you are able to cancel the PO, and the system will ask if you wish to make to SO available for Back to Back ordering again.
If as you advise the goods have been Received then the only option is to complete the orders and Return.
The other thing to be aware of (if using the Save and generate PO) when saving a Sales order (rather than the Generate Purchase Orders) then this will use the Default Preferred Supplier for the Stock item.
Thanks
ctayts
Hi ctayts,
I hope you can help, I’ve scrolled through over 2 years of questions before i asked as i was hoping you’d come across it before.
Every now and then we get a customer who wants us to change their company name on an invoice. currently the only way we can achieve this is to do a return, credit and new order with the correct company name.
As you can imagine it’s a very time consuming process, is there a quicker way to change a company name on an invoice that has been posted and sent?
Regards,
Charlie
Hi Charlie
Its possible to amend the Address when doing a ‘Re-Print Invoice’, however this doesn’t include the Customer name.
The only option would be to amend the Layout so that it uses the SLCustomerAccount.AccountName instead of the SOPInvoiceCreditAddresses.PostalName
cheers
ctayts
Hi Ctayts,
Much appreciated, we will try it out next time we have the issue. your help is much appreciated and i will let me you know how we get on!
Have a nice weekend
Charlie
Hi There
I work for a distribution company that uses SAGE 200. V8.00.0013
Is there a way to set an order to a certain priority by default?
Hi Lee,
Default comes from the Customer account.
ctayts
Hi,
We are encountering an issue with our sage integration where the solution works in our 64 bit Win 8.1 machine.
Our solution is basically a delphi 7 application accessing the SAGE assembly services via a COM interop DLL that was generated by a VB.net project.
In the working Win 8.1 machines, the sage services are accessed successfully and a sage application instance is created successfully within our solution.
But it doesn’t seem to work in a 64 bit win 7 machine where the solution hangs when trying to access the sage services.
After debugging through the background process we have identified the issue down to the specific web service.
In case of the working win 8.1, it seems there are additional DLLs loaded up by the background sage web services.
But this is not being done in the win 7 environment. We used the process explorer utility to identify which DLLs were loaded.
And we have identified this might down to the difference in the IIS configuration where the win 8.1 had version 8.5 and win 7 has 7.5 version.
I had contacted the sagesoft.developers/Technical Support Team their advice didn’t make much difference and they don’t handle anything relating to a COM interop DLL or IIS specific configurations.
Hence I was looking for any related advice wrt usage of COM interop DLL with IIS.
Is there a significant difference when used with IIS 7.5 and 8.5?
Any related info/advice would be extremely helpful.
Please let me know if any additional technical details are needed.
Thanks for the help in advance.
I have installed Sage 2013 R2 onto a server and i am having an issue witth workspaces not loading, I am getting a message ” System Error – we’re sorry, there has been a problem. Please try again in a few minutes or contact your system administrator if the problem persists” then Return to home page. I have tried everything including ask Sage but no joy. can you help.
Hi,
Is it possible to install Sage 200 On Premise via group policy?
Thanks
Ronan
Hi Ronan
No.
The only parts I am aware of is the Self Service SSL Certificate.
Hi
We are using Sage 200 BI to create our management accounts in excel. The BI link works well and brings through the information we need, however there is a real issue with speed. We have ten separate BI reports in the same excel workbook (different group companies). Each time the spreadsheet is opened, we are asked if we want to update reports etc. If we click no, the whole workbook grinds to a halt and it can take 30 seconds to just move between tabs. If we click yes, then it takes up to fifteen minutes for the reports to be updated – the spreadsheet then works fine, however sometimes it decides to ask if we want to update reports again and then we have the same issues as above.
Any thoughts as to whether we can speed things up gratefully received (my preference is not to have the reports in separate workbooks and then link the workbooks separately if possible).
Many thanks
Pauline
Hi
I’m trying to query the Sage 200 database directly. I am simply looking for some sales analysis between 2 dates. I believe that I can do that using the SLPostedCustomerTrans table. I can see most of the fields I want in that table.
However I need to split those sales down into types or nominals. Is that possible? what table would I join it to? using what fields?
Many Thanks Ian
You will need to link to NLNominalPostedTran. Join using URN
Hi guys, got a little niggle with this fantastic product 😦 everything seems fine other then this issue. Sage 200 allows us to load sales orders fine, however when we go to generate orders, it flashes as though its doing something, and then just does nothing. Then if you press the button to generate orders again it throws up this message “Unable to access the POP module feature ‘Generate Orders’ because you are currently accessing the POP module feature ‘Generate Orders’ and then does nothing, so currently cannot access generate orders to create purchase orders. I have done the basics, restarted server, deleted locks in the “User Login Status” section …but still no joy. Any help or suggestions you can provide would be gladly welcomed.
Oh and one other thing…just to add to the fun of it all…. because it thinks generate orders is already open , it will not let me close sage as a module is still open and needs to be closed, hence I have to kill the service from task manager which I hate doing as that will eventually cause its own issues I am sure.
Hi Craig,
Hope this finds you well. I previously developed some customer reports for a friend in Sage 50 and would like to replicate these in Sage 200. I will be creating an SQL view on the server for the data i need. I have taken a look through some of the back end tables, but am struggling to find where the actual value and details of each invoice is stored. In Sage 50, this was quite simple, as the records were stored in the INVOICE table and the INVOICE_ITEMS table, but i don’t see an equivalent for these tables in Sage 200. Is this something you can help me with?
Thanks in advance,
Trevor
Hi Trevor
You will need SOPInvoiceCredit, SOPInvoiceCreditLine, SOPOrderReturn and SOPOrderReturnLine (at least).
Ctayts
Hi, we have Sage 200 installed and wish to transfer stock from one product code to another easily.
We have certain products that are identical but have different cost prices and we sometimes need to switch stock if there a delay with supplies on one or the other for some reason. We also buy some items in bulk and split down into smaller units (like a wine merchant might)
At present we are writing stock off and then manually adding the stock to the revised stock code. As we have several depots this method does not allow us much control. Any ideas welcome.
Many thanks Rob
Hi Rob
This isn’t something that can be done as standard (other than how you are doing it).
I would suggest you look at having a bespoke screen developed where you can essentially Add and Writ off within the one screen, in order to save some time. Not sure what this would be likely to cost and if therefore it would be cost effective for you.
ctayts
Hi Rob, if you’re interested in looking further into this functionality we’d be happy to quote for it, I can be contacted at chris@tilted.co.uk.
I am designing a report which shows total sales by product group with the ability to drill down in to the product group to show individual stock item movements.
I can set the drill down to pick up the correct product group when it is clicked on but the problem I have is getting the drilled down report to use the same date criteria that are set in the original report.
I have used your drill down guide but just cant get the dates to work
Any ideas ?
Thanks
Hi There, I am not sure if you are in operation any more but I hope you are! I have just installed a new PC and gone from windows XP to 7. I had a lot of filters I created in Sage on my XP machine which have not come across. I have been told that my profile must have corrupted. Is there a location on the drive of my old PC where these filters would be so I can copy then across, or can I export them from the old machine and import them into the new one?
Hi Simon
I would suggest setting them up again.
ctayts
Thank you for the reply!
Hello Ctayts,
I’m new to Sage 200 and I have noticed that the “Sage200NotificationScheduler” service keeps stopping.
It’s set to automatic, and when I restart the service it stays started for around 5 minutes before stopping again.
The error in event viewer is:
2014-07-15 08:55:15,185 [7] ERROR SageNotificationScheduler.SageNotificationService [(null)] – Method DoWork errored.
System.Exception: Notification service url has not been setup in SAA.
at SageNotificationScheduler.SageNotificationService.DoWork()
It doesn’t seem to affect operations.
My question:
How can I set up the url in SAA?
Thanks,
P
Hi Ctayts,
A client’s Sage 200 shows differences between the stock valuation report and the stock nominal which cannot be explained by order and return accruals.
Do you know of any transactions which can affect the stock valuation without affecting the stock nominal? (one such instance is where the standard cost of an item is altered, but I know that this is not the issue as there are now no standard cost products)
Thanks
Chris
Hi Chris
Where do I start? I am having a debate about this subject myself at the moment. There are so many different circumstances and settings to think about. Its very difficult to reconcile in all honesty.
Costing Methods
Default Nominal Codes (and applicable settings in SOP and POP).
When is Stock updated by POP?
Stock settings for COS and Integrate nominal.
Are there any Misc./Service items which are posting to Stock in the Nominal?
When the POP Invoices is recorded, is anything being amended in the second screen (i.e. The Purchase Ledger Invoice screen)?
Is BOM or manufacturing involved?
These are just some of the many things to think about.
Stock Control adjustments (Add, Write off, Stocktake) can update Stock and not the Nominal Ledger. This all comes down to whether Stock is set to Integrate with the Nominal Ledger or not (Stock Settings). Also which Nominal codes are Defaulted/Used/selected when an adjustment is made.
Cheers
Ctayts
Hi
Can you please advise me how to run the following reports in Sage 200:
• A summary of sales orders for a given period?
• A summary of purchase orders for a given period?
• Goods dispatched report for all sales orders which have been completed in a given period?
• Goods received report for all purchase orders which have been completed in a given period?
Many Thanks
Kind Regards
Mark
Hi Mark
The first 2 are simple enough. SOP/POP > Reports > Status > Sales/Purchase Orders (Summary).
The Despatch and Receipt reports are slightly more awkward as the Completion of an order is based on the Posting, and this isn’t dated. If you can assume that the Invoice date is the same date it should be possible to amend/create a report (but you may need to bear in mind multiple Invoices for an order!).
There are Despatch and Receipt reports in SOP and POP.
Cheers
Ctayts
Hi ctayts,
I am currently trying to install Sage 200 onto a clients PC. However after installing and trying to run Sage 200 I get the following message:
Failed to process updates
Could not find file
‘C:\Windows\assembly\GAC_MSIL\Sage.Application.Factory\12.0.42.0__affbf938bc5e4d7b\Applications.xml’
Any suggestions?
I haven’t seen this before, do you have Full permissions to install?
Which version?
When does the message appear?
Ctayts,
Do you have any advice on the coding structure for the PL. I would ideally like to differentiate between suppliers, employees and clients (don’t ask!!). Ive used a code in the past of the first 3 letters of the name plus 2 numbers, however I don’t really like it as companies change names etc.
Also, with employees a lot of the expenses are in a foreign currency, and often the employee will receive an advance in that currency upfront before leaving. Would you advise numerous Currency accounts per employee (ie employee 1 EUR, Employee 1 DKK etc)? or just one account per employee and convert everything to GBP?
Thanks
Matt
Hi Matt
It depends what purpose you want to differentiate between the types. If this is just for Reporting purposes I would suggest creating an Analysis Code for ‘Type’. Assign the relevant value to each account and you will be able to filter them.
These cannot be used on screens when you perhaps want to search for a customer.
Generally I go down the AlphaNumeric route but your right, that if companies change name this can become confusing (unless you setup again NB. the copy account details option).
In terms of Currency, it comes down to how you want to display the debt and manage the account really. You are able to make payments in multi currency but if invoices are not in Sterling then you would need to convert these before entering (and then I would say it comes down to the amount of times you will need to do this).
Ctayts
Thanks Craig, that’s given me some food for thought.
Hello Ctayts!
I have a questions about multiple despatching. Do you know if there is an add-on which can allow mutliple desptaching of selected SOs?
Thanks
Gary
Hi Gary
Not sure I understand. It is possible to select multiple orders and Despatch at the same time, as standard.
Ctayts
Hi Gary
Just noticed this thread – are you looking at perhaps despatching a “load” – have you a van gone out with 30 sales orders on it and you want to despatch all 30 orders with a couple of clicks?
If so then the Transport Manager addon maybe something worth looking at, feel free to get in touch with me at chris@tilted.co.uk if you’re interested.
Chris.
Hey there again,
Is there any way to print an entire list of manual journals for a financial year. I have run a “Previous Year Journals” report and this does not balance to zero which is confusing me.
Regards,
Will
Hi Will
There is a Manual Journal Listing report which should be on your system
Hello! Is there a way to see a list of which nominal ledger are allocated to which category.
For example, I have discovered that there are some professional fees historically included in a category for travel & entertainment.
I am hoping that I can see a comprehensive list of which codes are allocated to which categories.
Please Help!
Nominal Ledger > Utilities > Ledger Set Up > Amend Report and Consolidation Settings
thanks
Logon failed due to unexpected error
Please check if the Sage200Configuration database is configured and running.
(Message was: The underlying connection was closed: An unexpected error occurred on a send.)
I have absolutely no idea, services seem to be running and have been restarted but to no avail.
New install? Sounds like network connection or permissions to the Logon directory on the server in the shared directory. Or the paths on the client in the SVN4WIN.INI file (c:\Program Files (x86)\Sage\Sage200) are not pointing correctly to Logon (needs full UNC path)
Ctayts
Stock Movement report
When I run a Slow-moving stock report it only takes into account ‘direct’ stock movements. We have some parts that are only used in BOM’s so they always show as being slow-moving.
Any ideas as to how we can get a more accurate report?
Malcolm
Are they group in specifically in terms of their Product Group? The Product Groups could then be excluded. Alternatively you could set up Search Categories to specify another grouping to allow these items to be filtered out.
Ctayts
Hi Craig
I am trying to write a new report and i’m having difficulty with the syntax for an expression.
I’m trying to use OnLastRecord when I insert it to my report it gives me OnLastRecord()
I would normally put the field I want to evaluate within the brackets but it doesn’t like it and won’t let me go any further.
Can you please tell me what the correct expression would be?
Thanks
Bill
Hi Bill
You shouldn’t need a field, this function is simply returning TRUE or FALSE if the record is the last for the whole report. It isn’t field specific
Ctayts
Thanks for that Ctayts.
I was wanting it to evaluate the record so that I could find the last entry for each part listed but it’s obviously not designed for that so will have to try and find a different way to do it.
Hi Bill
I have never used it to be honest, but I had a play suspecting a requirement like this and it only seems to work based on the Details section.
Ctayts
Hi Ctayts
Sorry if I am being very dim-witted. Have set up Sage Report designer to print invoices to my outlook (which is accessed via server and broadly the default provider is SMTP) fin Sage report designer>Options>Email Setup>Configure. The test email works fine, but when i click ok I get an error message saying “The Schema has not been set” and my invoices do not output to email. What am I doing wrong!?
Many thanks!
Hi Ctayts
Can you have multiple sales invoices from a single order?
Yes, if you have had multiple despatches and invoiced in between each despatch.
Hi!
I was wondering whether anyone knew how to create a report that gives a list of journals posted for a specific period.
When I post a journal it creates a report immediately showing that journal. What I want is a report similar to that but for all journals within a specific period.
For example:
Journal 1
Line 1
Line 2
Line 3
Journal 2
Line 1
Line 2
Line 3
Journal 3
Line 1
Line 2
Line 3
Etc etc
Any help would be gratefully received.
Thanks,
David
Hi David
There is a Manual Journal Listing report. This is currently Grouped as follows:
NLNominalAccounts.AccountNumber
NLNominalAccounts.AccountCostCentre
NLNominalAccounts.AccountDepartment
Remove these and group using NLPostedNominalTrans.Reference (or something like URN or PostedID)
You will then need to add/move the fields around.
Thanks
Ctayts
Hi,
I have an issue that simply will not go away. It is mostly associated with a single user but others do get it. This user has had a new PC, different network connection, new cables, new everything! Sage keeps crashing and a text file is created in the c:\pgogram files\sage\mms folder. An example is Sage.UnhandledException.20140322152808
The contents of the file suggest some kink of network timeout. There are no errors with any other application (email connected to Exchange, documents on network etc etc). This is quite simply doing my head in!
Sage version is 200 – 5.00.0052 linking to SQL 2005 on a 2003 R2 Standard server
Unlike Sage – Other applications like outlook/documents on network are not kept open all the time
Whereas Sage is Client-Server connected 100% of the time
You mention that it is a new client PC – which operating system is on the new PC?
(Win Xp / Win Vista / Win 7 / Win 8)
Sage 200 V5 was only supported on Windows XP and Vista 32bit
If the client is Windows 7 or Windows 8 – Version 5 was not supported so this could be part of/or the main problem.
Hi,
I’m just about to turn on Sage CRM Integration but I don’t want a particular set of customers to be created in CRM. Is there any way I can get it to exclude customers with a certain value in an analysis field?
I’ve been told it’s not possible. The only other option I can think of would be to ‘hide’ them from the users in CRM.
Can you suggest anything?
Thanks!
Haven’t looked at CRM integration in a while,
But from what I remember that is correct – it is all accounts or nothing
Unless you develop a complete separate integration – which could be a huge job in itself
As you have suggested – hiding them from other users is probably the best option;
Assign them to an Account Manager that the other users would not have access to:
Or create a new Territory and assign them to that.
Hope that helps
Hi Ctayts
Can I ask what’s this Sage 200 2013 R2 all about ?
Hi Marcus
Am a bit slow posting about this (busy, busy at the moment).
Should get something out this week, but essentially it is a new release for Sage 200.
I do not know why it isn’t branded 2014, but this isn’t a simple service pack.
Ctayts
Hi,
I wonder if anyone could help with a problem I am having when downloading reports as CSV from Sage 200.
When I download any report that has subtotals included it duplicates the line before the subtotal on the csv file. You can imagine when I am downloading something like an aged debtors with many subtotals the data becomes impossible to work with.
We have multiple users yet I am the only one who is experiencing this which leads me to believe it is something to do with settings.
Our partner with whom we have a support contract have suggested that my software is working correctly and the other users are not and the only solution is to rewrite every report. I am really struggling to believe this is the truth.
Really appreciate any suggestions to sort this.
Sharon
Hi Sharon
There are a few known issues that cause duplications. Most are system wide.
The only one that has been user specific is where a machine also has payroll installed as there is a conflict of report designer versions in the two systems. If this is the case it is worth ensuring you are on the latest service pack for your version. What is your version by the way?
Ctayts
Hi Craig,
I’m hoping you can help with an MRP query relating to re-order levels.
We have a ‘Factory’ warehouse and a ‘Finished Goods’ warehouse (they are physically 2 different buildings)
We have a raw material type product which has a re-order level of 8,000.
We have 16,000+ of this item stored in our ‘Finished Goods’ warehouse which we bring to our ‘Factory’ warehouse whenever our stock level in the factory is less than 8,000 (currently checked manually)
The question:
When I run MRP and we have less than 8,000 in our Factory, I am given a recommendation to raise a New P/O for this item – is this correct, even though there are 16,000+ in the Finished Goods warehouse?
The Finished Goods warehouse is set as a Valid Component Warehouse for the Factory in the Warehouse Hierarchy, and also set as ‘Valid for component source’ in the maintain warehouses section.
Any thoughts on this please?
Thanks and keep up the great work!
Paul
I should have said, the raw material product has a re-order level of 8,000 set against the ‘Factory’ warehouse (there is no reorder level set against the Finished Goods warehouse)
Hi Craig,
I could really do with your help on my query above!!!
Please help!
Thanks,
Paul
Craig OR anyone else….
Could anyone help with my questions above regarding Valid Component Warehouses etc?
Thank you in advance!
Hi
I think I’ve made a monumental mistake.
I’ve deleted transactions from one of the bank account casebook ledgers.
Can this be recovererd?
Sarah
Hi Sarah,
It should only be reconciled items that have been deleted.
Unfortunately they are not retrievable. Only option is to revert to backup
Ctayts
HI there,
We are using Sage 200 version 8, and I am struggling to get the stock to reconcile each month (comparing the stock valuation report to the Nominal ledger account)
Is there anyway to reconcile the balance on the nominal if it is different?
We have the system set to use standard costs, and have not changed those standards since the system went live a few months ago.
We are running the month end routine to post the differences betweeen Standard and Actual purchase price, and also the routine to post the difference between the confirmed / unconfirmed balance (although for some reason the posted amount is not the same as the reported difference on the stock valuation report, which is ran at the same time with all other users locked out)
The difference, excluding the difference between the reported and posted confirmed – unconfirmed balance, is different each month so it would not appear to be a static error and more likely something that is happening each month.
Any clues greatly appreciated,
Gary
Hi Craig
I am trying to write a new report and i’m having difficulty with the syntax for an expression.
I’m trying to use OnLastRecord when I insert it to my report it gives me OnLastRecord()
I would normally put the field I want to evaluate within the brackets but it doesn’t like it and won’t let me go any further.
Can you please tell me what the correct expression would be?
Thanks
Bill
Hi,
We are about to migrate Sage 200 from an old 2003 Server machine to a new Server 2012 machine with SQL 2012. We have experience moving databases and i notice that there is also an IIS element. I ahve searched on the internet but am not finding many resources or information on what things to consider while undertaking the migration.
Is this a big job? Could you provide some rough guidelines to follow or even better a Sage document covering the migration operation.
Thank you
Hi John
This isn’t a major job. You would need to do a fresh install to the new server and the. Point the clients to this.
Officially this should be done by uninstalling and reinstalling each machine, however you can amend Paths in the SVN4WIN.ini and MMC.exe files (C:\Programfiles (x86)\Sage\Sage200) on each client to include the new server. Also you would need to export the SSL certificate from the server and import to each Client.
I definitely recommend uninstall and reinstall if you are not sure what you are doing here.
For specific requirements for SQL 2012, Server 2012, IIS and other prerequisites you should review the Deployment guide a available on the disk.
I should query the version of Sage 200 also.
Ctayts
Thank you for coming back to me, I have read through the deployment guide, in our case we are migrating v2011.
My process at the moment would be:
– Install SQL and all other prerequisits on the new server
– Install Sage 200 onto the new server
– Export the databases from the old server
– Mount these databases into the new server
– Migrate the “SAGE” data folder to the new server and share
– Run Sage System Administation on the new server & add in the new companies (DB Info & UNC Paths to the Sage Data)
– Add back in the users manually, set permisions
– Either reinstall the clients or just update the SVN4WIN.ini file with the new path.
———————-
– Can you think of any other areas to consider?
I’m very greatful for your input on this, im suprised a the complete lack of information on this subject out there.
Hi John you shouldn’t need to add users back in as they will be held within the Configuration database. You will also need to amend the MMC.config file on each client if not reinstalling, and import license as per my other reply.
Cheers
Bonus Question: Is there any way of migrating the licence over? I have completed the new install on the new server but am being asked for three registration codes when I open the System Administration. If not, how would one go about getting these registration codes, we only have a single one from our last renewal.
Hi John, the licence is held within the login directory. You should copy this directory as part of moving the Sage Share from your previous server.
Hi,
We are having trouble running BI on Excel.
On one system, we have had it up and running for a while, but recently, it is not updating correctly. Error message says it cannot link to the cube.
On another machine, we can’t add the BI tab to Excel at all. I’ve tried following the help guides, but nothing works.
Can you offer any advise please.
Many Thanks
Make sure you have full admin rights to machine where the tab is not showing. Make sure that the add on is visable in Excel and not disabled.
As for Cube connection, if it used to update have any passwords changed on the server? Are all SQL services running?
Hi there
I have an issue where my sage200 won’t allow the fulfillment option to be changed on a SOP Line
is there any reason for this?
Ian
don’t worry it was a bespoke program causing the issue
Ahh cheers
Hi Ian, are you as a user able to change this (in terms of your general permissions?)
Good morning,
We have installed Sage 200 at our workplace recently and we try to find a way of searching the customer/supplier account if we only have an invoice number/reference. If someone makes a payment to our bank and quote only invoice number they paying and i don’t know the customer reference how do I find it?Is there and easier way than running a report?
Thanks.
Are you wanting to search in the ledger itself? As opposed to the order processing modules?
I would suggest using a workspace. You may need to design one to meet your specific needs. Is it 2013 that has been installed?
Hi.
It’s 2011( I think)Yes, in the ledger itself. Unfortunately we do not have the sales/purchase order processing.
If there isn’t any way of doing this how do i design a workspace?
Hi,
I’ve configured my invoice layouts to send via email and it works great. I’m using the expression “SL Customer Accounts > ContactEmailAddress” in my “To” field. However some customers require invoices to be sent to multiple addresses. Is this possible? Maybe send to all of the default contacts email addresses?
Thanks
Any ideas on this one? It seems likely other people would want to do something like this.
I do not think this is possible as different accounts would have different numbers of emails. You may be able to amend the layout so it produces the invoice x each email address and then pick up the relevant email address for each copy generated.
Hi,
On criteria on reports, can you have a filter that it is “contains”. We want it to use it for batch numbers where we don’t always know the full number, but just a 4 digit part that relates to our purchase order. It is difficult to find if we don’t know the first digits. (hope that makes sense).
Many Thanks
Hi Keri,
Its not possible to create a contains filter on a report,
But depending on your version of Sage 200, you could easily create a workspace
On the workspace you can add a contains filter – where you will get the batch item on screen then you can right-click on your result and run your required report
Hope that helps
Paullyie
Hi,
I have created a sub-report within an aging report. I have managed to get the report to work how we want, but only in pdf format. When exported to excel, the sub-report is not included. Is there settings that can be changed to include the sub-report in the excel version?
Many Thanks
Good Morning,
I am having a bit of difficulty with Sage 50/200 Report Designer.
We are using a third party software to extract information from Sage.
basically we create an expression like this:
“##VAR1 ” + Salesinvoice.Orderno + “##”
This then captures the field Salesinvoice.Orderno
The problem i am having is when i attempt to capture a money/price field it doesn’t capture correctly.
I have been advised i need to use a Wrap Expression to Wrap the Field as text. However i have no experience of this. would you be able to help?.
I also have another field i have come across which automatically puts in a decimal place or a comma e.g 5.143 or 23,678 on a sales order no field. however i want to capture the data without this in it. is there another expression i can use to get rid of this?.
Kind regards,
I am having problems printing an SOP invoice in a new company – when i go into document printing and then print invoices, it will not display my invoice. I have made sure the SOP settings are definitely the same as another company and it works in that one fine.
I have tried all out tricks of the trade and nothing will work!
Can anyone help please?
Thank you.
Hi Megan
Have you been able to Despatch the order?
And you can see this has been done against the order (View Order, select Show Quantities)
Ctayts
Hi,
Do you offer the service of coming into companies for half a day or a day to advise on sage 200?
Let me know
Many thanks
Hi Jonathan
I am employed by Technology Services Group (TSG), we have a team of consultants across the UK who would be able to come and assist.
Regards
Craig
Hi,
We have some Purchase Order/Account errors which are causing us problems for various reasons. Firstly we have some POs which have been set up incorrectly, but have been arrived and an invoice posted so they can’t be amended. Secondly, we have some POs where the wrong invoice has been posted. As they are now marked as complete, we can’t amend them and it is causing problems with our figures and reports. I have read about reverse transaction but apparently this only works on Sterling accounts. Is there another way we can reverse the transaction or amend the posted invoice to allow us to make these corrections?
Many Thanks
Hi I am in the process of setting up a new server ready for our business partner to install Sage Line 200. They have told me that my network needs to be a Domain and not Workgroup for Sage Line 200 to work.
All of my workstations are running Windows 7 Home Prem, which doesn’t allow the computers to join a domain! So I would have to buy upgrades for all 12 computers.
Is this correct in that I need a domain in order for Sage Line 200 to function correctly?
Thanks in advance
Hi Michael
Yes that’s is correct. Home edition Windows is not supported for Sage 200.
Ctayts
Hi Ctayts,
I would like to set up a new Company but not sure how to create the correct database .
If I copy an existing one it will have all the other companies info and transactions on. Is there an easy way for me to do this please ? or do I really need the help of my Business Partner ?
many thanks
Lesley
Hi Lesley
Within System Administration > Companies you can right click and Create New Company.
You will need to know where to Point the Attachments and Spool directories, as well as the SQLServer (you should get an idea of these settings by looking at an existing company).
If you ar unsure of this, I would contact your BP.
You will need to enter a Database name, when you select Create you will be prompted to enter the Financial Year.
You will then need to make sure that the correct Users have access.
Hi
We have successfully set up a new company in Sage 200 and want to copy the nominal ledger structure/codes etc from another group company – is there an idiots guide as to how to do this as I can’t find anything in the help files and am struggling to find anything on line?
Many thanks
Pauline
Hi Pauline
You need to obtain the import templates for Nominal Accounts and Report Categories. These are available on your Sage 200 DVD.
The easiest way to extract the Nominal structure information from a company is to use the List view. Select the Columns with the required information, then use the Send to excel button.
You may need to do a copy and paste exercise to populate the templates.
If you then require the P & L and Balance Sheet structures, you can Export and Import these from the Financial Statements screen in Nominal Ledger > Utilities > Nominal Ledger Setup.
Ctayts
That’s great – thanks.
Hi Dave
We have successfully set up a new company in Sage 200 and want to copy the nominal ledger structure/codes etc from another group company – is there an idiots guide as to how to do this as I can’t find anything in the help files and am struggling to find anything on line?
Many thanks
Pauline
Can anyone give a definitive answer to how many SQL CALS are required to run Sage 200 version 2011 and has this changed with version 2013
Hi Dave
You will need the same number as you have Sage licences.
Regards
Ctayts
Hi ctayts,
I’m looking to do a simple – I hope, conditional formatting change to the Despatch Note layout ….
For the quantity expression (SOPDespatchReceiptLines.DespatchReceiptQuantity), I would like to highlight the quantity if it is more than ‘1’.
However the expression currently has conditional formatting applied to it:-
if SOPOrderReturnLines.ShowOnCustomerDocs = 0 then
begin
SuppressPrinting := True;
end
How do I also incorporate the above with my needs?
Regards
Marcus
Ok I’ve got the expression I need working which highlights the quantity, but i’m unsure how to include it with the above.
if SOPDespatchReceiptLines.DespatchReceiptQuantity > 1 then
begin
Formatting := FormattingStyle(“Highlighted”);
end
Hi,
Is there a way to set on Sage certain products so they can only be sold to certain customers?
We have some products which can only be sort to certain customers (ie they have exclusive rights to a product in the UK).
Many Thanks
Hi Keri
Unfortunately not. This is something that I see requested quite a lot, particularly in terms of setting up Price lists.
Ctayts
It would be great I agree, but unfortunately not.
Ctayts
I wonder if you could explain the concept and use of product groups in Sage 200, please?
Hi VT
Product Groups are a way of categorising Stock items. This allows for better reporting breakdown and analysis and also you are able to set settings which will be defaults for associated stock items.
Regards
Ctayts
Hello There,
We are using Sage 200 and were facing the problem as below
We are unable to view the transactions in the Transactions enquiry tab of the VAT control account and also unable to see these transactions in the Nominal ledger generated.
Could someone please help us on the above issue URGENTLY
Thanks,
K.Ramu
Hi
Is it only this nominal code that you are unable to view the transactions on?
Do you get any error/message when attempting?
How long are you keeping transactions for? And are you set to archive after this period?
I would check the number of days against this nominal code and the. Your nominal ledger settings to see if Archiving is enabled.
Ctayts
Hi Ctayts
Thanks for the reply. Could you please advise us for the below
Yes. We are not able to view the transactions only in the VAT nominal code and we are not getting any error/message when attempting.
Archiving option is enabled. We are unable to view the VAT entries posted regarding the bills/invoices in the Nominal ledger and in the transactions enquiry except the Journals posted to the VAT account. Please advise.
Thanks for your help in advance.
Regards,
K.Ramu
Hi
I cannot understand this. Has your BP looked at your data?
What is the setting for how long you keep transactions?
Ctayts
I would suggest you ask your BP to look at your data.
It seems bizarre what you describe.
I would check the settings on the VAT account for how long transactions are kept.
Ctayts
Hi Craig
A further BI question – is it possible to make all reports in an excel workbook ‘static’ in one go or do you have to set each one individually?
Thanks
Pauline
Hi Pauline
You have to do this individually.
Ctayts
Hi
Sage BI issue – I have eight separate BI reports in one excel workbook and have linked them so that I only have to change the accounting period once and they all update. Seven of the reports all update correctly however, when I update the accounting period, one of the reports changes the date but 12 months on, so instead of being period 4 2013 (like the rest of the reports) it is period 4 2014 etc, I have tried deleting and re-creating the report with different titles etc but keeps happening – is this something you have come across before – I can easily de-link this report and update manually, but would prefer it if it worked like the others. Any help greatly appreciated.
Many thanks
Pauline
Hi Pauline
This isn’t something I have come across. If you copy the workbook, delete the reports other than the main Report and problem report, do you have the same out come?
Ctayts
Hi,
When posting our purchase invoices they all default to unauthorised, however when we authorise the invoice it is duplicating the postings to the nominal code account for the expense code and then offsetting the duplication by posting the difference to the exchange rate variance code. This is resulting with inflated costs and a massive exchange rate gain in our accounts. Have you experienced this and is there a solution to stop it happening? It only appears to happen on multi line invoices and the duplicated amounts being posted are the total invoice value not just the individual line amount.
Your help would be greatly appreciated
Hi Jonathan
This is a known bug (number 5095). This issue is fixed in Hotfix 1 for Sage 200 2011 SP5
ctayts
Hello There,
We are using Sage 200 and we are facing the issue listed below.
We will not be able to see the transactions posted in cash book in nominal ledger. Could you please some on help us urgently how we can see this in nominal ledger. Is there any setting required?
Hi Nandha
I cannot see which issue this relates too.
How are you entering the transaction? Does the Nominal Analysis in the Cashbook Transaction enquiry give you any information about the Nominal code and location (Posted, Current or Deffered)?
Thanks
Ctayts
Hi Ctayts,
Thanks. We had a look into the Nominal Analysis in the cashbook Transaction enquiry and found the Cashbook 110 code has been linked to Nominal Code 100 and nearly 150 transactions was in the Code 100.
Is there any easier way to move these transactions from Nominal code 100 to 110.
Thanks,
Nandha
Hi Nandha
Unfortunately your only option is to enter a journal.
Ctayts
Hi Ctayts,
The Cash Book coded 120 linked to the Nominal Ledger code 120 is posting transactions to the Nominal Ledger code 110. Is there any way we can make sure this doesn’t happen in the future.
Also we are unable to generate the VAT control nominal account report in Sage 200. Please advise.
Thanks,
Nandha
Nandha
I have never seen this happen before. What transactions is this occuring on? Is the Bank Account definately set up correctly?
I would suggest that someone looks at your data for you.
ctayts
Hi Craig,
I have piles of purchase invoices to post at work at the moment. I have all the data in CSV but I am having a bit of trouble trying to convert it into a usable CSV import. Ideally I would have the invoices imported as multi-line records to make allocation easier at a later date. The problem I have is that I keep getting errors when I validate the import files to do with the tax analysis totals. I think this is down to rounding as the figures I have are the totals of thousands of small transactions with the VAT calculated on each transaction so that my VAT total is not exactly 20% of the net value. For example, I want to import the following:
N/C Net VAT Gross
5100 26318.44 5263.42 31581.86
5101 277.14 55.41 332.55
5102 2047.33 409.43 2456.76
5103 64.3 12.86 77.16
So total net value is 28707.21 therefore VAT at 20% should be 5741.44 but I want to post the VAT as it appears on the invoice at 5741.12 so I’m 0.32 out.
If you were posting it manually you could just overtype the autocalculated VAT quantity but it doesn’t seem to like it on my import file.
Any suggestions?
Thanks
Tom
Hi Tom
There is no problem with the Tax Value not being exactly 20%.
Aslong as the Tax and Net values = the Gross values then this should be fine.
So (assumption is that the transactions are all in Base Currency) the Goss value (31581.86) should be in GoodsValueInAccountCurrency and PurControlValueInBaseCurrency
Tax Value (5263.42) should be in TaxValue and TaxAnalysisTaxOnGoodsValue/1 (assuming only 1 VAT rate)
Net Value (26318.44) should be in NominalAnalysisTransactionValue/1 and TaxAnalysisGoodsValueBeforeDiscount/1
Thanks
ctayts
Is there a way to invoice per dispatch rather than invoicing every dispatch on a sales order through sales order processing?
Hi Jonathan
No, the standard Sage 200 functionality is to consolidate dispatches onto an invoice. The only way would be to despatch, then print invoice, then do next despatch and so on.
Ctayts
Hi,
Completely unrelated, but something else that has been bugging me. Our stock control and SOP was up and running before our accounts department fully moved over from Sage 50. Due to this, we have some SOP orders which have been shipped on Sage 200 but have been invoiced on Sage 50. Therefore, in SOP the orders are still showing as Live as the system thinks they are waiting for an invoice. Is there anyway we can mark these orders as complete without issuing an invoice on 200?
It’s a little annoying have so many ‘live’ orders on SOP.
Many Thanks
Hi Keri
As standard no. You would need to invoice them and then credit in the ledgers.
What you could is (with help from your BP) have. SQL script created, to complete the required orders.
Ctayts
Hi
A Sage 200 BI question if I may. I have created and tailored a report using BI for one of the companies in our group. I want to use the same report for another company. Rather than recreate the report, is there a way of copying the report and changing the connection so the copied report picks up data from company 2 rather than company 1?
Many thanks
Pauline
Hi Pauline
If selecting via the Sage 200 menu, it will pick up based on the company you are logged into.
If not from within Sage 200 then you are able to amend the connections using the connections option on the Sage BI toolbar.
Ctayts
Hi,
I am trying to amend some templates that our old employee had added on Sage. I am having trouble with the POP Purchase Order layout. We thought he had just amended the aesthetics, but I think he must have changed some filters too as now the free text and comment lines don’t print out. I know they do on the original Sage templates, but I don’t really want to have to start again from scratch as all our layouts have been set-up to look similar. I am assuming it is just a filter or formatting that is excluding these lines, but I can’t seem to find it anywhere. Do you have any suggestions? Basically we need to see all lines on the print-out regardless of the line type.
Many Thanks
Hi Keri
Its likely there is a report filter set up in DATA > FILTER, or it is possible they have added one to the Line Section (Select the section, See Filter within the Properties pane).
The standard layout does have a couple of sections for order lines, which relate to different scenarios
Ctayts
Hi,
I have looked in the Data > Filter and there is nothing sent.
The Filter for the section product line id header 1 is:
Not (POPOrderReturnLines.LineTypeID = 0 AND (POPOrderReturnLines.PricingUnitDescription POPOrderReturnLines.BuyingUnitDescription OR POPOrderReturnLines.BuyingUnitMultiple POPOrderReturnLines.PricingUnitMultiple) AND POPOrderReturnLines.ShowOnSupplierDocs 0)
and header 2 is:
POPOrderReturnLines.LineTypeID = 0 AND (POPOrderReturnLines.PricingUnitDescription POPOrderReturnLines.BuyingUnitDescription OR POPOrderReturnLines.BuyingUnitMultiple POPOrderReturnLines.PricingUnitMultiple) AND POPOrderReturnLines.ShowOnSupplierDocs 0
These look exactly the same as the original Sage templates which is why I’m so confused.
Hi,
I figured this out over the weekend.
The “Include Records with No Transactions” was set to false.
I have changed it to true and it appears to be showing all the information needed.
Kind Regards
Keri Boorman
Internal Freight and Clearance Co-ordinator
AgChemAccess
Tel: + 44 (0) 845 459 9413
Fax: + 44 (0) 207 149 9815
Email: Keri@agchemaccess.com
[cid:image001.gif@01CE0EC9.8CA03CD0]Winners of “Best New Service Sector Company” 2008
Confidentiality Notice: This email transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the email address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this email is strictly prohibited. Any statement and/or opinion not related to the company’s business and expressed in this message is that of the author and does not necessarily reflect those of AgChem Access Holdings Ltd or associated companies. This company does not take responsibility for the views of the author in any matter not related to the company’s object. If you have received this email in error, please reply to the sender so that we can arrange for proper delivery, and then please delete the message from your inbox.
Please see our website http://www.agchemaccess.com for our full terms and conditions.
P by not printing this email you are respecting the environment
Hi
I am attempting to link Sage 200 to Excel via ODBC/SQL with a view to creating a trial balance in excel which shows both the movement for the period as well as the year to date cumulative position – but I am struggling to find the correct tables to use. Any guidance gratefully received.
Hi Pauline,
Why do you not use BI. Its made for this example!
For the movement you will probably need to calculate this between each period.
but you would need the NLAccountPeriodValue, also linked to SYSAccountingPeriod to get the required periods.
Hi
I am looking to set up a new company in Sage 200 – is there an idiots guide as to how to do this as I can’t find anything in the help files and am struggling to find anything on line?
Many thanks
Pauline
Hi Pauline
You will need to do this in System Administration, but you will need to have an understanding of the Installtion data structure and what your SQL server is called. You could look at the Properties on an existing Company to find this.
Login to System Administration
Right Click Companies and select Add New Company
Enter the Name
Enter the paths to Attachment and Spool Directories
Enter the SQL Server name (can select from dropdown)
Enter a Database Name
Select Create E
Enter the START of the financial year
Click OK
This will run through and create the company.
Give user access to this Company then login and enter Settings.
You should also be aware of your SQL Server setup for database backups and Admin etc. and make sure this new database gets included.
ctayts
That’s great, many thanks!
Hello
I keep getting an error mg saying ” in order to use PCcsquofileheader.sccsquofileheadEx” you must specify a license string for the control. Use licenses. add to add the license string to the licenses collection. PCcsQuoFileHeader.SCcsQuoFileHeadEx
Hi Nahal
This doesnt look like standard Sage 200. Are you using any addons?
ctayts
We are new to using Sage 50 Manufacturing and have come up against a few issues which are causing us some problems. I was hoping you would be able to help me!
We put our sales orders into sage accounts and we used to print the job cards in accounts. We are now using a job sheet in manufacturing.
The issue we have is that if there is an order with two products codes which are the same, but have comments which differentiate them in some way, manufacturing is pulling both comments from both lines on the sales order and displaying them both on the same comment line for both product codes e.g.
product code: 1234 comm: pack with x type screws.
product code: 1234 comm: pack with y type screws.
This shows on the job sheets as:
product: 1234 comm: pack with x type screws. pack with y type screws.
product: 1234 comm: pack with x type screws. pack with y type screws.
The problem is that we have to identify them and change them in WOP, which means extra work when we were hoping this system would be more automated.
Our company manufacturers items on quite a long lead time, we are doing batch jobs on our popular items and what can sometimes happen is we ask them to make 500 qty, but by the end some have been scrapped and this ends up being maybe 480 qty which come through on the last operation. We do not care about those 20 qty as it would be a waste of time for them to start on 20 from the beginning (equally sometimes a higher qty can come through) Now when I go to complete a works order, I wrongly assumed that it wouldn’t let me complete a qty higher than the amount which has been last scanned in the last operation, but it does and it also doesn’t warn me in any way that this is the case, is there any way of setting it to complete the qty which was scanned in the last operation?
I would be extremely grateful of any help you could offer me.
Hi Louise
Sorry but I am a Sage 200 consultant and though I have some knowledge of Sage 50, I do not know Sage 50 Manufacturing.
The best bet is to contact Sage Support.
Ctayts
Many thanks for your quick reply. We have spent some time talking with Sage Support, the answers are basically no to everything above, unfortunately you were my last resort! Thank you anyway and I like what you are doing here on your blog.
Hi,
I am new to SAGE and have just started a new job. The Management Accountant is spending a vast amount of time reconciling the stock.
It would appear that the nominal ledgers are not turned on for postings from stock. This is resulting in the manual reconciliation of stock and posting to the ledger. I am also unsure if the standard costing functionality works.
I need to know the best way to get a handle on how the system “should” work and what the impact of turning the nominals on for stock etc.
Also I need to know how to access and write custom reports.
Can you help or advise where to go?
Thanks
Dave
Quite a lot in there David and suggest you contact your BP for some consultancy.
It’s easy enough to switch on postings for Cost of Sales in Stock Control settings for Sage 200. You will need to be sure that all items and product groups have default nominal codes (in the case of COS the Issues default is used). Also consider in SOP settings when you wish COS to be updated to the Nominal (Despatch of Invoice Post)
You can create custom reports using the report designer.
Thanks
Ctayts
Hi,
many thanks, that is a good starting point.
Do you know of any guides for the custom report designer?
Hi David
There isn’t any I am afraid. If you are using Sage 200 2010 or 2011 the help files are ok. Also I have posted a few things on here.
Ctayts
Hi Ctayts
In your reply you mentioned that for COS items the default is used – is there any way of changing this?
We have a few products and they have all got seperate nominals in the setup but when we issue them they all go to one code and I need to identify each transaction and journal it to the correct code.
Thanks for your help
Bill
Hi Bill
Each item can have a specific Nominal code specified for Cost of Sales. This is the Issues Default on the Stock Record.
Please bear in mind that there are settings in SOP settings that can override the Stock item default, if you use CC and Depts.
ctayts
Hi – new to Sage 200 – we have a number of companies with multiple cost centres and departments – when I run the standard trial balance reports, selecting ‘all’ cost centres etc, the TB that is produced lists the balances for cost centre 2 after those for cost centre 1 etc, is there a way to get a TB that has just one set of balances for the combined company (i.e. not splitting out cost centres separately)?
Hi Pauline
Not sure I understand. Which TB layout are you using?
Do you not want to see balances for accounts with CC and/or Depts? If not then you would need to look at using Group accounts and run a TB just showing Group type accounts.
Thanks
Ctayts
Hi there
We are using SAGE 200 2010. We have 2 invoice types set up on 1 of our companies that is defined based on what service the customer is taking (selected in SL Stationery, sales invoice layout). Working fine for the last 2 years. Yesterday I went to process our customers on repeat orders and they all have the same invoice layout, however on processing the ‘COPY’ reprint text appeared even though the invoice had never printed or been run previously.
It appears to be doing it with any customer on this invoice type, other invoice type is fine, I have looked at the layout in report designer but cannot locate the condition that causes SYSCompanies.ReprintText to appear or not.
No recent work has been carried out on any layouts (last time was about a year ago!)
Please help as I am at a lose as to what is causing this to occur.
Hi Michael
There should be a conditional property against this field.
Do you ever need it to print ‘copy’ (I.e. when you print a copy)? If not you could remove or suppress the field.
Ctayts
Hi
Having problems generating payments in Sage 200, I have done a run and got the message “the payment is valid but the payment batch has been rejected because of errors elsewhere” .
Any idea what’s wrong ?
Eunice
Hi Eunice
Is there no error log? What type of payment are you trying to make? Is BACs involved?
Ctayts
Hi Ctayts,
We have recently updated to Sage 200 V2011 form V5.1 and I am still having issues with trusting the accruals reports. when I go into POP maintenance/ Order and Returns Accruals there doesn’t seem to be any consistency. Many of the values to be accrued are a negative value ( Why?), and some have no value what so ever – even though when I look at the Purchase Order the whole amount should be accrued for, and then the odd value appears to be correct. Maybe I am misunderstanding how the accruals works within sage 200 but it just doesn’t look right to me. Surely the value to be accrued should equal the total value that is still oustanding on the purchase order and as such should never be a negative value .
I would really appreciate any feedback/ help that you can give me as I am having to manually look at the accruals at mont end and this is avery painful and long process !!:(
many thanks
Lesley
Hi im new to sage line 200 and have done a cheque run thorough payment processing. There are 60 cheques to be written but the person writing them made an error on cheque 30. When this happened previously i could not see any way to stop the numbers mid stream on generate payments i.e the list would go 1 to 29 then 31 to 61. It just went straight through with the numbers thus posting the wrong chegue numbers for half the cheque run. Is there a way to do this?
Hi Neil
What you are able to do is use the ‘RePrint Payments Only’ option within the Generate Payments screen. So go to Generate Payments, enter the new starting reference but tick the Reprint box at the top.
You will need to be careful though as 1-29 were fine, these accounts will need to be deleted from the payment file by using ‘Delete Suggested Payments’, so that these are not re-referenced.
ctayts
Hi
We have an item we would like to convert to having Batch number, i have set up the product group but it will not allow me to change it. is there a way around this?
Thanks
Liam
Hi Liam,
You are not able to change it. For this you will need to create a new Product Group and also create any Items again.
ctayts
Hi, just wondered if there was some kind of info on the interweb that you know of, that lists what info each report within sage gives you. I am looking for one that will give me the top ten products we sell, I want to be able to see this by county or by the UK as a whole.
Hi Ruth,
There isnt anything specific. But if you have the Business Intelligence module, this is made for this type of report.
Craig
Hi Craig
We have just migrated to Sage 200 from Sage 50 and I have tried to pay my suppliers using eBanking (which I have been able to do in Sage 50) but something is obviously not set up correctly as although the payments have been applied to the Purchase Ledger accounts and the Bank account, a CSV file wasn’t generated. I’m sure it’s in the set-up and I have read your how-to but I’m still not sure how to set up the payments for eBanking. Can you please give me a dummies guide ?!
Hi Eunice
It needs to be enabled in Cashbook Account record. In the e-banking tab select the service you need.
In Purchase Ledger Settings Make sure Electronice Payments is enabled. Then on the Payment Groups Tab create one and select to Electonic File for the Document/Output option.
You then need to link the applicable Supplier accounts to this Payment Group. In each supplier record you can do this.
ctayts
Hello there,
We have Sage 200 manufacturing with multiple levels of BOM’s. We are looking for a cost/BOM analyst with management accounting to help us on a temporary/consultancy basis – any ideas.
Liz
~Hi i installed our sage200 system in 2011/12, i work as a management/cost accountant looking after sage. i ended up pulling all the info from Line 100 as Sage couldnt help us and told us they could get notes through. what was you looking for?
Hi Craig,
I’m having a problem with my Report Designer. When opening from Sage 200 or a saved report on my desktop, the application only flashes up for a second then closes. It has gotten slightly better since I first tried to open it, but still will not stay open.
Any Ideas?
Thank you
Kelsey
Hi What version are you using.
There are some issues where Sage Payroll and/or Sage 50 is also installed the machine.
Do other users experience this?
Ctayts
Hi Craig,
2011, i don’t have sage 50 or payroll and its just me that’s experiencing this. I’m happy to re-install the client but if you have any other ideas that would be great.
Thank you
Kelsey
I would try a reinstall, there is also a hot fix/patch for the report designer which may be worth applying.
Hi Craig,
I use Sage pay to take payment in sop but would also like a cash drawer to ping open for cash sales and an integrated chip and pin machine for customer present transactions (preferably commidea). Is this something that you have come across?
Regards,
James
Hi James
This isnt something that I have dealt with befor I am afraid.
Ctayts
Hi , We have recently upgraded to version 2011 and I am having difficulty in reporting on financials. The reports are only sdet up to pick one accounting period but I would like to be able to select a period ‘from’ or ‘between’…is that possible ?
many thanks
Lesley
Hi Lesley
you can enable the Accounting period and this will allow you to do as All, Is, In
Analysis Period cannot be amended.
ctayts
Hi Ctayts,
I have gone into report designer and to thye criteria – and in here i have made sure that the accounting period is enabled, but it still wont let me choose from thee drop down list – i can only pick one accounting period. I am obviously doing something wrong ??…please cen you talk me through step by step…….
many thanks
Lesley
When in Report Designer > Data > Criteria and having selected the Accounting Period criteria
Under the ‘Status’ field (where you have selected Enabled), is a, ‘Available Range Selectors’ field, Select ‘All’
At the bottom you are able to select the default.
ctayts
Also make sure that the field SysAccountingPeriod.StartDate is added to the report. You can supress this within the field Properties.
ctayts
Hi,
I am having trouble after converting our sage 200 to a virtual machine in hyper-v to be used a a test system.
When starting Sage 200 (7.00.0044) I get the login screen but it fails after my credentials with “Directory specified for company’s Spool path does not exist. Check Sage 200 configuration and the the network is available. The server is pingable from my PC and I can see the c:\sage share
I have checked the c:\program files\sage\sage200\SVN4WIN.INI which contains:
SVNDIR=\\SAGEVM\Sage\Logon\,C:\SAGE\SVNDATA,C:\Sage\Spool\,,c:\SAGE\SVNPROGS
(I changed the server-name to match the SAGEVM name)
Sage Spool does exist however c:\sage\svndata or svnprogs does not neither does it on the live system though.
Any Ideas?
Thanks
Paul
Hi Paul
Are you receiving this error on the server or a client machine?
The problem is likely to be down to the company settings within System Administration. Against each company there is a path to the Spool directory. Make sure that this path exists. I would expect it to be \\SAGEVM\Sage\Spool or \\SAGEVM\Sage\Spool\
ctayts
Hi Ctayts,
Are you aware of any issues with SagePay integration with Sage200?
Reproducible error:
Create order for 5 Widgets @ £24ea + VAT @ 20% (Total £288 inc VAT)
Complete order by taking payment via SagePay integration, Save Card, Despatch, Invoice, Post.
All fine there ….
Next:
Create new order by copying previous order and complete payment using stored card information etc.
For the 2nd order Sage records payment on the ledger as £288 correctly however payment taken by SagePay is completely wrong and bears no relation to the correct amount – £138.00.
As you can imagine this is causing some large headaches ……
Sorry Marcus, nothing that I am aware of Although to be honest it isnt something I have had a great deal of involvement with.
ctayts
Hi there
Is it possible to get a stock transaction History report which also includes the URN and also the date the postings are actually made, as opposed to the date entered on the system.
Any help would be fantastic.
Thanks
Nick
Hi
URN can certainly be added. The date on the standard report should already be the transaction date.
ctayts
Hi there, I have been asked to prepare some data extracts from a Sage 200 system. I need to be able to pull from a nominal ledger cost centre, the relevant accounts payable entries that made up the balances against the cost centre and it’s accounts. Could some help with the database tables I need to be using please. I think I need certain tables prefixed ‘PL’ but not sure which ones, and then certain ‘NL’ prefixed tables but again not sure which ones I would need. Any help greatly appreciated. Thanks Ajay
Hi Ajay
You are probably looking for NLNominalAccount (Nomianl Account details), NLCostCentre (Coct Centres), NLPostedNominalTran (Transactions posted to the nominal), NLReportCategory (Report categories), and PLPostedSupplierTran (Transactions posted to the purchase ledger).
ctayts
Hi Craig,
After running MRP, can I really not combine purchase order recommendations for different items that come from the same supplier???
For example, I have a very simple Works Order that requires 3 different components, all supplied by the same supplier.
MRP suggests 3 purchase orders, but I would like to be able to combine them into one purchase order.
Is this just how it is???
Regards,
Paul
Hi Paul
You should be able to Combine Purchase Orders by selecting them in the list view and selecting the combine icon. It may be that you have not been given access to this function.
You cannot combine buy recommendations linked to demands for sales order items with the fulfilment type from supplier direct as the purchase order items need to hold the delivery address from the sales order.
If multiple locations are used the items being combined must come from the same warehouse.
ctayts
Hi Craig,
I can combine purchase orders with the same stock code, this is no issue at all.
However if i try and combine 2 purchase order recommendations that are for 2 different stock codes (both from the same supplier though), I receive a message box saying that the stock codes are different and the orders cannot be combined (or words to that effect).
The recommendations are not linked to demands for sales order items, they are for items on the Make to Stock list.
I would really appreciate any more light you can shed on this for me!
Thanks,
Paul
Hi Paul
Looked into this a bit more.
You can only ‘Combine’ the recommendations if the items are the same. However if you ‘Action’ the the two recommendations together, this will create the single PO.
ctayts
Hi,
I am having a problem with the Sage Notification Service keeps crashing with the following error:-
Logon failed due to unexpected error.
Please check if the Sage200Configuration database is configured and running.
(Message was: The type initializer for ‘Sage.Common.Caching.EntlibCacheManager’ threw an exception.) —> The type initializer for ‘Sage.Common.Caching.EntlibCacheManager’ threw an exception. —> The file exists.
I just cant get to the bottom of the problem, the Sage200Configuration database is running ok. Any ideas?
Regards,
Peter
Sorry Peter this isnt something I have come across before.
Has it/does it ever run? It could posibly be a permissions problem.
ctayts
Hey Guys,
Do you know why the field barcode (StockItem table) isn’t available for maintenance?
thanks in advanced for your help.
Regards,
Fabio
In what way?
I am not a developer. My understanding is that it’s a field for storing a bar code which can then be reported on.
ctayts
HI Ctayts,
I’ve found the field on the table StockItem however the field barcode isn’t available in Sage 200 screen AmendSTockItem.
I would like to know how to make this available for the user.
Hi Fabio
You would require the Developer kit to add a field to the screen.
ctayts
Hi Ctayts
While this is not a strictly a Sage200 question, I’m hoping you can help.
I wanted to create a tiny Access2013 database application which references the Sage200 Stock tables so the warehouse could quickly look up expected stock levels for a particular SKU (we haven’t got enough licences for them to have their own Sage200 login).
When I create a linked table (readonly) to the required table, the first record in the list displays fine, but if then go on to search for another SKU all I get is ” #Deleted ” shown in the fields.
Is this some strange Sage ‘you may not reference our database because we said so’ thing?
Thanks
Marcus
Hi Marcus
I thought I had replied to you about this. I can’t understand from a Sage point of view why this would be a problem.
I presume you can view data if querying using Excel?
ctayts
Hello Craig
I would appreciate your thoughts on this. We have a client using Sage 200 2011 (SP5) who have an intermittent issue with stock becoming “stuck” in reserved stock meaning that it is not available for sale. The fix is to run Balance Ledgers for the Commercials however this is not easy as there are a large number of users.
The client does not use batch/serial no tracking and does not do back to back orders. Sage have said that it must be an “environmental issue” rather than a problem with Sage 200.The client has some Sage 200 add ons however they do not interfere with the Sage 200 logic that controls reserved stock.
The problem happens on average once or twice a day and is a major annoyance.
Hi Allan
Is Reserved Stock one of your locations?
Are you using BOM or Manufacturing?
It seems a bizarre one. What error does balance ledgers give?
ctayts
Hi Ctayts,
Can you point me in the direction of which tables I need to link etc to create a view listing all the components of a BOM?
Just using the BOM and BOMLine tables doesn’t list any sub-assemblies despite the Sage Database info document stating that it will list the sub-assembly, just not the components within it.
I need to list everything that’s needed for build the BOM.
Thanks
Andy
Hi Andy
You will need BomComponentLine
ctayts
Hi Craig
We have an ongoing intermittent issue whereby stock becomes “stuck” in reserved stock meaning that it is unavailable for sale. Running the commercials balance ledgers routine fixes the problem but we want to know what is causing this.
We are using Sage 200 2011 (SP5) and we do not serial/batch numbered items or back to back orders.
Allan
Hi, we are currently using version 5.1 and are about to upgrade to 2011 and I was hoping you may be able to tell me if there are any major differencies in the versions and also if it is possible to get a hanbook for version 2011 to look at – or do i need to request this from our business partner?
I am particularly interested in knowing if the accruals works correctly in v 2011
many thanks
Lesley
There are no Sage 200 2011 handbooks. Sage do not produces these with their software anymore as everything is available within the Help pages (which are very good). Your BP may have written some manuals that you may be able to obtain.
The accruals to my knowledge works the same, but I can’t think of any bugs with Accruals so not sure what you mean by correctly.
Thanks
ctayts
Hi Ctayts,
Got a project Profitability Analysis (Structure) report to produce. I want to be able to filter by individual projects and to exclude certain project items. What would you suggest.
Imran
Hi Imran
Sage 200 has a BI Project Profit report as standard (Gross Profit By Projects This Year and Last).
This could be amended to included particular Projects, or Project items. Either by using Filters, or Specific selections within the Report Layout pane.
Ctayts
Thank you changes made.
In terms of period the reports are picking up, it’s set to period 2, which is correct. How would I go about choosing period 1 on its own.
Hi Imran
Depending on the report/cube you are using you will need to pick up something like the Accounting Period field.
This may be shown as a criteria already in which case you can double click or you may need to amend the report using the Report Layout option. Select the field (Accounting Period) which is displayed (in Column or Row). Select the Period from the values diplayed on the right.
ctayts
Hi Craig
Thanks for your help on my earlier questions.
How do businesses choose between Microsoft Dynamics NAV and Sage 200? Is there a series of questions to ask?
Jack
It generally comes down to how much flexibility you want and your budget. Sage 200 is much more of an ‘Out the box’ solution. Although bespoke solutions can be written for it. Nav isnt something I have any real experience or knowledge of, but I am led to believe it is designed to be much more configurable. That said due to this it is generally more money to implement and for on going support.
There are probably some comparison guides on the internet.
ctayts
Hi Ctayts,
Hope your well and may I thank you for all the good work you’re doing with your blog.
A question I have is put third party supplier has installed BI module but when running reports it only goes to row 107, he said this is because this is the default setting. Is this true, I have my doubts. I’ve manage to unfilter the report to show all the rows.
Imran
Hi Imran
This is true yes. But it is just a setting within File > Report Layout options (Limit Rows to). You also have the same for Columns which default to 50.
Another similar item to be aware of is the page sizing within the Layout Pane. By default it will only list the first 100 values within a Field (E.g. first 100 customer values), you are then able to click for the next 100. This default can be amended within Personalise > Paging Size. Nb. this also effects the number of values displayed within the Criteria/Slice Fields.
Ctayts
Help. Ive messed up a sales order. When adding stock onto a sales order i have clicked on the description bar and changed the order of the items. I have tried clickibg back but its just organised them in a alpha numerical order. I need to revert back to the original status if not i wiil have to delete it and start again. And its a large order. Any ideas. ?? Please
Hi Angela
Is it such a problem from a screen point of view? The order printed on documents will be dictated by the sort of the layout design.
Ctayts
Hi
Is there any way to change following in bulk on sage 200;
Account credit limits (from £0.00 to £0.01)
Statement layout option on sales ledger accounts (from paper to email)
Many thanks
Tom
Hi Tom
There is no function within the program, like in sage 50.
What could be used is a SQL script. You would need to be sure of the exact criteria. If it’s update everyone then it’s a lot easier.
UPDATE SLCustomerAccount
SET CreditLimit = 0.01
You may want your BP to assist, and certainly take a backup and test.
Ctayts
Thanks for your reply
I’ll have to amend individually the account limits as its not all customers that need doing 😦
Would the same apply for the statement default , to change from paper to email?
Tom
Hi Tom
A script would still be possible, but the different criteria would need to be taken into account.
E.g. UPDATE SLCustomerAccount SET CreditLimit = 0.01 WHERE AccountNumber = “ABC001”
UPDATE SLCustomerAccount SET CreditLimit = 100.00 WHERE AccountNumber = “XYZ001”
UPDATE SLCustomerAccount SET CreditLimit = 1000.00 WHERE AccountNumber = “ZZZ001”
OR
UPDATE SLCustomerAccount SET CreditLimit = 0.01 WHERE CountryCode = “GB”
The Statement layout can be amended via a script also, but there are a number of tables that need to be joined.
UPDATE dbo.SLCustomerDocument
SET dbo.SLCustomerDocument.SYSDocumentLayoutVersionID = 2
FROM dbo.SLCustomerAccount INNER JOIN
dbo.SLCustomerDocument ON dbo.SLCustomerAccount.SLCustomerAccountID = dbo.SLCustomerDocument.SLCustomerAccountID INNER JOIN
dbo.SYSDocumentType ON dbo.SLCustomerDocument.SYSDocumentTypeID = dbo.SYSDocumentType.SYSDocumentTypeID INNER JOIN
dbo.SYSDocumentLayoutVersion ON
dbo.SLCustomerDocument.SYSDocumentLayoutVersionID = dbo.SYSDocumentLayoutVersion.SYSDocumentLayoutVersionID
WHERE (dbo.SLCustomerDocument.SYSDocumentTypeID = 4
The above script would update everything to Email Standard. Again you could add to the WHERE clause to filter as required.
Ultimately the SLCustomerDocument.SYSDocumentLayoutVersionID is to be amended. 0 = Paper Standard, 1 = Papaer Alternative, 2 = Email Standard and 3 = Email Alternative.
ctayts
Hi Craig
Thanks for providing this resource. My questions:
1. How difficult or expensive would it be to add a field in an SOP form so that essential export information appears on an invoice?
2. We need to produce a report to summarise profit per project. Each project has a four-figure code. The report would have to draw together data for the project from SOP, purchase ledger (goods bought to build the customer’s order), Bill of Materials (labour and other costs), cash book (receipts), nominal ledger (we gain or lose something if we are dealing in a foreign currency). How difficult would this be? Is it expensive to have someone make such a report?
3. If a BP makes modifications to your Sage 200, does that making switching BP more difficult?
4. Where is there a good source of training materials?
Regards
Hi Jack
Thanks for your comment.
1. Hard for me to say as I am not a developer. It depends on the information that needs to be entered into the field, whether it would be Free Text or Selected from a Pre-Defined table. Probably between 1 and 3 days.
2. Project Accounting already has Profit Reports available within Project Enquiries, you can aslo use BI. To write specific report Designer reports for Project Accounting is notoriously difficult due to how the Project Data is made available to the reporting model.
Would the report need to draw from BOM or would it Draw from Stock after the BOM has been built? BOM doesnt link to Project Accounting. If you are selling Stock via SOP then this too cannot be analysed to Project Accounting. You would need to use Billing within Project Accounting.
Depending upon some of the above, and how you would want the report laid out etc you are probably looking at a high expenze (in terms of Report costs).
3. It shouldnt do. But will depend on the degree of modification. You should also consider who owns the Source code of any modifications. Also make sure any new BP is fully aware of the fact you have modifications. The BP I work for (TSG) has a development team dedicated to Sage development work. If we were to take someone on we would audit the system to gain an understanding of the set up and any bespoke functionality.
4. Sage 200 does not have an array of materials readily available. This is the main reason for my blog. Your best bets would be the Help within the system, your BP (do they have any manuals that they have written) and TalkingSage200 of course!
ctayts
Re question 2, I should have told you that we do not have Project Accounting. We have Financials, Commercials and BOM.
We tend to manufacture to order, and what we build is generally bespoke and expensive. Each order or contract is called a “project” internally.
The report could draw from Stock after manufacture, and we do want to sell stock via SOP.
Would BI or Excel integration help?
Hi Jack
That would make it easier then. Most likely option is Report Designer and possibly some SQL views to bring the data together.
Probably a day or two to create.
Hi guys,
Quick question with in report designer if I have a report and a nominal with no value to it my report shows a blank like is there a way of putting in a zero instead.
Imran
The field sounds as though it is suppressing 0.00.
Select the field and the select Number format from the properties pain. There is a tick box to Suppress zeros.
Ctayts
Hi again,
Have a question regarding B.I. I have already set up reports within in the nominal ledger –> utilities –> financial layouts. I want these report layouts to be incorporated into the B.I. Module but when I set this up in sage admin and then add to the menu via tools — menu item but it’s not exporting to excel.
Hi Imran
I am confused with what you are asking.
Is your query how to use the financial statements layouts/structures within BI? These should just be available if your cubes are updating.
Or is your query how to add the BI report to the menu? Would need to add as a Feature in Sys Admin, enable feature for your Role and then add to the Menu using Menu Design Mode.
Crafts
Hi Craig
I have just been catching up with all the questions and this one caught my eye.
I took it that Imran was wanting to know if there was a way to export a report from the nominal > financial statements layouts so that it can be used within BI.
Obviously this would save having to create a new report.
Is there any way of doing this?
Thanks
Bill
Hi Bill
BI already has access to the financial statement layouts.
Craig
Hi,
I am trying to amend a despatch note layout in report designer but I am getting the following message when I open it up (in report designer)….
Variable ‘Warehouse item’.Warehouse ID cannot be found in catalog
Variable ‘Warehouse”Name’ cannot be found in catalog
Please could you explain what this means and maybe what I need to do to rectify this
many thanks
Lesley
Hi Lesley
It sounds as though the layout that you are amending, has previously been extended to include the WarehouseItem table. However, the reason you are getting the message is becasue that table is no longer associated to the layout but the variables from it (WarehouseID, WarehouseName) remain. They may not be added as visable fields on the layout but could be in filters, expressions etc…
Which version of Sage 200 / Report Designer are you using? Either the layout will need to be amended to add the table back in or the variables found and deleted. If the Legacy Report Designer is being used then the extended catalog will be required.
regards
Ctayts
Hi Craig and all,
Anyone using Sage 200 mobile?
Its something I’ve looked at on and off over the last year with 50 & 200.
I have it configured and working ok, so can view standard and custom Workspace’s on the mobile etc.
But what I’d like to know is, is it possible to use the Workspace actions on the mobile
or is Sage 200 mobile still “Read-Only”
(Where as Sage 50 contains an extra menu for creating orders and quotes etc..)
Thanks,
Paullyie
Hi Paullyie
Currently you are only able to view the workspaces.
Ctayts
Hello,
I was just wandering if you would be able to advise me on how to attach a separate word document to an invoice spindle on sage 200?
Thank you.
Hi Bronwen
Just to confirm, are you sending an Invoice using Spindle Professional, and you wish to send a word document at the same time?
Is the word document a generic document for everyone who recieves an Invoice to see (such as a newsletter or standard price list update)?
This can be achieved by amending the Document Automation for Invoices. Within Spindle Tools, Select Document Automation, Double Click the Invoice Configuration, you will see an Operation(s) (such as Send To Clinet). Double Click, you will see each Distribution Method (i.e. Email). Select Email. Select File Attachments. Click Add and browse to a file.
It is possible to use a command on the Layout to attach a document (##ATTACH C:\Files\InvoiceAttachments\Newsletter.pdf##). You could use an if type expression with this to only send the attachment to Customers IF they meet a certain requirement.
You could also use ##Commands as part of the path to pick up files e.g. Picking up a file from a folder with a customers name (the folder name could be used as a ## command on the layout). (C:\Files\InvoiceAttachments\##COMPANY##\JoeBloggsLtd2013Pricelist.pdf – Where ##COMPANY specifies the customer Name from the layout and the Directory, located at ##COMPANY## in the path, is the name of the customer).
I hope this makes sense.
Ctayts
Is the spool directory stored local or on a network?
Does each user share all spools or does each user have only their own local spool.
The first thing that comes to mind is permissions, can you check that the user has full read/write access to the spool directory?
Hi and thanks for replying.
It’s a network spool directory on the Sage200 server with users having the ability to see own spool files only. All users within the Everyone group have full permissions to the folder.
Print Spooler = Printed / Unprinted?
We have an intermittent issue where a user will print a document (and they have, I have seen them do it!) and the status will change from ‘unprinted’ to ‘printed’, but when they click the refresh button, the same document will change status to ‘unprinted’ again?
This is causing multiple print outs and mass confusion with picking lists…please anyone help?
This long running problem has now been fixed by our BP – turns out there was an update to report designer and they decreased the available memory that was available to SQL – therefore allowing more available to the OS.
Not too sure which truly fixed it but really pleased with the outcome.
Oops – wrong problem – please ignore!
This was a fix to increase the time it takes to spool documents to the printer.
DECREASE – My lord, I’m having a bad morning – Admin / MOD – please can we have an edit post function!
Hi,
Please can you tell me if there is a report I can run in sage against a particular supplier which will tell me the quantity of items per day/week they have supplied over a certain period . I need to provide some information regarding a supplier who actually supplies us with several stock items and I need to provide details of the volume of each stock item they have supplied either per day or per week
hope you can help
many thanks
Lesley
Hi Lesley,
The closest report you will get is running the transaction history report in Stock Control
Stock Control >> Reports >> Transactions >> Transaction History report
on the criteria window
– enter the date range
– enter stock item (if required)
– also select transaction type = Purchase Order (for goods ordered in)
– then there is a field called “Source/destination” if you put the suppliers account number in here it should run only for that supplier
Hope this helps
paullyie
Hi Paullyie,
Thanks for the info, it was a great help…….I have managed to run the report and it works!!
many thanks again
Lesley
Thanks, following on from my last question, being novice is it detrimental to the database if I execute a query within Microsoft query once I have the tables I need ?
Regards
Imran
No as you are only querying the data that is in the database.
Hi,
Is it possible to use the odbc connection to extract a TB which I could format via a macro?
I guess the question I’m asking is what table would I need to look at?
Imran
You may as well use BI.
You could use the NLNominalAccount, NLAccountPeriodValue, NLAccountYearValue tables via ODBC. You may require some other NL tables too depending upon what you wish to achieve.
Regards Craig Taylor
Sent from IPhone
I was also meaning to ask you guys, If I was to learn crystal reports would I be in good sted with sage 200 report designer
Hi Imran
Crystal Reports is a different tool completely. To learn the Sage Report Designer I suggest asking your BP for some training.
The main thing with complex report writing is understanding the database and how joins can be made between tables etc.
But the pure functionality of each report writer is different.
Regards Craig Taylor
Sent from IPhone
It might sound stupid for you guys, but I have created a workspace and now I don’t know how to remove/delete the workspace – would you please help me?
Hi Fabio,
Hope Craig doesn’t mind me answering this 🙂
you don’t delete Workspaces through the program or workspace designer
When you create a new workspace or make changes to an existing one, an xml config file is saved on the Sage share on your server
\\server\sageshare\IM Content\Modifications\Workspaces
You just delete the xml file related to the name of your workspace and this deletes the actual workspace – you’ll need to log out and back into sage for the change to take effect on the menu
Hope this helps
Paul
Are you looking for a job Paul?
Regards Craig Taylor
Sent from IPhone
Not at the moment Craig but thanks for the offer… ha ha..
i’m also a Sage BP and just like sharing the knowledge on forums etc. and helping people out where possible.
thank you Paul! 😉
Thanks Paul that worked a treat many thanks…
I’m looking to put a few expressions for calculations such as gross margin including staff costs and percentages but can’t seem to get the data to do so as its grouped via subtotals…any suggestions…also thinking of producing a report for the income statement with cost centres is this doable ? Imran
Imran
With having your report its hard to say. You can include subtotals using the standard functionality of the financial statement layouts in Nominal ledger > utilities > ledger set up. You wouldn’t do this in the report designer.
What do you want percentages of as there is also standard functionality for this as well as standard report to accommodate.
Also the BI module would be a great option for you as you can insert and create calculations.
Regards Craig Taylor
Sent from IPhone
Hi,
I tried that filter it didnt do anything to the report, I than thought I could apply that to criteria and suppress printing on zeros did not work.
Imran
Sorry I couldn’t reply quicker. A filter is required, but it needs to be be based on the Expressions that are calculating the values for the report. (Row footer 10).
For eg: The Profit & Loss MTD YTD
Has an expression that calculates the MTD value for this Year if it shows Left or Right. YTD this year as well as this month and year to close in the previous year.
What I would do is to create an expression that adds each expression up, and create a report filter based on this being 0.00.
Ctayts
Regards Craig Taylor
Sent from IPhone
Hi,
I’ve tried it still doesn’t work, what could I possibly be doing wrong.
Regards
Imran
Hi,
I’ve tried suppressing the zero values by using the filter in the row footer 10 for the variables, take the expression > 0 although this works I now have a problem because my credit values are now not showing, I thought my best bet was to replace > with = but this has no bearing on the report.
Imran
try “” 0 (not equals to)
sorry characters are not appearing on the post you need to two brackets right after each other
hopefully they should appear this time as <>
Hi again,
It was in the nominal for the income statement and balance sheet. I don’t want lines to appear if there is no value for them?
Can you help?
Imran
Hi Imran,
You would need to edit the Balance Sheet report in the report designer and add an extra filter
Open the report designer
Open the balance sheet report, located in Reports >> Nominal
Then select the required report
Select data then filters and add the below to the filter
“NLNominalAccounts.Balance 0”
Hope this helps
Hi again,
I’ve set up the financial statements and within report designer I would like to suppress any lines with no values. Is this possible? I don’t want these lines showing when the report is printed.
Imran
Hi Imran
For Stataments there is an Sales Ledger Setting in SL > Utilities > Ledger Set Up > Ledger Settings
Where you can specify what is displayed on the Statement. It sounds as though yours is set to ‘All Transaction’, if you change to ‘Outstanding Transactions’ then this shoud stop the zero transaction from showing.
If it wasn’t for the Statements then you could supress the section by adding a Filter.
ctayts
Hi Ctayts,
Thank you for your help
I have been trying for a while now to make amendments in report designer but think I have some issues…
When I look at the help topic it says then when you open Report Designer up you should see a main desktop with menus, toolbars etc but I simply see Financials/commercials and project reports.
I also want to look at the criteria settings as I can’t produce retrosoective creditors reports and think I need to go in here to check how it is set up…..
many thanks
lesley
Hi Lesley
You need to select Financials/Commercials/Project Accounting which will then open the Report Designer. It only matters which of these you select if you are going to create a new report from scratch.
Retrospective is, admittedly, slightly dodgy in earlier versions. I wouldnt have thought opening the Report design will help unless previously amended.
Should just need to tick the Retrospective tick box, and enter the base date for ageing (ignore Analysis period).
ctayts
Hi Ctayts,
We have our Payroll 2013 disc and are ready to install it but I just wanted to check that it will work ok with Sage 200 2011 SP1 as that is what we currently run (our BP doesn’t give us service packs unless it specifically resolves an issue the customer has).
Is there anything that has to be done in Sage 200 before/after installing the new Payroll?
Thanks,
Chris.
Hi Chris
It should be fine, Sage 200 doesnt need any configuration at all, even if the Payroll Nominal Link is used.
Thanks
Ctayts
Sage 200 Costing Report or ODBC needed
currently Sage 200 report doesnt not roll sub-assemblies up correctly althrough you can see the correct one on screen when you do the report it doesnt take into consideration the lower levels of overheads and materials. So I need help on how to create a sql statement for a odbc or how to amend the report to show the correct overheads and materials
Thanks
Liam
Hi Liam
Are you saying that the Screen and Report show different figures?
ctayts
Hi when you cost an item it splits in up so show, Materials, Labour, MAchine. but on the report it donts show the split correctly and all the previsious costings are there.
Hi Ctayts,
We are running Sage 200 2011 SP5 and are constantly encountering Out of Memory unhandled exception errors, even when doing something simple like changing the Filter on the SOP order list!
The machines have 16Gb Ram – I know Sage is 32bit and can only access 3.2Gb. Even so this is ridiculous! Are you aware of any outstanding Sage bug reports connected to this?
Also on a completely unrelated note, there appears to be a HUGE bug in Sage 200 where by we can’t reprint Despatch Notes and Invoices from archived orders. (Well we can’t for sure, it’s just doesn’t ‘do’ anything) Our BP knows about it and continually blanks the situation!!! GRRRR
Are you aware of this?
Thanks
Marcus
Hi Marcus
I havent come accross any Out of Memory issues specific to SP5. But as you say the RAM is more than enough for the machines. Are messages consistent in terms of when/Where they appear?
The Desptach Note one isn’t a Bug. It is how the system works. Its because the DespatchReceipt table is cleared down for the Archived order(s). Your Business Partner would need to put this in as a request for change.
ctayts
hi
not sure how to post a new message, need some help with works orders, we manu a part under a works order, this is then sent to a supplier for some work to be carried out. then returned to us prior to being despatched
what is the best way to do this on sage
thanks
Hi Nadine
Thanks for your query.
You can set up Operations and specify them as Sub Contract operations. You are able to enter Supplier details such as Costings and Timings.
As part of the Works Order Process you are then able to raise a PO for the sub-contract work, despatch components and return them. This is done within the Works Order Module.
ctayts
Hi, I’m using Sage 200, ver 6.00.0057. When setting up debtors the only credit limiter I can see is where you set a credit limit. Is there anywhere you can set the number of days credit a debtor can get so when they exceed this they go automatically on stop.
Thank
Hi Robert
There isnt anything as standard. You do have the payment terms for each individual invoice. But to set a limit to the number of days they can be over a credit limit, would need to be bespoked.
ctayts
Hi Ctayts
Thanks for the reply. Not a great system then..
I would defend that Robert. Sage 200 doesnt do everything for everyone, but what it does do it does very well.
Your query isnt something that I have had requested before.
Its certainly worth you looking down the bespoke route. Contact your BP who should be able to help.
ctayts
Hi, I am using version 5.1 of sage 200 and it has been painfully slow today… really doesnt seem to want to do anything !!! and I have now just received the following message and hoped you could explain what it means …” your Sage 200 system has not been enabled. Run system Administrator to enter your enable string.”
many thanks
Lesley
Hi Lesley
Probably one of 2 things. Your licence needs renewing. Your Business Partner will need to be contacted regarding this. (Wouldnt impact on speed though).
Or you have lost your network connection or access to the Sage shared directory on the server. This could be related to speed problems earlier depending on the cause.
ctayts
You’re a legend…works a treat. Thanks
Seems to work thanks how do I add a list of users to that filter would it be separated by a or/and?
It would be OR
Hi,
Is there a possibility of showing me how? I can’t see the user and when I select appropriate table and select the variable for user, I get no data available prompt. When I filter by addresses I get the expression builder? Help?
Hi Imran
Try this post http://wp.me/p1CtiO-6w
You need to base the filter on POPOrderReturns.DocumentCreatedByUserID
i.e. POPOrderReturns.DocumentCreatedByUserID = 8
Then select Supress Print as TRUE on the Conditional Formatting Properties tab.
This will mean the field will not show when user 8 has entered the order.
Hi again,
Setting up a po with 2 delivery addresses and what I want to happen is when someone prints from one location there delivery address be selected and when the other office inputs a po there delivery address to be selected. How do I go about this, I thought I could set up warehouse locations for them and get the info from that data table that way on the po but we don’t use stock control module so no table info can be linked. Please help?
Hi Imran
The only thing I could suggest is that you add both addresses to the Layout using Text Boxes. You then filter as appropriate based on each User number. So you would need to enter a conditional format on each Text box.
The address would not show on the Purchase Order Delivery and Invoicing tab.
There is no way of linking a user to a specific address.
ctayts
Hi, I am trying to login and I am getting the following error: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
any tip?
many thanks.
Fabio
Hi Fabio
Do you get this message on all machines or just one? Do you get this if you try to login on the server?
ctayts
just on the server where I have installed the Sage 200. basically it does after I enter the credentials and the system need to load the menus.
Is there enough memory on the server?
yes, there is 16GB.
Hi Fabio
I presume you are logging on as Administrator? If not make sure that the user has full permissions.
It could be a .dll issue, where a .dll has failed to register. May be worth reinstalling as Administrator if this is the case.
ctayts
I’ve seen this on two other occasions myself
1 Is where there is low-disc-space on the server where the SQL db is stored
or
2 The user does not have permission to run the Sage application (a very restricted profile)
make sure there is read/write access to
C:\Program Files (x86)\Sage\Sage200 and C:\ProgramData\Sage\Sage200
Hi Paullyie, thank you for you advise. after change the permission of the folder c:\program files\sage 200 – the system is working properly.
Hi,
I am using Version 5.1 of sage 200 and need to amend the despatch note in sop but don’t know where i need to go to do this. I presume I go into report designer somewhere but am not sure how I attach the deisn i want to the sage programme as you can only have one choice , unlike invoice, credit notes and statement layouts where you can have a choice of several….
many thanks
Lesley
Hi Lesley
If you first go to File, Choose Layouts and select SOPDespetchNote. This will show you the layout that is currently being used (also where you select the layout you want to use but as you said above you can only have 1).
In Report Designer select the Layouts folder and then you should see the Layout that you are using, and so wish to amend.
ctayts
Hi,
Is there an easy way to pass on price increases from suppliers to customers? If I just had a report which identified the customers who are buying items from a certain preferred supplier this would be very helpful as I would know who to send letters to.
Many thanks
Hi Hannah
It really depends on how your pricebook is used and maybe your Product groups too.
Pricebands within Pricebook allow you to have different prices for particular groups of customer, but all Stock items are linked to all pricebands (so there if no way of differentiating by a Stock Item field such as Preffered Supplier).
If Discount are used then as standard all amendments would need to be manual.
If it is just Pricebands then these can be exported to excel, use a formula to amend the prices and then import back in. Easy if it is something like an x% uplift accross the board for a price band.
Youcould do is to create a report that shows you the customers that have bought an item (maybe for the last x number of months), and then find out the Preffered supplier for each item. Unless your product Groups happen to be the Supplier in which case you would have all the information available (BI would probably be your best bet for this sort of report.
This will give you a summary for the current day.
SELECT
SOPOrderReturn.DocumentTypeID, SOPOrderReturn.DocumentNo, SOPOrderReturn.DocumentDate, SOPOrderReturn.ExchangeRate, SOPOrderReturn.TotalNetValue,
SLCustomerAccount.CustomerAccountNumber, SLCustomerAccount.CustomerAccountName
FROM
SOPOrderReturn INNER JOIN SLCustomerAccount ON
SOPOrderReturn.CustomerID = SLCustomerAccount.SLCustomerAccountID
WHERE
DATEADD(dd, 0, DATEDIFF(dd, 0, SOPOrderReturn.DocumentDate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GetDate()))
ORDER BY
SOPOrderReturn.DocumentNo ASC
This will give you a total for the current day.
SELECT
SOPOrderReturn.DocumentDate, SUM(SOPOrderReturn.TotalNetValue) AS TotalNetValue
FROM
SOPOrderReturn INNER JOIN SLCustomerAccount ON
SOPOrderReturn.CustomerID = SLCustomerAccount.SLCustomerAccountID
WHERE
DATEADD(dd, 0, DATEDIFF(dd, 0, SOPOrderReturn.DocumentDate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GetDate()))
GROUP BY
SOPOrderReturn.DocumentDate
I can give you step by step instructions on how to bring a query into Excel and have the Excel document refresh the number when the spreadsheet opens, or on-demnd?
Andy,
A step-by-step will be most useful.
If you could be so kind and this works – i owe you a few virtual pints!
Many thanks,
Rich
Ok, I’ve managed to get the query running in Excel, but need to apply a filter on the data returned to exclude any temporary sales orders that start with ‘#’ symbols…any tips?
Hi Rich,
Sorry I didn’t get back to you yesterday – work was in the way. I did have the message flagged to follow up. Glad you got it working yourself.
The following should accomplish what you need.
Summary for day
SELECT
SOPOrderReturn.DocumentTypeID, SOPOrderReturn.DocumentNo, SOPOrderReturn.DocumentDate, SOPOrderReturn.ExchangeRate, SOPOrderReturn.TotalNetValue,
SLCustomerAccount.CustomerAccountNumber, SLCustomerAccount.CustomerAccountName
FROM
SOPOrderReturn INNER JOIN SLCustomerAccount ON
SOPOrderReturn.CustomerID = SLCustomerAccount.SLCustomerAccountID
WHERE
DATEADD(dd, 0, DATEDIFF(dd, 0, SOPOrderReturn.DocumentDate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GetDate())) AND
SOPOrderReturn.DocumentNo NOT LIKE ‘#%’
ORDER BY
SOPOrderReturn.DocumentNo ASC
Total for day
SELECT
SOPOrderReturn.DocumentDate, SUM(SOPOrderReturn.TotalNetValue) AS TotalNetValue
FROM
SOPOrderReturn INNER JOIN SLCustomerAccount ON
SOPOrderReturn.CustomerID = SLCustomerAccount.SLCustomerAccountID
WHERE
DATEADD(dd, 0, DATEDIFF(dd, 0, SOPOrderReturn.DocumentDate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GetDate())) AND
SOPOrderReturn.DocumentNo NOT LIKE ‘#%’
GROUP BY
SOPOrderReturn.DocumentDate
Sorry, but getting an error now with the new statement from MSQuery:
Incorect syntax near ‘`’.
Statement(s) could not be prepared.
It looks like this forum has replaced some of the characters in the code I pasted so when you’ve copied and pasted it it uses the incorrect syntax.
Find NOT LIKE ‘#%’ in the code and relpace the single quotation marks with apostrophe’s found on the @ key on a standard UK PC keyboard. You should then find it runs ok.
You may already know this but when you get it working you can set the data to automatically refresh when the file is opened and/or every X minutes.
To do so, depending on your version of Excel, select somewhere in your data, click the Data tab, click Properties (on this screen I always select Overwrite existing cells), click the icon next to the connection and in the following window you will see the options for refreshing the data.
I don’t personally use the auto refresh to avoid unecessary load on the system, not that this on its own would cause much load, but if you had lots of reports like this with large datasets querying the database every 1 minute it could noticeably slow down the system for everyone.
Quick question: Is there an easy way of seeing the total value of sales orders on the system for the current day?
I need to produce a way of displaying this on a screen that automatically refreshes the value.
Any help would be much appreciated – thanks in advance.
Rich
Hi Rich
To be honest your best and cheapest option is to run a Report. It wouldn’t be an automatic refresh on a screen but if you amend the criteria on the Sales Orders report to default to Today, it will not be that much slower than having to open a screen.
If you specifically wanted a screen you would need some bespoke work. One option would be to have a workspace. There is a standard one call ‘My Sales Summary’ which shows MTD and YTD. This could be amended so that the Data Source of the My Sales Summary Content Part included the SUM of todays orders. You would need a programmer that can code in LINQ to do this.
Another option would be to create an Excel Spreadsheet that reads the data using an ODBC query. You could keep this open and use a Macro to refresh as required.
Thanks
ctayts
Hi Craig,
Thanks for the quick reply.
The second option is exactly what I want to achieve as it’s pretty easy to then create a self refreshing .htm file from the spreadsheet. I did this with our Line100 but have to admit to being a bit lost with the query in Sage200 though – there’s a lot more tables & fields now!
So far I’ve managed to connect via ODBC and the query looks like this:
SELECT
SOPOrderReturn.”DocumentTypeID”, SOPOrderReturn.”DocumentNo”, SOPOrderReturn.”DocumentDate”, SOPOrderReturn.”ExchangeRate”, SOPOrderReturn.”TotalNetValue”,
SLCustomerAccount.”CustomerAccountNumber”, SLCustomerAccount.”CustomerAccountName”
FROM
{ oj “OUR_LIVE_COMPANY”.”dbo”.”SOPOrderReturn” SOPOrderReturn INNER JOIN “OUR_LIVE_COMPANY”.”dbo”.”SLCustomerAccount” SLCustomerAccount ON
SOPOrderReturn.”CustomerID” = SLCustomerAccount.”SLCustomerAccountID”}
WHERE
SOPOrderReturn.”DocumentDate” >= ? AND
SOPOrderReturn.”DocumentDate” ‘0000200000’
ORDER BY
SOPOrderReturn.”DocumentNo” ASC
But I still can’t get it to run properly and give me the correct figure.
We can get the figure to display in a Crystal report, but need the Excel spreadsheet to create the .htm file to edit and put the script in to self refresh…
Any tips would be much appreciated!
Many thanks,
Rich
Is this bit correct?
SOPOrderReturn.”DocumentDate” ’0000200000′
Hi Craig
I want to use a Trial Balance report in Excel and need Sage to generate this including accounts with zero balances so I can drop into a standardised spreadsheet with fixed links.
Hope you can help.
Charles
Hi Charles
The easiest option is to use the Trial Balance Analysis within Business Intelligence (BI).
Otherwise you would need to amend a Report Designer Trial Balance report to remove the filters that prevent the 0.00 from displaying. You may also wish to amend the Excel Options strip out the Report and Page headers and footers.
ctayts
For various reasons loading the BI unit is still on the “to do” list!
Many thanks.
Hi,
I actually can’t see a way of adding tables in the join editor to Sal_Free_TXT. I am very new to this but can’t find anything in the report designer user manual.
Regards
Imran
Hi Paul,
I can’t see the option to add a table to Sal_Free_Txt. I make the join from syscompanies and keep on adding tables until I find the one with sales ledger customer contacts but can’t seem to link anything to Sal_Free_Txt.
Regards
Imran
Imran,
Rather than linking to the view, I would try adding the contact tables instead.
The object reference message tends to mean it is expecting something to be there that isn’t.
Are you amending the joine types at all?
I am not sure why you have linked tables to syscompanies as this is relevant to your company info.
Ctayts
I’m now getting to error messages. When I’ve made the link “Object reference not set to an instance of am object” and “no property or field ‘A2_CompanyName’ exists in type DynamicClass2′.
Someone please help?
Hi, it sounds like either bad joins or wrong data source
I have it working ok, but I can’t attached a sample or the report here
Here is a screen shot of what the joins should look like and the filter to be applied
Hi Paul,
Thanks for the quick response, I’ve tried to link these tables with no results. I’ve never linked a table so if you could give me the steps to follow would be great as I’m having a nightmare.
Regards
Imran
I’m still getting the same error message regarding dynamic class?
I’ve managed to do it. Thanks, I will follow up if I get stuck.
Hi,
It’s a free text invoice layout. The tables I’m trying to join are SAl_FREE_TXT_AFD_5031 and SLCustomerComtactDefaulyVws. The default table with this layout is the former. I’m trying to add contact details to each invoice as its not in the former table. I add the variable contact from SLCustomerContactDefaultVws. When I go to preview the error I get is No property of field ‘A3_CompanyName’ exists in type ‘DynamicClass1’. I’m hoping you can help?
Regards
Imran
Hi Imran,
The problem is that “SAl_FREE_TXT_AFD_5031” does not contain a unique customerID so your only possible option is also to add SLCustomerAccount table and join on customer account number
SAl_FREE_TXT_AFD_5031 Join to –>> SLCustomerAccount on customer account number
then SLCustomerAccount Join to –>> SLCustomerContactDefaultvws on SLCustomerAccountiD
Another item to be aware of is when you start joining contact tables you will start running into duplicate records due to the different roles
So in your layout you will need to filter where the contact where IsDefaultRole = True
This should work ok
Hope this helps
Paul
Hi,
I’m a fairly new user to sage and I’m trying to set up some layouts for sales invoices, orders etc.
I’ve selected a free text invoice in the sales ledger and working on this on my lay out. I want to add a sales ledger contact and telephone to this layout but its in another table. When I go into the join editor and I add the table to my variables list everything is fine. However when I add the variables to my layout it doesn’t like it. What am I doing wrong, both tables are within the sales ledger so I don’t understand why it’s not working? Are there certain tables that won’t link?
Hi Imran,
What message are you getting?
Tables can be joined as long as there is a unique identifier that you can join the tables by.
Which tables have you joined, and how are they joined?
Thanks
Ctayts
Hi there,
I have just found your site whilst googling a Sage problem that I am intermittently having…
10 workstations, running WinXP SP 3 and Sage 200 6.00.0057
1 Server running Windows SBS 2008
The problem has been experienced by several users, but not every time – when trying to start sage on a workstation, the error “Directory Path for Specified Logon does not exist”. Usually, a reboot of the workstation fixes this, but it is annoying to have to keep doing this whe it occurs. In all cases, when checking the server, the \Logon and \Spool directories are shared and available across the network.
Any ideas or suggestions appreciated.
Hi John, this sounds more network related. Do they have full network rights to the Sage server? Do they have to login to connect to the sage share?
Hi there
I was hoping you could help with a Sage 200 BOM query. For some reason, stock items built through BOM are showing up as unconfirmed. To build the stock item I select the item in BOM, do a trial kitting, select create works order, allocate issue and complete the works order and then 1 is added to stock. However on the stock valuation report this is showing up as an unconfirmed item. Any idea what I am missing?
Thanks
Guy
Hi Guy,
Thanks for your query. Within WOP you also need to Close the WO, after completing it. The Close updates the other ledgers/modules.
Hope this helps,
Ctayts
Has anyone experienced the report designer talking a very long time to load – 5-10 mins on sage 200 v 2011 (it maybe since the new RD). We have raised it with Sage support and they say it is becuase the RD scans the C drive, I can’t believe this is the cause and our c drive is less than 50GB any way. This does not happen on all systems but quite a lot of them. Any similar problems or ideas.
Thans
Tom
Hi Tom
I havent experienced this with the New Report Designer. It always used to be a common problem with the old one.
ctayts
Do you have mixed reports?
(i.e. are you running some reports on the old Report engine and some on the new engine?)
Hi there, Just wondering if there is any way to amend a posted journal narrative, I have posted a large journal and just want to correct part of the description if possible without have to reverse and repost. Thanks
No this isn’t possible. If it is a large journal which you would not rather reverse and re-enter then you could look at a script to update it.
Ctayts
Hi Craig, I read your post re Adding Sub Reports to a Report with interest having recently had a request from an end user. It was great until I got to the part about linking the 2 reports together where it was perhaps not quite so clear. However, with some perseverance and Sage KB Article 27254 (which is actually for Sage 50 Manufacturing but had some graphics which further helped), I got my report working as I had envisaged it.
The user wanted a report of Converted Quotations to SOP Orders. As you are no doubt aware, there are no natural links in the db as this is all part of SOP Orders. Therefore, the Rep Hdr had a filter SOPOrderDocumentID = 3 (the original SOP Quote) and SourceDocumentNo starts with “0000” (the converted order) and the Detail had my SR containg the detail lines for the original quote.
I then started to develop the idea and thought that as well as the original quote details, it would be good to create a 2nd SR to give the customer the associated SOP Invoice/s and Lines, grouping on the SOPDocumentNo. Finally got the links working (or so I believed), which on Sage DemoData with some 5100 SOP Orders, produced the first 2 pages almost immediately and then progressed I assume, to trawl through the remaining orders to my most recent converted quote.
However, when you scroll through the final page past the said order, it brings up what I believe is a Windows Error message saying the program has stopped working, check online for a solution, Close, Debug etc. Problem details show APPCRASH on SageReportDesigner.exe Kernelbase.dll
So, the question before I call Support, after all this was a nice to rather than need to, is have you used multiple S/R’s with any success and is the system capable of producing them or is this a bug? It could be of course that I have my linking all wrong but I would be interested to hear if you have experienced any of this.
I am using a VMWare Image of W2008 Server & Sql 2008, 200/2011 Build 8/0013 i.e SP2
Thank you in anticipation
Hi Graham
I havent tried adding multiple Sub reports. and havent come across that error either.
I would prehaps try running it for a smaller range. maybe just the order that therror is showing on.
Craig
Craig,
An interesting article on adding Subreports although I must confess I got somewhat lost on linking the main and subreports. However with perseverance and the graphics in Sage KB Article 27254 (which is actually for Sage 50 Manuafacturing), I managed to get mine working.
This was in reponse to an end user asking for a report on Converted SOP Quotations. So the main report was SOPOrdersReturns, details line filtering on Quotes (SO Type 3) and SourceDocumentNo begins with “0000” as there are no natural links in the db. So my main report was converted Quotations, my subreport is the detail lines. Having thought about it since, yes I could have achieved this anyway without an SR but I wanted to “play”.
I then got to thinking, wouldn’t it be nice to give the customer a report with details of the original quote and then a second SR of the resulting invoice/s! Have you ever successfully used a 2nd SR?
On my 200/2011 Build 0013 VMWare Image with Server & Sql 2008 using DemoData with SO Nos down to the 5100 range, it produces the first 2 pages almost immediately and then trawls through all the orders, gets to the final page 3 where mine is, actually brings it up on the page but as you scroll down, then shows I believe, a Windows error message (Check online for a solution etc, close/debug) SageReportDesigner.exe Fault Module Kernelbase.dll
So, it may be that my linking is wrong or that RepDes can’t handle 2 SR’s but I would be interested to hear your thoughts before calling support.
Hi Ctayts
I am using sage 200 V 5 and it has had some kind of meltdown today on raising purchase orders as it has suddenly jumped number sequence by approx 7100 numbers !!! There is obviously a glitch somewhere but i’m not sure even where to begin and don’t know if there is anything that i need to do ? …… Is there a report that I need to run ? Is it likely that whatever the issue is will have affected other parts of the programme ? I could really do with your help
many thanks
Lesley
Hi Lesley
I have seen this before, but in v2009. The way it happened was down to the following steps:
1. Double click PO from POP List.
2. Receive a message saying the order is complete and can not be amended.
3. Click ok.
4. The amend POP form remains. Close this using the x in the top right corner-This step is crucial to recreate.
5. Click Yes at prompt ‘This order has not been saved, do you want to complete it now’
6. OK the Purchase Order Confirmation screen.
The program goes into a loop and is incrementing the order number the whole time. You have to crash out, using Ctrl Alt Del, to stop the loop.
Basically to stop this occurring don’t close the form at step 4 using the x in the corner, use the close button instead.
This was a bug that Sage fixed in 2009 SP3.
ctayts
Thanks for that…. your help is very much appreciated
Lesley
Hi
Question about credit limit?
Sage 200 2010.
We have got a credit limit on sales ledger, on SL Invoice entry shows available credit.
Which is Credit limit less account balance.
Is it the same principle applied when entering sales orders, as i get the over credit limit message
but have available credit on the account. Does it take into account all outstanding sales orders.
Many Thanks
Jonathan
Hi Jonathan
When entering Sales orders the system does take into account any outstanding sales orders.
Outstanding Sales Orders are only taken into account in the SOP module.
Thanks
ctayts
Hi Can you tell me a way to link PLPostedSupplierTranID table with POPOrderReturnLineID as i need ItemCode and ItemDescription from POPOrderReturnLineID on my report.
Hi Kevin
This isn’t an easy join to make as the Transaction in the PL is header/footer based. Therefore what if the Invoice/Order relate to multiple items?
ctayts
Hi,
We are having a problem in formatting a report to perform calculations based on the results of previous calculations carried out on a different line at the same report level. For example, a Job
Sheet report might generate the 4 items from a BOM, e.g.
Item1 – Dry Weight = 160
Item2 – Wet Weight = 100, Dry Weight = 35
Item3 – Wet Weight = 75, Dry Weight = 30
Item4 – Wet Weight = 120, Dry Weight = 40
We want to perform calculations on those as follows:
Calculation 1 = Item1 Dry Weight + Item2 Wet Weight (i.e. 160 + 100 = 260)
Calculation 2 = Item1 Dry Weight + Item2 Dry Weight + Item3 Wet Weight (i.e. 160 + 35 + 75 = 270)
Calculation 3 = Item1 Dry Weight + Item2 Dry Weight + Item3 Dry Weight + Item4 Wet Weight (i.e. 160 + 35 + 30 + 120 = 350)
We can’t get the calculations to work across each item as they are at the same report level. Should we be looking to use a series of sub-reports to pick out each BOM sequence or do you have any other advice that might help? Any help would be appreciated.
Thanks
Ian
Hi Ian
As you have found this is going to be difficult as the Group level is the same.
I think your best bet is sub reports, and filter these to only contain particular lines. You will need to make sure that the lines are always coming out in the same order though or fiter to something other than sequence.
ctayts
Thanks – I may be back asking for more help when we give it a go!
Ian
No worries, have you read my post about Sub Reports? Here is the link:
https://talkingsage200.wordpress.com/2012/01/08/creating-sub-reports-using-the-sage-200-report-designer/
Hi,
We are running a new install of Sage 200 2010 7.00.0042 and would like to import journal entries as a csv.
I have done this before in Sage 200 but cannot remember the headings required in the CSV file.
A gentle jog of my memory would be greatly appreciated.
Thanks
Sian
Hi Sian
Please find below:
AccountCostCentre
AccountDepartment
AccountNumber
TransactionType
TransactionDate
GoodsAmount
Reference
Narrative
UniqueReferenceNumber
UserNumber
Source
PostedDate
TransactionAnalysisCode
ctayts
Hello,
Love the website, very useful. I am a new Sage 200 user, and I am trying to display a project code (PCProject.Code), on a POP purchase order which is linked to a project.
Do you know a way of doing this?
Any help would be appreciated.
Kind regards,
Daz.
Hi Daz
You would need to use the Joins Editor to add the Project tables to the Layout. It may even be better to have a View created with the Required details and link Join that in.
ctayts
Hello ctayts,
Thanks very much for the info. Is the joins editor something I should be able to access, or will I have to go back to my Sage 200 provider?
Your help is very much appreciated.
Kind regards,
Daz.
Hi ctayts,
I’ve managed to join POPOrderReturnLines to PCProjectEntryVws and have the information I need in the layout screen.
Thanks for your help, it was much appreciated.
Kind regards,
Daz.
No worries, sorry I didnt get back to you about if you have the Joins editor, but I guess you found it!!!
ctayts
Hi
I would like to create a report in Sage using the parameters of an existing report. I know how to do this by going into the Report Designer.
My question is can I put a link in the module, say Purchase Order Processing, so I can run the report from there rather than going into report designer.
Regards
Robert
Great question Robert, I have added a blog abot this including a document of the steps required.
https://talkingsage200.wordpress.com/2012/10/09/adding-reports-to-menus/
ctayts
Hi Ctayts,
When we migrated our data over from Sage 50 to Sage 200, all the purchase orders that were “complete” on Sage 50 are “Live” on Sage 200. I later found out this was because our accounts person was not recording the purchase invoices against the PO’s on Sage 50.
My MD wants the old purchase orders clearing so they don’t all stay “Live”. Is there an easy way for me to clear off or complete PO’s before a certain date? Or is there any other way I can remove them? I’m not sure if recording PO invoices against these old orders now will mess up the financials.
Thanks,
Chris.
Hi Chris
There is a delete Incomplete orders routine within POP.
The affect of this depends upon the actual postion of the order in terms of part receipt/invoice and so on, but for what you say above, it should just complete the order.
I would advise you test this in a copy of your data to check the full affects. The Help guide will give a fuller explanation also.
ctayts
Thanks, this should do exactly what I need.
Hi Ctayts,
(You’ve got mail).
Thanks for the help, expression is working great!
Marcus
Hi,
I wrote off three invoices as badt debt at the end of our financial year and my MD would now like me to reinstate them. I don’t want to put them on as new invoices if possible but was hoping that there was a way within sage that would allow me to do this…. do you know of any? Or will I have to journal them back onto the system with the start of this financial years date ( as now we have done year end sage will not let you post anything prior )
many thanks
Lesley
Hi Lesley
The write off will have just entered a Credit Note and allocated this to the invoice.
You can therefore unallocate using Amend Allocations and Reverse the Credit Note using Reverse transaction.
The reversal will create another invoice and allocate to the credit note, this will use the same date. You may therefore need to amend the date validation settings to allow the use of a date that falls in last year.
From an accounting point of view, as you have done your year end all reversal transactions will update the current accounting period. Therefore you may want to journal the accounting entries out of this year, and then post a prior year journal to show the entries in the last financial year.
ctayts
Hi, we have an account on sage that has hundreds of different delivery addresses is there a report (or a way of using excel) that could give us what each delivery address orders over a set time frame?
Hi Ruth
There isnt anything as standard which would already show this information. But reports such as the Sales Orders (Detailled) and (Summary) can easily be amended to include a Delivery adress grouping (after the Customer ID grouping).
ctayts
Hi, we have a in-house sage 200 solution running on sbs 2003 on a decently powered (all-be-it old poweredge 1900)
Found that Business Intelligence finance wont run on a domain controller, so looking at a very costly upgrade to Windows 2008 Server standard.
We are moving to google apps or office 365 soon, so wont need our exchange etc running on the sbs soon, have two queries …
1) We get an new entry level dell server with Windows 2008 Foundation, but have no idea if this will run our Sage 200 with business intelligence?
2) Remove the primary domain controller function from the SBS and turn it into a workgroup server and install business intelligence on it, is this supported?
Thanks
Harry
What version of Sage 200 are using, as the Domian Controller is no longer a problem from version 2010 onwards.
Sage 200 is only supported on Windows Server 2008 Standard or Enterprise.
ctayts
sorry for delay, thought i’d ticked notify me of update … its version 2010, its ok on the sbs its the business imntelligence addon which is not supported when its running on a domain controller
Hi Harry
Sage 200 is also only compatible with Office 2003/7/10
just read somewhere that the cloud version of sage is fully compatible with office 365, expensive monthly apparently but for four users might be worth fully migrating from sbs/local sage, i think we qualify for free months for our existing licences.
Devil will be in the detail i suppose, especially when it comes to support ..
Hi Harry
Domain Controller is not an issue for 2010 (INCLUDING BI). There is an amendment to the Deployment and Installation guide dated 24/08/2010.
As for the cloud version, this has not been released yet. There are some hand held sites (sage would be happy of more also) at the moment. Whispers I hear is that the aim is for March/April 2013 which will probably mean nearer June.
ctayts
thanks just downloaded the latest guide
it must be related to the version of sql server were running on our sbs box
??
Hi Ctayts,
When an order gets put on hold, am I right in thinking that the only place in the database that this is reflected is in the SOPOrderReturn table (the DocumentStatusID field)?
The reason I ask is that we get a lot of orders on hold and the sales guys are supposed to chase the customer for payment before we take it off hold. I have created a page on our intranet with a list of the orders on hold (pulled from the Sage database) and a textbox stating what they are doing about the order (this part is kept in a separate database). Either myself or someone in the logistics office will then release the order so they can print the acknowledgement.
What I want to do is have the order come off hold when they add that note to the order. Creating that feature is no problem but I want to make sure I won’t be screwing up the Sage database (as a rule I generally don’t like to run UPDATE or INSERT statements against an external applications’ database unless I know it inside out). As far as I can tell the only place the order status is changed is in the SOPOrderReturn table. Would you envisage any problems doing this?
Thanks,
Chris.
Hi Chris
The SOPOrderReturn table is the only place.
I do not invisage a problem with the above, however you are on dodgy ground if you are writing anything to UPDATE the Sage database without a Developers licence (not sure if you have one). Although it may be a basic update you are making you could make your system unsupportable and I have to advise you discuss with your BP first.
Thanks
ctayts
I don’t have a developer license (bit too expensive for the in-house applications I write for pulling data from Sage).
I will mention it to our BP, thanks for the info.
Hi Craig
Another question for you.
Is there a way of automatically generating an email notification when orders are received? I had a look at the help and it seems the automatic notifications are only for people authorising the PO’s.
We would really like to have the notifications automatically based on the Project Engineer responsible for the job the goods/services are rather than creating a new notification each time a PO is raised but that may be a step too far.
Thanks
Bill
Hi Bill,
You would need bespoke to have an email notification to say that the Goods Received has been entered for a Purchase Order.
You are correct that the standard email notifications work with the Purchase Order authorisation.
Presumably the purpose is to tell the relevent person that there items have been received and so are available for them to use?
ctayts
Hi Craig
Thanks for getting back to me.
You are right that it would be to tell the relevant Engineer in charge of the job that the goods had arrived so they could then ensure the work went ahead.
Do you have any idea how much a bespoke for this would cost?
Bill
Hi Bill,
I am not a developer so I couldnt really. The BP I work for has there own Sage 200 development team.
Your BP prehaps does development work or someone they sub-contract to?
I am not aware of there being any addon that is in the market already for this.
ctayts
Thanks again Craig.
Unfortunately our BP isn’t very good with the things that Sage is meant to do and so will just have to try and find another way round the problem!
Bill
Ok….well there are plenty about if you need to change.
ctayts
I am trying to get it changed!
And I think that this site is a very good advert for your firm – have mentioned it to the boss a few times
I am glad you find the site helpful. I do it independently from the company I work for (TSG) so it isnt a direct Sales/Marketing source. But if you do want some information there is a link to the website on my homepage, or when ready feel free to email me and I will happily get somone to contact you.
ctayts
I didn’t think it was direct advertising but I just feel it highlights to me that people at your firm know what they are talking about.
I will be in touch when/if I can.
Bill
That would be great.
Thanks Bill
ctayts
I’m a Sage 200 manufaturing user and we have come across a very frustrating bug. Do you know if there is a work around?
When we pick stock for a sales order it is selecting based on location (alphabetical) and then by batch (date). The problem is that one product may appear in several locations or racks. This has left us with stock that is out of date due to it’s location.
Hi Andrew
Within the Stock Control > Stock Order Fulfilment you are able to select your prefered allocation priority.
You can choose from Bin Priority, Smallest Bin Qty, Largest Bin Qty, Oldest Sell By Date, Oldest Use By Date or FIFO (Oldest Date Received).
NB. The default is Bin Priority, which if these are all set as the same (i.e. not amended) then it will be based on the alphanumeric order of the Bin name.
Hope this helps
ctayts
Thanks, that has certainly got us going in the right direction.
Please help. I’m trying to set up our invoices so that we can automatically email batches as we do with our statements. I have gone through your “Email Setup” instructions and checked everything I had tried to do but I still get the error message:
Cannot find the report file: SOP Invoice (E-mail).layout
I set it up in my “maintain invoice layouts” as description: “SOP Invoice (E-mail)” and layout “SOP Invoice2” .
When I go into Choose Layouts I can select SOP Invoice2 in the Report Key drop down and the file name is: SOP Invoice (E-mail).layout
but I’ve obviously missed something and your help would be brilliant!
Thanks,
Hannah
Hi Hannah,
At what point do you get this message? It suggests the layout doesnt exist where it is expecting.
You shouldnt really need to go to File > Choose layouts as the Maintain Layouts in SOP should be enough.
I would re-select the layout via SOP > Maintain Layouts and see if this works.
ctayts
Hi,
Thank you ever so much for coming back to me. I was actually testing my new invoice layout in the “Test Company” and so since your email I tried printing a standard paper invoice as we normally do in the live company and this didn’t work in the Test company either.
So I decided to be brave and just tried to use the new email invoice in our live company. The good news is that it did produce the invoice and picked up the correct email layout but the bad news is that it just went to the spooler and not to my email inbox as the statements do.
I have attached a print screen of the email options section from the invoice layout in case you are able to spot anything wrong on that. It seems to be the same as it is for the statements which will go to my email inbox fine.
Thanks again,
Hannah
Hi Hannah
With documents such as an Invoice layout it will be spooled or Previewed first (relevent to your out put mode) but from here you are now able to select the email button and it will use your email configurations as set up on the layout.
Statements and Remittance Advices are the only documents that have a programmed Email function.
If you look at my document about how to Email Cocuments (think you have this) page 7 mentions that you will still need to Spool or Preview first.
Hope this makes sense.
ctayts
Hi,
Thank you again for your quick replies. I’ve read through your instructions properly this time and think I understand – because I used the “Group 5 – SOPInvoiceCredits.SOPInvoiceCreditID” I should be able to click on email from the spooler and they will go into my inbox to be sent (I have selected the “save emails to inbox” choice on the email options).
Unfortunately however, although I get a message to say “The report was successfully output to e-mail”, nothing is appearing in my in box. Any ideas?
Hannah
Have you configured the Email Setup options within Tools > Options?
Are you able to send a Test email from here?
The test email seems to work fine for both the MAPI and Outlook 2010 options (but doesn’t for SMTP). The test message reads “If you can read this message, your Sage Report Designer email settings are configured correctly.” But even though when I click “email” from the spooler and get the message “The report was successfully output to e-mail.” there isn’t anything in my inbox or sent box and it doesn’t get to the intended person.
Hannah
Its difficult without having access to your system.
It sounds as though the SMTP settings need configuring and that the Document Email output needs to be SMTP.
Alternatively, is the document set to use SMTP? If so make this MAPI or Outlook 2010 instead.
Have you got a BP who can look at the configuration?
ctayts
Hi
I am relatively new to using Sage 200 – I have only been using it a couple of months but I am keen to try and get it working the way I want it to, unfortunately I work for a relatively small company so I’m on my own when it comes to finding out how things work. It’s been a huge boost for me finding this site as it has answered a lot of my questions and saved me a lot time.
Unfortunately I have a problem that I can’t find an answer for.
I am just testing out importing journals from excel (via csv) when I have posted them and then go into the posting and drill down into the transaction I get a message saying “The set of nominal transactions is incomplete” and it won’t show me the full journal details.
The journal posts without any problem – the other side of the journal is through the ledger but I am not happy with this as it can be very useful to see the full journal when you go back to look at it later.
Am I doing something wrong and if so how can I fix it?
Thanks
Bill
Hi Bill
I would guess it is because you are not entering a Unique Reference Number (URN) on the import file.
The drilldown can only show transactions that have the same URN.
If a URN is not specified on the import file, each line gets a unique URN, this is why you are getting the message.
Unfortunately it doesnt look at the other fields such as ‘reference’ and then realise to put the same URN.
If it isnt the URN, then it could happen if dates are different, and some transactions are in history. This is unlikely in your scenario as you have just imported them.
Hope this helps.
ctayts
Hi Craig
You are spot on! That’s a big help, thanks.
Bill
Hi,
I want to add my own custom criteria to a report. I need the option to to be able to filter the report at run time to exclude rows where the WarehouseItems.QuantityFree < WarehouseItems.MinimumLevel.
I can't see where I can add my own custom criteria and wondered if Sage have not allowed this type of functionality.
Is this acheivable?
Thanks
Andy
Sorry, I should have said …..ONLY INCLUDE rows where the WarehouseItems.QuantityFree < WarehouseItems.MinimumLevel
Hi Andy
I posted quite a few articles on report designer so hopefully you will find some useful tips in Modules > Documents/Report Designer.
Here is one about Configuring Criteria:
https://talkingsage200.wordpress.com/2012/01/15/configuring-criteria-for-reports/
Hope it helps
ctayts
Hi Ctayts,
Thanks for responding so quickly.
Yes there is bespoke work and we are using version 5.1
The bespoke has been updated but I installed the new zip file and it still doesnt work
I would be grateful for any suggestions
thanks
Lesley
Hi Lesley
It will be related to the bespoke so you would need to speak to whoever developed it.
It does sound like install issue, maybe a file is missing or installed to the incorrect place on the problem machine. I am guessing a .dll file or files should be in Program Files > Sage > Sage 200.
Ctayts
Hi Ctayts
One of the guys is having difficuly in raising a sales order and keeps getting an Error in Script Message. The strange thing is that when he logs on to another pc (as himself) he is able to raise them ok. I’m not really sure if this is a sage issue or a pc issue and was hoping that you may be able to point me in the right direction
many thanks
Lesley
Hi Lesley,
What version is this for?
It suggests that there is bespoke on your system, which isnt installed on your colleagues machine.
However from about 2009 to present (2011) this shouldnt occur if the bespoke has been installed as an SDBX file via System administration.
ctayts
Hi Ctayts
Another report designer question!
On the despatch note, I would like to use the conditional filter to hide a field if an Analysis Code within the SO has been set.
So in my sudo code ………. IF Analysis code 1 = “Plain Label” THEN HIDE THIS FIELD
Hope that makes sense!
If been struggling to find how to refer to the SO’s Analysis Code, is this possible?
Thanks
Marcus
Hi Marcus
On the Conditional Formatting of the field you want to hide you need the following filter:
SOPOrderReturns.AnalysisCode1 = "Plain Label"
Then in the properties (of the Conditional Formatting) set Supress Printing to TRUE
This should work
Ctayts
Nominal Code Access Restriction
Is it possible to restrict access to particular nominal codes by user?
For Example : I want Fred to have the ability to post invoices but only to a specific list of nominal codes, Fred can also enter Journals but again only to a restricted set of codes.
The real reason for doing this is that I have a tool to select nominal codes from Sage and then the tool updates an invoice batch for importing but I only want the user to select their own codes. I suppose this could be done at a higher level of Cost Centre or Department if I have to. I just can’t find anywhere that says user Fred is restricted to Only Cost Centre “A” or Department “B” or Nominal codes 123 and 124. The only restriction I can see is the company code.
I was trying to avoid having to create a separate table and something to update it.
Thanks
Hi Steve,
I have come across this query before bur it would require bespoke. There is nothing as standard.
I am not sure if there is an add on already available.
Ctayts
Hi Ctayts,
On the Sage Developer blog there is a link to a Sage lab where they created context-sensitive buttons in custom workspaces (I can’t view the lab without a dev login so don’t really know how they do it). I was wondering if this is possible just using the built-in Sage 200 workspace tools and not a developer SDK?
I’ve created quite a few workspaces now but the limiting factor in making them the default go-to place for our users is the fact they don’t have access to the toolbar buttons (allocating and despatching come to mind). They like to be able to highlight orders and have the buttons recognise the highlighted order but the only thing I can do for them is add those features to their Favourites but obviously they then have no relation to what is going on in the lists.
I don’t even see a way of assigning form actions to some of my created workspaces. It seems Sage 200 decides what form actions are available depending on the data source of the content part.
Thanks,
Chris.
Hi Chris
You would need the the SDK to create the buttons I am afraid.
The Actions within the workspace should recognise the details of the line that is highlighted, i.e. select an order, choose View Order Action and it should open the selected order.
The Actions available to a Content part are based on the Data Source for the Contect Part. If you select to Edit a Content Part using the Content Part Designer that is in the Workspace Designer, you are able to Select and De-select the Actions that are available to that Content Part.
I hope that makes sense:
For an existing workspace you have created Open a Workspace in Workspace designer, Select Edit Content Part (against the ContentPanel you want to amend Actions in). Then on the Right side it will List the Actions and Reports that are available to the selected Content Part.
ctayts
Hi Craig
Many thanks for getting back to me.
That document sounds very useful I would really appreciate a copy – as also the LinkedIn suggestion.
We would prefer not to report directly against the sage data as we also need to bring in data from outside sage so ideally we were hoping to use our external source and the Sage BI tables to develop a DataMart.
Kind regards, Sandi
Hi Craig
We have Sage 200 (v7 2010) which currently services multiple companies within the Business and we are looking to implement Sage 200 BI (initially for evaluation). We use BusinessObjects for most of our BI reporting as we combine Sage data with other data sources for our reports so we are very interested in linking to the SAGE BI schema rather than writing our own ETL . Do you know if there is a document around which describes the entities/attributes/relationships (or Tables/Columns/Joins) of the Sage BI schema?
I have tried googling to no avail – except that it did bring me to your very informative site 🙂
Cheers,
Sandi
Hi Sandi
To my knowledge there is no documentation specifically around the BI schema. However, if using a different BI tool would you not be able to link directly to the Sage 200 schema? If so there is a document called “Understanding the sage 200 databse” (although not sure how up to date it is) which I can send if you like.
If you are looking specifically at the Sage 200 BI module then there is a good group on ‘Linked In’ called ‘Sage 200 Business Intelligence Practitioners’.
There are a number of developers on here who will have a better knowledge/insite to the BI schema side of things.
Hope this helps.
Hi Ctayts,
This blog is such an invaluable resource, I hope you keep it going. I have an issue with units of measure that I am struggling with.
I am trying to change the unit of measure on a few stock items but some will not let me change it. When I try to change the base unit/stock unit from ‘Each’ to ‘12.5kg’ on our 201 product code I get the following error: “You cannot remove this unit of measure for the item because it is in use.”
This also happens on some other items. I have made sure that the item is not on any live purchase or sales orders using our test copy of the company but it still gives us this error. Our BP says that if a stock item has any kind of transaction history on it the unit of measure cannot be changed. This seems strange as when we migrated the data from Sage 50 everything had a unit of measure of ‘Each’ and we have managed to change most codes.
Their solution is to create a new product code but that’s not an ideal solution as we’d lose the history against that stock item. Is there anything I can do about this?
Thanks,
Chris.
Hi Chris
Thanks for your comments. I have every intention of carrying on with the blog so its nice to know that it is beneficial. Make sure you tell Colleagues, friends etc about it!
With regards to your problem, it isnt an easy one.
Orders that are in the Current tables are the complication but if SOP and POP Orders/Returns, that contain relevent items, are Completed and Archived you should be able to make the change.
I havent tested this too intensively, but I would suggest that item balances should be 0. I would also recommend that before making the change you should Reset Trading Values and Revalue the items after the change.
It may well be worth doing a Stocktake of the items afterwards.
Certainly test the above first!!!
I have known instances where scripts have been used to make the changes, but I would not recommend this.
Thanks
ctayts
Thanks for the info. I wrote a SQL script that replaced the units of measure to the ones I wanted (on our test company of course) and it seemed to work ok (everything seemed to calculate properly) but I won’t use it on the live system just in case.
Hello! I would like to know, is it possible to add only payments in sage 200, bank mess up, and to go true I need add only payments in cash book, just start to use saga and do not know how what to do! Thank you!
Hi Jolanta
Are you wanting to post a Payment, but only affect the Cahbook account?
To do this you can enter a Nominal Payment. Within the Nominal Analysis select the Nominal code for the Cashbook account you have selected. This way the transaction will post to the Nominal, but it will Debit and Credit the same Nominal code and therefore the Net affect is 0.00.
Hope this helps.
ctayts
Hello Ctayts!
It works 🙂
Thank you very much!
Jolanta
Yes that’s exactly what I’m trying to do 🙂 But I don’t think it will do it.
No, this would require some sort of look up which isn’t possible.
ctayts
Hi Ctayts,
I have a report designer issue again.
We sell on several eCommerce platforms and the software that ‘syncs’ the sales to Sage 200 puts them into a generic account per platform, (for payment reconciliation purposes).
Now it only populates the delivery address fields which isn’t a problem for printing despatch notes – (apparently Sage200 didn’t like it when trying to also populate the invoice address fields).
Printing invoices is ok after altering the layouts to show the delivery address fields however, when a customer wishes us to make tweaks to the invoice address/name etc. we have an issue – the amend address routine is obviously changing the invoice address (which isn’t printing on the invoices for the reason above) ……
I have managed to ‘bodge’ together some conditional filters, which allow me to put both the deliver address and invoice address on the layout and it will display primarily the delivery address – unless – the invoice address has been populated (via the amend invoice address routine).
Our eCommerce platforms and therefore invoice layouts are generally for a per country basis so I have the filters working as so:
and
The problem I have is a couple of invoice layouts could be for any country, which would render my filters useless and not produce a correct invoice. Is there any function in Report designer where by I could tell it to reference the country list in Sage? I really don’t want to have to type out every country 😦
I hope that makes sense!?
Thanks
Marcus
Hi Marcus I do not think it will do what you want, but I am not sure if I have understood your requirement.
So I make sure I understand what you are wanting to do: Are you wanting to say “If ADDRESSLINE1 = Something in the SAGE200COUNTRYCODE list?
ctayts
Hi Ctayts,
I think the answer to this is a “no”. In Sage50 when a stock line went into minus levels the line turned red, Can the same be reproduced with Sage200?
Thanks
Marcus
Hi Marcus,
Not without bespoke. You could use a report and apply a conditional format.
Ctayts
Hi – is there any way to change the formatting on the Nominal Ledger (lists) display screen ? Currently its left justified with no comma separation – which is super annoying to look at. I have asked my sage support people and they have said it can’t be done. It also exports to excel as a non numeric value. Grateful for any assistance. – thanks!
Hi Andrea
As standard there is no way of changing this. Potentially a develope could bespoke something for you.
I am not sure what you mean about the comma separation.
The export to excel is as Text. I am not sure if this is something that can be bespoked.
thanks !
thats a shame :¬( – its such an annoying unaccountanty type thing to have a display like this -by the comma – I mean no comma separation for thousands – its just a flat number – which again is really hard to look at – thanks for much for your reply.
Ak
Hi,
I’m struggling with the ‘new’ Sage 200 Report Designer.
Is it possible to make an image appear on a despatch note if it is for a particular sales ledger account but not for others? (Hope that makes sense!)
Thanks
Marcus
Hi Marcus
You should be able to do this. You may want to think about where it needs to go and the effect it may have on the section you are adding it to.
For example the size of the section will be the same for all.
But for now, the simpliest thing to do is add the image from Toolbox > Image (embedded).
Select the Image, then within the properties pane select Conditional Formatting.
Enter the following filter:
SLCustomerAccounts.CustomerAccountNumber “EnterTheAccountNumberOfCustomerYouWantItToDisplayFor”
N.B. You must enter the account number betwen ” ”
In the properties tab set ‘Supress Printing’ to TRUE.
Select OK.
Let me know how you get on.
Ctayts
Hi Ctayts,
Thanks for the quick response.
I have multiple accounts for which I would like to show the image how would I use the filter? OR argument between each one? (Guessing here)
I’ve had a quick ‘go’ but I must be doing something wrong because the report won’t ‘print’.
Thank you.
Marcus
It may be my fault as I missed out the sign (Sorry!!)
The filter should be:
SLCustomerAccounts.CustomerAccountNumber “EnterTheAccountNumberOfCustomerYouWantItToDisplayFor”
To do for multiple enter as follows:
SLCustomerAccounts.CustomerAccountNumber “EnterTheAccountNumberOfCustomerYouWantItToDisplayFor” OR
SLCustomerAccounts.CustomerAccountNumber “EnterTheNextAccountNumberOfCustomerYouWantItToDisplayFor” OR
SLCustomerAccounts.CustomerAccountNumber “EnterTheNextAccountNumberOfCustomerYouWantItToDisplayFor” OR
Sorry Marcus, NOT EQUAL TO signs are being stripped out of my posts for some reason.
But the filter should be:
SLCustomerAccounts.CustomerAccountNumber (NOT EQUAL TO SIGN) “EnterTheAccountNumberOfCustomerYouWantItToDisplayFor”
Hope this makes sense.
I still can’t get it working, maybe it’s because I am adding it to the footer section? The report is not being printed.
The image I would like to show is an ‘airmail’ image. I have 3 sales ledger accounts which are airmail accounts. I’ve tried:
SLCustomerAccounts.CustomerAccountNumber”FRAMAZ” OR SLCustomerAccounts.CustomerAccountNumber”DEAMAZ” OR SLCustomerAccounts.CustomerAccountNumber”F.N.A.C.”
and
SLCustomerAccounts.CustomerAccountNumber “FRAMAZ” OR SLCustomerAccounts.CustomerAccountNumber “DEAMAZ” OR SLCustomerAccounts.CustomerAccountNumber “F.N.A.C.” OR
I can manually put this into the conditional formatting field, however if I click on the … button to open the advanced editor it tells me:
unexpected token: [“”FRAMAZ””,,line=1,col=41]
Do you have any ideas, or am I asking it to do to much? :o(
Thanks
Marcus
Ah ha! I’ve just seen your reply, thanks I will try it now
This doesn’t work either, no report produced :o(
SLCustomerAccounts.CustomerAccountNumber “FRAMAZ” OR SLCustomerAccounts.CustomerAccountNumber “DEAMAZ” OR SLCustomerAccounts.CustomerAccountNumber “F.N.A.C.”WordPress stripped out the (NOT EQUAL TO SIGN) in my post but same result as before :o(
There shouldn’t be a problem with putting it in the footer.
Below are my settings:
I think I am understanding this a little more now! I have the filter setup as your directed and the report is printing. However the image is not appearing on any report, even the ones I want it to show on …
Maybe I have to use a NOT operator in instead of the NOT EQUAL operator?
Thanks
Marcus
I presume that there is only the one footer, as per the standard layout. No other filters on the layout.
if you want to email me the layout send it to ctayts@gmail.com
Hi,
Emailed :o)
Thank you.
Hi Marcus
If you replace the OR with AND this should work for the multiple accounts. Also make sure that the Supress Printing on the properties of the Image is set as False. Supress Printing then be set as True within the conditional Formatting properties.
SLCustomerAccounts.CustomerAccountNumber <> "FRAMAZ" AND
SLCustomerAccounts.CustomerAccountNumber <> "DEMAZ" AND
SLCustomerAccounts.CustomerAccountNumber <> "F.N.A.C."
Ctayts
Hi Ctayts,
This WORKS! It’s fantastic!
Thank you so much, I’m extremely grateful for your help.
Marcus
Not a problem
Hi Ctayts,
Have you ever seen an issue with Sage 200 where the default tax code on a Sales Order is not adhering to the default set on the customers account in Sales Ledger? I have the code set as Zero Rated and the box ticked to use as SOP default but the customer keeps showing up with Standard Rate. Even if I pick something else the Standard Rate stays as the default in SOP. This is only happening on one or two customers but obviously its an issue as they are being charged VAT when they shouldn’t be.
Thanks,
Chris.
I havent seen this specifically but there is an issue which is similar. The two problems may be related.
The known issue is logged with Sage as issue 4009.
For this the Stock item VAT code is ignored for UK customers, if ‘Use segmented addresses’ is switched on.
In this case the VAT code from the delivery address is used, rather than the item.
It may be worth getting your BP to check this with Sage.
Hi
2 qq.
Can an imported nominal journal in Sage 200 be setup to reverse in a follow period?
Can you go into a nominal ledger, find a journal and tell it to reverse?
Regards
R
Hi Robert,
As standard the answer would be no to both.
The import routine could be bespoked though.
How you are populating the import file? Can you not duplicate the journal in reverse on the file?
Once the journal is posted the journal type is irrelevant. Nothing is retained to say the type used so there is nothing to amend.
Ctayts
Hi
I make the import file in excel and then turn it into a csv file and import that. I can just change the drs to crs and crs to drs and repost with the new date but was looking for a quicker way to do it.
Thanks for the answer.
Regards
Robert
Is it possible to display username on sage reports ?
Hi Kris
Yes it is possible, depending on the report you have in mind.
The transactional reports can be amended by picking up the UserName field from the relevent posted transaction table (SLPostedCustomerTrans, PLPostedSupplierTrans, NLPostedNominalTran).
In the order processing modules, the OrderReturn tables have fields for DocumentCreatedBy.
Hopefully this helps.
Craig
Hi Ctayts,
Hope you are well, Thanks again for all the help you gave a few weeks ago, it has made a big difference.
I have a quick question for you, we sell the same types of orders over and over, but our prices change monthly. Is there anyways for Sage to copy an order or quote and automatically update it with the new month’s prices.
So for example, I do a quote today and then on April 1st, I have someone who requests the same type of system and the new prices have been inputted on Sage. When I copy the quote it will stay with the March prices. Is there anyways to have it source the new prices?
Thanks again for all your great help.
Any thoughts on this Ctayts?
Sorry Ryan, I thought I had responded to this.
If you are using Stock Control and the Price book. Then you could update the Pricelists in Pricebook.
When you create a new order select the ‘Copy Order’ button at the bottom, here you can select the order you wish to duplicate and the customer that the new order is for. There is also a tick box to copy the prices from the original order. If this is unticked the price used by the new order will be that as set up within the Pricebook module.
When creating a quote you have the same ‘Copy’ button, and here you can choose to copy a quote or an order.
NB. I have tested this in Sage 200 2011.
Regards
ctayts
Ah thats brilliant, right under my nose. Thank you very much!
Hi,
Very silly simple question here…
I have used the wrong project cost code on a batch of charges (not invoices, just charging the project directly.) Is there a quick way to change them or do I need to credit and recharge them all?
Thank you!
In Project Accounting > enter Transactions you are able to enter Cost adjustments. These are essentially a Project Accounting journal so you can move costs between Cost Items or even Projects.
Hi
How can we get Hot Fixes for Sage 200? We have sage 200 and dev license but no access to hot fixes. Is this correct ?
Hi Kris,
If your company is a business partner then you will be able to set up a Business Partner login on the Sage support website (link available at the bottom of Talking Sage 200). Within the downloads area you will beable to download service packs and hotfixes.
If you have a developer license you should probably be able to access hotfixs from the Sage 200 developer areas.
Not being a developer myself I do not know where these areas are but I would suggest contacting the developer support line.
Thanks
ctayts
We have a bespoke import routine that imports from our CRM directly into Sage 200. We had three companies and have never had a problem, I have created a further two companies and when we try to do a transaction import from our CRM to Sage on one of the new companies we get an error message ‘database read only’. Where would the import routine live within the MMs folder and do i need to edit the .dll too?
Hi Lorain
As the routine is bespoke it is defficult for me to advise. Aslong as scripting is enabled on your system (which it will be if one company works) then customisations will be able to work. I am not a develope myself so do not know if the .dll will need to be edited.
Is it not Sage 200 CRM that you are using? Or if it is what is the data that you are importing?
Sorry I couldnt be more helpful!
Ctayts
Hi,
I have a query regarding sales orders and invoicing. We have Sage 200 7.00.0042. We need to use a product code (currently use just TXT lines) when we sell labour services so we can report on them in the future, i.e “One Days Consultancy” @ £500 for example,The item code for consultancy needs to have the ability to overide both internal cost price and sale prices when order is processed as these costs can vary. Our current Sage Partner is saying that bespoke work is required to achieve this and I can’t believe that it is not available out of the box.
Can you confirm if this is the case?
Any help appreciated.
Jonathan
Hi Jonathan
Your business partner is correct.
You are able to create Labour/Service items which would allow you to change the Sales price, however as the costing method used is Standard Costing you would not be able to amend the Cost Price at the point of order entry.
As the products are Labour/Service the system does not keep a balance or a valuation, which is why the only costing method available is Standard (as there is nothing to track FIFO or Average against).
In changing the Internal Cost Price, would you want this to be used as the Cost of Sale posting? or purely for estimating profit within Sales Order processing.
If it is the latter reason, then you could create the Labour product as a Stock type item and allow the stock balance to go negative. If you want to be able to change the Cost of Sale value (which you wouldnt be doing with a free text item) then bespoke is the only option I am afraid.
Thanks
ctayts
Hi Ctayts
Thanks for your reply,
That is very useful, we will look at the best way forward on this and if we need to vary the labour cost or setup multiple products for the different cost bands.
Appreciate your help,
Jonathan
Not sure I’ve got the complete requirements, but why not create a stock item on a back-to-back order basis – then you will get a purchase order where you can vary the ‘cost’ price and yet is linked to the sales order and its profitability?
Hi,
I am trying to locate the standard edi files for inventory control within sage line 200, at the moment i believe the intention is to keep very straight forward and use csv flat files. I would be grateful if you have any documentation or example files you could point me too.
Thanks,
Mike
Hi Mike,
EDI does not come as standard with Sage 200. There are number of EDI specialists that work with Sage 200.
The EDI system selected would dictate the format you require, although I believe a lot of the systems can be adjusted to meet your requirements.
Have you got an EDI system/provider in mind?
Hi
Quick Question (hopefully)
How can I clear a User who apparently is logged in but actually not
Sage is reporting that the User has a Transaction Screen open which is stopping other areas from being access eg updating Accounting Periods
Best Regards
Ken
You need to access Accounting System Manager > System Utilities > User Login Status.
There will be at least one disconnected user who must have crashed out of the system whilst having a screen open.
Note this will only clear the disconnected session so is ok to do if the user has now logged back in.
Ctayts
Hi,
New to Sage 200 install, i’ve installed it as per the instructions in the manual (frankly useless) and have installed Sage 200 on a Windows 2008 standard server. Have finished most of the install and have entered the enabling string but I’m stuck when it’s asking to create a SageConfiguration database, unfortunatly it keeps saying “login failed”
I’ve gone through some of the forums and found similar issue but the guy fixed it but there’s no mention of the fix.
I also noticed that within the SQLServer Management studio which i have installed on the server there is no Sage databases, are there suppose to be any there.
Your help will be greatly appreciated,
Thanks
Bhav
Hi Bhav
What version is this for, 2011 presumably?
At what point do you get the Login Failled error?
The Configuration database is basically the System Administration database that will hold the Companies, Users and Menus etc.
At the top left hand coner it will be an option called ‘Sage 200 SA (localhost or Servername)’. Right click this and select “Connect to another server”.
This should be as follows:
https://servername:10443/Sage200Administration/Sage200AdminService.asmx
Clicking OK, assuming you do not receive an IIS error, you should now be prompted to create the configuration database again. If it doesnt, Right click Sage 200 SA and select Properties in order to create your configuration database.
When you create the Configuration database you will be prompted to enter a SQL server login which needs to have SQL SA privilages.
Let me know how you get on.
The install does not create any databases in SQL Server. These will be created when you add your Companies within System Administration.
Ctayts
Hi, I’ve managed to install sage200 103 and it seems to be working fine on the server but cannot connect from client. Disabled the firewall and opened the ports too. I can go as far selecting companies and it says “login failed for user ‘.’ please run system administration and check company settings” Am i missing something?
Hi Jai
On client install C:\Program Files(x86)\Sage\Sage200
Open SVN4WIN.ini in notepad and make sure the Logon and Spool paths specified are the correct UNC paths.
Ctayts
Sorry, I could not reply to your post for some reason.
I’ve looked for SVN4WIN.ini but i couldn’t find it. Tried searching too but it takes a day and doesn’t find a thing. any idea? cheers
Which version of Sage 200? If no SVN4WIN.ini then probably 2013?
Yeah, it is 2013.
Hi Catyts
we are currently using 2011 version and I want to know the file format for invoice files.
Do you know if this is a fixed field format ?
and how can this be exported from Sage ?
many thanks
Lesley
They are fixed file, can’t be exported as come on install disk. Your BP could provide or I can send if you need. Is it for Sales Ledger?