Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

Creating Variables

Report design issues and solutions.
Post Reply
welshal
User
User
Posts: 21
Joined: 09 Jan 2012, 21:51
Sage Version: v2015 21 UK/Europe/Africa

Creating Variables

Post by welshal » 05 Mar 2013, 15:42

Lo all.

Can you create a variable to put in a report?.

We want to create an invoice with a Bill Of Materials layout.
The Component Code and Quantity are there, but we can't find a variable to include the description of what would be
a part if it was just a vanilla invoice.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Creating Variables

Post by brucedenney » 05 Mar 2013, 16:12

What you need to do is to "join" the BOM item to a second copy of the Stock (Product) table and then you can reference the product description from the 2nd copy of the stock table.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

welshal
User
User
Posts: 21
Joined: 09 Jan 2012, 21:51
Sage Version: v2015 21 UK/Europe/Africa

Re: Creating Variables

Post by welshal » 06 Mar 2013, 12:16

Firstly let me debase myself with thanks for your work here, and responding to my question.
Suitably humbled, I must now ask how you link a BOM item to a stock (product) table?

I'm afraid we're total database novices here, pushing sage further than we understand.

Regards.

Al.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Creating Variables

Post by brucedenney » 06 Mar 2013, 12:48

What you are looking to do is complex and not so easy to explain in words and not the kind of thing I would suggest a novice attempt but if you are up for it I will try and help you do it. It is good to push yourself.

The description associated with each stock code in the bom can be "looked up" by linking the BOM product code to an extra copy of the stock table. So if you wanted to link say the first 10 items of the BOM to the product description you would need 11 copies of the stock table one for each of the bom items and one for the actual item, one copy of the stock table would relate to the product and then each additional copy would relate to an item on the BOM that it was linked to.

To do this in the report designer go to joins and drag a copy of the stock table onto the area and then drag a link between the BOM stock code 1 and the Stock code in the Stock table, all the items in this copy of the stock table eg the description will now relate to the first item in the BOM.

I attach a report that I did that does a BOM explosion of the first 10 items on a sales order as an example, hopefully you can make sense of it and use it as a guide.

If you get really stuck, I am available to hire :wink:
Login to access the files attached to this post.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Post Reply

Who is online

Users browsing this forum: No registered users and 19 guests