Browse By Tags

  • SQL Server Web vs Standard for Sage CRM

    Hi all, I am looking at the price of SQL Server Web vs Standard and the price gap is huge. The web version is much more cheaper compared to Standard. In the installation guide of Sage CRM did not mention if we were suppose to choose Standard over Web…

  • What is the best way to get a GLCode attached to either an Item or a PO?

    I am trying to join one of the following tables to GACCOUNT. PORDERQ or ITMMASTER. There doesn't seem to be many ways to get from GACCOUNT to an ITMREF_0 or POHNUM. 

    I have tried a few different options but none seem to be working. Your help, as always…

  • How do I join STOJOU to GACCCODE?

    I am looking to join the two above tables, According to the data models, the join is supposed to happen like this: 1;ACCCOD. How is that supposed to work if STOJOU doesn't have any accounting code? Also what does the 1 mean in the data model? Any and…

  • Looking to build a report for Sales by GLCode by month

    Hi, as the subject says, I am building a report to pull sales numbers per company by GLCode in sql server. I'm having a hard time getting data that looks correct. I have tried a few different ways of joining SORDER table to GACCOUNT table. I've tried…

  • Where can I find GL code and descriptions of the code?

    I am building out existing access report in sagex3. Where can I find GLcode and descriptions? I've looked at the accounting data model tab and am just not really sure what I am looking for. 

    Thank you in advance!

  • What is the column name for AvailableStock->TotalRequirements in sql?

    I am looking to get the sql column name for  total requirements qty located in available stock. If anyone could point me in the right direction, it would be greatly appreciated.

  • Is there a way to see how much quantity was pulled from a specific lot?

    I am creating an invoicing table that has Item, Lot and QtyOrdered. Is it possible to see per lot allocation by sales order number? I know you are able to see the qty on hand/lot. For example, if I have an item that exists in multiple lots, I only want…

  • How to create a new index that supports the "Include" option on an X3 table?

    It's always the way isn't it?  You don't see any articles about creating indexes then several come all at once!   Chris Hann recently wrote the article "How to use the Database Optimization function" but what if you want to create a new…

  • Looking for Total Usage Qty

    Good Morning! I am currently working on swapping from Process Pro To Sagex3 and one of the columns in PP is Usage qty which is total qty used for the year. Is there a field in sagex3 for this? As always, your responses are greatly appreciated!

  • What is EOQ?

     is it estimated order quantity? Does anyone know the name of the column in sql? As always, your responses are greatly appreciated!

  • Where can I find the received qty from a PO?

    As seen above, I am looking for received/delivered qty from a Purchase Order. Is it the Received STK or PUR in the PORDERQ table? so far I have been using received stk, but am unsure if that is correct

  • Sage 100c Premium (SQL) Disaster Reocvery

    I'm confused about the relationship between Sage data files and the SQL database. If a server dies, or gets ransomed, my first step will obviously be to reload the application server from backup, then the SQL backups. There is potential for different…

  • How do suppliers work?

    How are suppliers set up? I see in the ITMBPS table, there is a flag for default supplier which makes me think that if it is not a default supplier, is it the last supplier that updates anytime an item is received from a new supplier? 

  • Important Information - Microsoft SQL Server®︎ Lifecycle Dates - 2012 Server Upgrade Requirements

    Notice 21-09 | September 08, 2021

    As published by Microsoft® Extended Support for SQL Server 2012 will cease effective 12 July 2022. In line with this The Access Group would like to remind you of this and the impact on our products that use Microsoft…

  • How can I go about joining the SINVOICED table to the STOJOU table?

    As seen in the subject, I am looking to join the STOJOU table to the SINVOICED table without the use of the ITMREF column. Data models show a one way arrow from STOJOU to SINVOICED with VCRNUM;VCRLIN. I am unsure of what to do with that, with VCRNUM only…

  • SAGE X3 TABLE INDEX dimensional field, example GACCENTRYA field CCE_1

    When we try to create an index in table GACCENTRYA including field CCE_1, SAGE X3 gives error. For now solution is to add the index manually directly in the SQL Server, any solution to make this solution permanent? Thanks.

  • Looking for the sales FOB field in sage x3

    Good day everyone, I am looking for the sales FOB field in sage x3. If there is no field in x3, is there a column in sql? So far I have been unsuccessful in finding said field. Any guidance would be greatly appreciated. 
    Thank you in advance!

  • I am trying to join the PORDER table to the SINVOICED table

    Good morning Sage City,

    I am creating an invoices tables with the base table SINVOICED, I need to JOIN PORDER table to my base table but am unsure of how to do so. How can I go about doing this?

  • llamada a procedimiento almacenado de sql server por código desde un elemento de Cálculo

    Buenas tardes.

    No se si este es el lugar adecuado para hacer esta consulta.

    Quisiera saber en sage 200 si es posible ejecutar un procedimiento almacenado de sql server desde un elemento de Cálculo del repositorio, si existiese alguna función de sage que…

  • SQL Server Topic - Max Degree of Parallelism

    Last month in June, I wrote about capping SQL Server's memory so that it does not use up all available memory. You can read about it here. In this blog, I want to look at another setting in SQL Server, and that setting is the Max Degree of Parall…

  • SQL Server Topic - SQL Server has to know its limitations

    The main objective in this blog is to draw your attention to SQL Server and the min/max memory setting. Why is this setting important? Well, SQL Server is a memory hog. It would take all the memory if you let it. SQL Server has got to know its limitations…

  • Consolidated billing query with AR_InvoiceHistoryDetail

    We are trying to write a query or stored procedure to identify the cash receipts for each of our franchises (warehouses) but ran into some trouble with consolidated billing. Since payments aren't applied to the line items we need to create a lookup table…

  • Collation de Sql Server


    Chez un client on a installé la langue Anglaise, puis en changant la langue dans l'application un message d'erreur appraitre : 

    Suite à des recherches de la source de l'erreur on aperçois que la collation du serveur et de la base…

  • Limit SQL Server memory to a fixed amount

    Have you ever noticed that SQL Server loves to use memory, even when there is not much activity happening on the server?  Is there a way to manage the amount of memory utilized by SQL Server? Yes, you can limit the amount of memory SQL Server can use.…

  • SQL Replicator and the table PRM_MASTER__EMPLOYEE_TAX

    We just installed SQL Replicator and previously used Anterra Datacenter.  The table PRM_MASTER__EMPLOYEE_TAX has a field called Tax_ID that, when using Anterra, held Tax ID's like FED, MED, MED_E, etc.  Now, using SQL Replicator, that has changed to…