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

Inactive stock flag ODBC

Sage 50 general help forum - Free help and support for all general issues
Post Reply
cooperdooper
User
User
Posts: 3
Joined: 22 Jan 2021, 09:03
Sage Version: v2018 24 UK/Europe/Africa

Inactive stock flag ODBC

Post by cooperdooper » 22 Jan 2021, 09:11

Hi,
Trying to find the table/field through ODBC that shows when a stock item is marked as inactive.

I assumed it would be in STOCK (just as the IGNORE_STK_LEVEL_FLAG is when stock level control is turned off which has 0 or 1 to designate)

But I can't find it in STOCK or any other likely tables.

Thanks!

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

Re: Inactive stock flag ODBC

Post by brucedenney » 22 Jan 2021, 10:48

The field is called STOCK.INACTIVE_FLAG
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.

cooperdooper
User
User
Posts: 3
Joined: 22 Jan 2021, 09:03
Sage Version: v2018 24 UK/Europe/Africa

Re: Inactive stock flag ODBC

Post by cooperdooper » 22 Jan 2021, 11:57

Thanks for fast reply but I've scoured the STOCK table via ODBC so many times now (via table linked into an Access dbase)
There's no field of that name or similar in what i'm seeing.

I've got INTRASTAT_COMM_CODE, INTRASTAT_IMPORT_DUTY_CODE, IGNORE_STK_LVL_FLAG
But not INACTIVE_FLAG

I'm using Sage50 program version v26.3.245.0 and ODBC 64bit v26 driver.
I'm starting to wonder if this field isn't exposed, or there's a bug, in the ODBC driver.

Thanks though!

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

Re: Inactive stock flag ODBC

Post by brucedenney » 23 Jan 2021, 10:07

I do not use the 64bit drivers as moist of my work is with MS Office and only the 32 bit version works properly with Sage 50.

I have checked the v26 32 bit driver and the field is present.

Screenshot 2021-01-23 095513.png

This could be a bug in the 64bit driver.
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.

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

Re: Inactive stock flag ODBC

Post by brucedenney » 23 Jan 2021, 10:09

If you are using Access, why not install the 32bit version of office which is properly compatible with all the sage integration, you can then use the 32 bit drivers.
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.

cooperdooper
User
User
Posts: 3
Joined: 22 Jan 2021, 09:03
Sage Version: v2018 24 UK/Europe/Africa

Re: Inactive stock flag ODBC

Post by cooperdooper » 25 Jan 2021, 08:24

Thanks for the suggestion.
Just tried the 32bit odbc driver (and i'm running access for off365 32bit version) but no different i'm afraid.

Strangely other fields showing in your screen grab are not present in my STOCK fields exposed via either 32 or 64bit odbc (eg I can't see DISC_E_LEVEL10_RATE, PURCHASE_NOMINAL_CODE, RECORD_CREATE_DATE)

We might have to give up on this one!
Thanks anyway.

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

Re: Inactive stock flag ODBC

Post by brucedenney » 25 Jan 2021, 11:50

If you are doing this from Access I assume you have created a DSN to access the data.

I wonder if the DSN is pointing at the correct data set.

Add a product and see if it appears

Can you attach the DSN?
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.

SomeGuy
User
User
Posts: 17
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: Inactive stock flag ODBC

Post by SomeGuy » 28 Jan 2021, 08:57

There is no difference in the fields that are exposed between the 32 bit and 64 bit ODBC drivers as far as I am aware, and the problem cannot be data related.

I believe that the problem you're hitting is that Access limits the number of fields in a linked table to 255 but the STOCK table in Sage has significantly more fields than this - I've not counted them but it's well over 300. What is happening is that Access is only linking the first 255 fields and this is all you see, even though there are more. I am not sure how Access decides on the order but I'd guess it is something to do with the order they are declared in Sage.

To my knowledge the 255 limit is hard coded in Access and applies to all data sources, not just Sage. So unfortunately you can't get all the fields, and because you can't control the order of the fields you could not do something like linking the table multiple times and expose different fields in each.

I suspect the reason why Bruce sees them is that he is using Excel - that's an educated guess as the screenshot certainly looks like the query wizard that Excel uses - and this does not impose a limit on retrieving more than 255 field names (although I believe it may still limit to 255 fields in a query).

So your choices are pretty limited I'm afraid. Best bet would be to query the ODBC directly rather than going via Access.

Hope that helps.
SG

Onion RS
User
User
Posts: 12
Joined: 13 Feb 2020, 10:49
Sage Version: v29 UK/Europe/Africa

Re: Inactive stock flag ODBC

Post by Onion RS » 28 Jan 2021, 11:05

Hi Someguy,

Very interesting. I've played around a bit and it seems that the MS Query interface does indeed have a 255 field limit. However the ODBC driver does not. What I've done is use MS Query to set things up with a single field query like SELECT STOCK_CODE from STOCK and then edit the SQL in the connection in Excel to read SELECT * from STOCK. Then I get all 365 columns brought back into Excel.
Connection.JPG
Regards,

Ian
Login to access the files attached to this post.

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

Re: Inactive stock flag ODBC

Post by brucedenney » 28 Jan 2021, 11:36

Someguy has a given good insight into this, indeed I was using Excel.

If I look at where in the list of fields that field is it is towards the end so is consistent with his view.

Having a quick google, it confirms there is a 255 field limit.

https://support.microsoft.com/en-us/off ... c1025bb47c

I dumped the table and there are 367 fields in stock.

As a work around, you could do an external query using something like the outwit ODBC program to get just the field you want in a CSV file, or you could look at using one of the odbc to odbc tools an see if there is one that can only present a subset of fields. e.g. https://www.easysoft.com/blog/access-column-limit.html
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 11 guests