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
- - - - - - - - - - - - - - - - - - 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
-
- User
- Posts: 3
- Joined: 22 Jan 2021, 09:03
- Sage Version: v2018 24 UK/Europe/Africa
Inactive stock flag ODBC
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!
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!
- brucedenney
- Site Admin
- Posts: 4643
- Joined: 28 Feb 2006, 09:56
- Sage Version: v30 UK/Europe/Africa
Re: Inactive stock flag ODBC
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.
-
- User
- Posts: 3
- Joined: 22 Jan 2021, 09:03
- Sage Version: v2018 24 UK/Europe/Africa
Re: Inactive stock flag ODBC
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!
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!
- brucedenney
- Site Admin
- Posts: 4643
- Joined: 28 Feb 2006, 09:56
- Sage Version: v30 UK/Europe/Africa
Re: Inactive stock flag ODBC
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.
This could be a bug in the 64bit driver.
I have checked the v26 32 bit driver and the field is present.
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.
- brucedenney
- Site Admin
- Posts: 4643
- Joined: 28 Feb 2006, 09:56
- Sage Version: v30 UK/Europe/Africa
Re: Inactive stock flag ODBC
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.
-
- User
- Posts: 3
- Joined: 22 Jan 2021, 09:03
- Sage Version: v2018 24 UK/Europe/Africa
Re: Inactive stock flag ODBC
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.
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.
- brucedenney
- Site Admin
- Posts: 4643
- Joined: 28 Feb 2006, 09:56
- Sage Version: v30 UK/Europe/Africa
Re: Inactive stock flag ODBC
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?
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.
Re: Inactive stock flag ODBC
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
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
Re: Inactive stock flag ODBC
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.
Regards,
Ian
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.
Regards,
Ian
Login to access the files attached to this post.
- brucedenney
- Site Admin
- Posts: 4643
- Joined: 28 Feb 2006, 09:56
- Sage Version: v30 UK/Europe/Africa
Re: Inactive stock flag ODBC
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
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.
Who is online
Users browsing this forum: No registered users and 6 guests