Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

IF Statement help

Report design issues and solutions.
Post Reply
gazmoz17
User
User
Posts: 54
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

IF Statement help

Post by gazmoz17 » 21 Aug 2020, 14:57

Hi Bruce,

I have identifiers in customer analysis 1, either ROOFING or GRP. To show customer type in invoice profit reports.
I run a profit report containing this above field and then pivot/filter on customer type.

When the field is blank (where we havent set the customer type in new customer screen) I go back and key all these in the customer screen and in the profit report which is exported to excel.

With an IF statement I want my invoice report to allocate ROOFING or GRP, by looking up if said invoice contains Barcode R (roofing prod identifier) then ROOFING.
Roofing Products Bar code.JPG
So if inv analysis1 = blank and contains Barcode R= ROOFING.....if blank and doesnt contain R= GRP

Tried the below:

SALES_LEDGER.ANALYSIS_1= "" AND STOCK.BARCODE = "R"?”ROOFING”: SALES_LEDGER.ANALYSIS_1= "" AND STOCK.BARCODE <>"R"?”GRP”: SALES_LEDGER.ANALYSIS_1

*But keeps returning GRP when its an unallocated customer type & said inv only contains a roofing product with barcode R in background.

Any help much appreciated, I've tried writing the expression a few diff ways.

Many Thanks
Gareth
You do not have the required permissions to view the files attached to this post.

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

Re: IF Statement help

Post by brucedenney » 21 Aug 2020, 15:50

I think you need to nest the if statements rather than ANDing them together something like this I think

Code: Select all

SALES_LEDGER.ANALYSIS_1=""?STOCK.BARCODE ="R"?”ROOFING”:”GRP”:SALES_LEDGER.ANALYSIS_1

Code: Select all

If Analysis 1 is Blank  then
     If Barcode is "R"  value is Roofing
     Else  value is GRP
Else value is Analysis 1
Note this is untested
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

gazmoz17
User
User
Posts: 54
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: IF Statement help

Post by gazmoz17 » 24 Aug 2020, 08:05

Good morning Bruce,

Ive tried it first thing this morning and unfortunately Im struggling getting it to return correct market type.

Keeps returning GRP even on a test invoice with a single roofing product on. Returns GRP as well if customer doesnt have a market type and inv for said customer doesnt contains any prods just a message line.

Any further help much appreciated, layout attached....new expression in red next to original.
SAS Allocate mkt type.report
Many Thanks
Gareth
You do not have the required permissions to view the files attached to this post.

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

Re: IF Statement help

Post by brucedenney » 24 Aug 2020, 09:19

Well sage is full of interesting quirks.

What I did was to simplify

I tried the expression STOCK.BARCODE ="R"?"ROOFING":"GRP"

This yielded the result GRP even when the BARCODE was R (In this context the last product on the order is the barcode we are looking at.)

I added the Barcode to the report and there I saw "R" to double check, so something was crazy.

My next thought was perhaps there is a space or other invisible character so I tried to use the trim function

Trim(STOCK.BARCODE) ="R"?"ROOFING":"GRP"

The expression editor would not let me save it, that was strange!

This is normally because the type is not the same, ie BARCODE is not a string, but it is not a number and it behaves like a string in use..

So is added a Cstring to explicitly convert it to a string

Trim(cstring(STOCK.BARCODE)) ="R"?"ROOFING":"GRP"

And it worked... so BARCODE is an odd field, it is not a string, even though it contains a string.

The trim is probably redundant, only useful is a user actually types a space when entering the code.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

gazmoz17
User
User
Posts: 54
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: IF Statement help

Post by gazmoz17 » 24 Aug 2020, 13:54

H Bruce,

Appreciate your time there!

Yeah I thought maybe a space before R but quadruple checked (dont know the trim function) and checked was the R in barcode.

Tried dont if formula a but diff and didnt work.

Cheers for this big time saver :D

gazmoz17
User
User
Posts: 54
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: IF Statement help

Post by gazmoz17 » 24 Aug 2020, 14:28

Hi Bruce,

Yeah that works great.. but is there a way to accomodate it with the If market is blank only hen allocate market type.

At moment.... Trim(cstring(STOCK.BARCODE)) ="R"?"ROOFING":"GRP"

Overides what market type is in background. Not really a biggie for new customers but could be for current.

Eg current roofing customer only buys ancills (which are used in both market types so I dont put an R in barcode prod background) then currently that will show as GRP but really the ROOFING in customer screen background for said customer should overide this as should only be If SALES_LEDGER.ANALYSIS_1=""

Thanks

gazmoz17
User
User
Posts: 54
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: IF Statement help

Post by gazmoz17 » 24 Aug 2020, 15:13

Hi,

Ignoring if SALES_LEDGER.ANALYSIS_1="" for the time being.

If the last invoice item is none Roofing (no barcode R) it returns GRP even if any other items are roofing with the R in background.

e.g.
Roofing then none roofing prod example.JPG
So the above should return roofing but for some reason returns GRP?

Many Thanks
Gareth
You do not have the required permissions to view the files attached to this post.

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

Re: IF Statement help

Post by brucedenney » 24 Aug 2020, 15:52

If you have a mix of items on an order, then you can not classify the order into one or other category, it is a mixed order and you will never resolve that issue because the issue is in the data.

Perhaps it would be better if we start at the beginning and ask the fundamental question

Why are you attempting to classify orders into different types?
What is the objective?
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

gazmoz17
User
User
Posts: 54
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: IF Statement help

Post by gazmoz17 » 25 Aug 2020, 07:28

Morning Bruce,

Just to establish if the invoice is a Roofing customer or not, anything else we just call a GRP customer...which is vey broad but we dont really get much more detaled than that.

Reasons


Currently so can put the market type in salesledger analysis 1. And then from that distinguish the roofing customers and send emails/marketing etc.
Primarily to show roofing financials eg average net sale etc and these figure as comapartive against "GRP". Want to take this one step further & pivot from that report and will be pivoting to show Roofing/GRP comparitives.
We are going to set up a seperate company down the line which will be just roofing. But would be handy to see how much roofing business we currently do.
The barcode R I use to generate a roofing price list with up to date sales information. So thought could utlilise this identifier to allocate the market type itself to save me filtering on blank market types in excel (in report I sent you) then going into each individual inv to see what type of customer the are...and then finally going into each customer & entering the salesledgeranalysis1 field.

The layout I sent you I export to excel filter on Market Blanks then manually key market types. Then copy and paste into a master excel which is just that sagelayout but from beginning of time with excel subtotals at the top. The excel has an additional column which transport costs which we dont bill the customer. I do a vlookup on Trnasport customers excel invoices to populate this transport cost which then gives me a true profit figure for each invoice after weve deducted transport costs which we have absorbed.

I dont know if theres a quicker way to do this! Tried to make the overll process quicker by:
  • Attemptng this market self allocating
  • Requesting transport companies invoices on excel rather than pdf so can do vlookup. Was doing it manually control f...find the job ref then key transport cost fr said invoice per transport pdf inv line
  • New to pivot tables, so want to use these to compare data instead of just manipulating the master spreadsheet via filter and subtotals at the top
Thanks
gareth

Many Thanks
Gareth

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

Re: IF Statement help

Post by brucedenney » 25 Aug 2020, 10:10

Well, an analysis of all items might be more useful in working out the roofing part of the business.

I would link all the orders and items to an excel sheet and build a pivot table off that.

What you will need to do is to have something in the details section that detects the presence of an R in the barcode of any product on the invoice.

For example if the barcode is R then the value is 1 else it is 0.

Add up that field in the footer and then, if the value of that field is not 0 then this order has roofing items on it.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

gazmoz17
User
User
Posts: 54
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: IF Statement help

Post by gazmoz17 » 25 Aug 2020, 10:31

Hi Bruce,

Yeah I do plan on doing a seperate sage report showing products to pivot from. But do want to keep this report as is on an invocie basis so can include the transport costs per invoice...when we book transport jobs we reference the sales order no. so do a lookup on this.

Right im with you similar to what you do for me with an error report where the error had a value and at the bottom an error count triggered an error message. I'll have a look at that layout you helped me with and see if I can mirror it.

Cheers
Gareth

gazmoz17
User
User
Posts: 54
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: IF Statement help

Post by gazmoz17 » 10 Sep 2020, 10:40

Hi Bruce,

Apologies im late getting back to you been shortstaffed so all project stuff on back burner.

This work relly well now from your recommendation so thanks again.

Cheers
Gareth

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest