Browse By Tags

Hello folks, We are starting the process of upgrading from an older version of MAS 90 which ran on Providex to 2023 Premium. There are a number of custom apps that we've developed over the years to run with our MAS data and they all used the MAS ODBC…

Hello everyone When I create a SQL trigger on a table in SQL management studio the trigger will be deleted when the table is validated or a new release is patched. Is there any way to create SQL triggers so that they are protected? Like activity codes…

Hello, I have written a simple SQL query that returns info about sales returns. I am trying to get the return code description to show up, from the ATABDIV table (Miscellaneous Tables) but it's throwing this notice that the column name, LNGDES_0,…

I am trying to develop a query that involves aggregating invoice lines such that values for kits are the sum of their components. I can see there is a flag (LINTYP_0) to determine what is a kit and what isn't, however I am having a hard time figuring…

I have query in the Graphical Query Tool that I'd like to see the actual query executed. How do I see the query in SQL syntax? I want to see the tables and how / what they are joined. Is there a way of doing that?

What I am trying to do is write a query that will list all SEI processes and the Folders they are located in. I can't seem to find any table in the SEI database that contains the folder structure. Is what I'm trying to do possible? My organization is…

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…

Hi, I want to create a Business Intelligence Report by using Report Manager & Connector. In this report, I want to get rows from table APPYM of all Companies database. But first, I need to know which database is actually a company database for Sage…

Hello, I have a script complete that runs a SQL Query and returns the results back in a MsgBox. The very last part I need to do is access the Sales Order Number to include this in my "Where" clause for my Query. This needs to be run on the Client…

I have successfully been able to create queries that can access the data in Sage but I am now trying to query data from a UDT and I am getting an error. Can anyone advise how to workaround or solve this error? This query runs fine: select * from …

Using Sage 100 ERP v2017. I am trying to run a very basic MS Excel query in order to build a database for our new website. Literally, all I am trying to get is the product part number, description and the name of the item image. My query is as follows…

Hi, I am trying to run a query to pull fields from the screen Miscellaneous Receipts. The three things I need to pull are Quantity/Product/Receipt Date/Supplier Lot Number. I looked at the screen level and did not find a table from which I can pull…

Hello, I have a question. I created a SQL query using the query tool, and I am wondering how to make it so that the parameters function. I added a 'Date From' and 'Date To' under the Parameter definition window, but when I run the query and select criteria…

Been looking for a way to do that in Sage 100 when using Premium version, but not finding anything. In Sage 500, we commonly will create a custom SQL view and make it available to users in BI Explorer. Anyone else out there mastered this? Thanks…

Hi , I have a query , which is desired to return the KG's produced and sold (just based on finished goods). What I have is the following: -- SELECT ISNULL(I.ITMREF_0,'[Total-Produced]') as 'Product', P.ITMDES1_0 as 'Desc', H.STU_0, SUM(P.ITMWEI_0…

Hello, I am not having any luck retrieving the allergen description (misc table 4100) for finished products that have >1 allergen listed. When I bring in the ATEXTRA table through a query, it returns values for products with 1 allergen listed, but if…

I would like to make a filter returning the record with me max value, is there a way to do that? In SQL I use this: select MAX(NUM_0) from PINVOICE Just a example, how I can do that in X3?

Hi, Is there a way to join the two tables ITMMVT and ITMCOST such that values of ITMREF,STOFCY from ITMMVT display when these are NULL for ITMCOST and values of ITMREF and STOFCY for ITMCOST display when these are NULL for ITMMVT. I can get the first…

Does anyone know how to join tables through code (with one table being ATABDIV (misc tables))? I am trying to link TSICOD (statistical group ) (dim=0) to the table on the ATABDIV table (it's table 20), but I can't do it through code. Can anyone help me…

Does anyone know how to display a column in SQL which displays the % of that line as a total of the column. Ex. A --- 25 B --- 20 C --- 20 D --- 15 I want column A to display as a % 25 / 80 (31.25%) and B as 20/80 (25%) ,etc. Thanks

Hello, I have created the following query, but am getting an annoying message saying "the conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value." (see below screenshot). SELECT S.ITMREF_0, I.ITMDES1_0, S.STOFCY_0…

Does anyone how to to write a statement in the 'WHERE' clause that allows you to the option (not the requirement) to use a certain criteria? I have the following query: SELECT DISTINCT K.CPLWST_0, H.MFGNUM_0, (H.TRKLASTC_0 - 1), I.ITMREF_0, P.ITMDES1_0…

Does anyone know how to create a where clause in SQL such that For a given column (which has both negative and positive amounts), it only shows the positive amounts (or shows all negative numbers as 0.00)? I have tried, but to no avail. The expression…

Is there are command to call Today's date while creating or editing a View in Customization?

Does anyone have a very basic example of how I can integrate a SQL statement in to a script? I've run into plenty of occasions where I wish I knew how to accomplish this, here is my current goal: We ship using Starship and for a variety of reasons…