Sage X3

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?

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…

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…

I have a stored procedure I would like to call to populate a work table as a preprocessor to an SQL client requester. Syntax would be helpful. Thanks SQL stored procedure call from X3