• Query tool - How to get data from custom fields

    Hello everyone,

    I am running a Query to get some info about a item.

    Table ITMMASTER; Field ITMREF; Others Fields by default.

    The info that i need is related with a Custom Field, where i need the value of the field and their descripton. 

    Table ITMMASTE…

  • Required stock in-transit reconciliation report.

    Amount is being posted into In-transit account through inter-site transfer and container management, but not able to reconcile. Hence need a report to reconcile the transactions with Goods In-transit A/c.  

  • Sage X3 Excel Addin Query Level


    I'm attempting to read a query in Excel, but the query is level 5. Currently when I set the level of the query to 5 via the menu item screen, it comes back as level 1. I can't find any way to effect the end result of what data comes out other…

  • Looking for description data for Payment Terms, Carrier, Delivery Mode

    Hello community, 

    I am trying to add some description data to a customer facing crystal report and noticed I am only pulling the ID/ Code of the certain fields but need to show the full description of what that id/code is. Trying to add the descriptions…

  • Querying the SEI database directly

    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…

  • How can I link the following tables (SDELIVERYD, MFGITMTRK, ITMMVT, ITMMASTER, STOJOU) "Graphical query"

     I tried with all possible keys, but I still have a problem with the connections in between the table. Please, can anyone help me? I intend to create a query that presents all shipments and all production follow-ups made on a date and the respective accumulated…

  • SAGE REST API - How to get all the records of a table in single fetch/query

    When I try to get all the records in a single query, I got an error from X3 folder. And also tried with $next/$last links, however, we need to call these links synchronously until we get all the records from a table.

    Is there any chance to get all the…

  • Last transaction dates/Inventory

    Hi all - I'm using Microsoft Query to extract info from the system and it appears whats happening is if the item has a few bin location the data is being returned in that manner meaning I could have the same item listed a few times

    Looking for a means…

  • 4GL embedded Sql query presenting error on script run.

    Let me start by saying: Everything is DONE, except the sql query fails. This is the last piece holding up deployment.

    I am trying to embed a sql query in a 4gl script to pull specific data into a variable set, then email the results on a nightly scheduled…

  • Row Level Permissions on Query Reports

    Hello All,

    I am trying to setup row level permissions on Query reports so that users can see data only specific to their site. I was able to setup the row level permission on all the transaction screens, but I am not sure how to setup similar permissions…

  • Fixing "Division by zero error" in Graphical requester calculation fields!

    Hi Team,

    This is a suggestion to add a feature in Graphical requester creation where user adds a Calculation filed (like Margin = Price/Cost) and for some instances when the Cost is zero in this example, they get the famous error "Division by 0" error…

  • Filtre sur requêtes portail


    Il serait intéressant de pouvoir donner la main aux utilisateurs de pouvoir filtrer une requête qui serai affiché sur le portail.

    Ajouter la notion de "Critère" comme en "Visualisation de requête", via un bouton action sur…

  • Filter within Graphical Queries on Home Pages

    It would be great to have the amazing filtering capabilities that X3 currently has added to Graphical Queries displayed within Home Pages, this would remove the need to export to another app "Excel" and obtain the relevant data right within X3.…

  • How to do a select distinct in Query tool

    I'm trying to create a new query so that my user can have a list of new prep lists on their landing page.  using table STOPRELIS, I was able to create a query but there is a line per product, when I only want the prep list, pick ticket, and so number…

  • Graphical Query

    I am running the following Query, however when I run it, it does not output the delivery number. What am I missing.

  • Sager X3 V11 - Display a chart in an homepage


     I've made a request using the standard query tool and I woul like to display this chart in an home page.

     It works fine within the requester - I could see the bar chart.

     I've successfully created the corresponding menu items (based on one provided…

  • Miscellaneous Receipts


    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 this…

  • Expand Capabilities in Query Tool record searches

    For customers with a lot of data, allow wild card searches (consistent w/ left list lookups) or filter criteria for 'begins with', 'contains', etc. from within the Query Tool record lookup. Currently, there is no logic to be applied with the beginning…

  • Successfully querying customer notes field BPCREM in BPCUSTOMER

    I'm trying to pull the customer notes into a spreadsheet along with delivery text information for a migration. Whenever I bring over this field , it spills over onto other lines and as a result when I export the data it is all choppy and screwy. I'm assuming…

  • Exporting queries and reducing confusion

    In X3 PU9 a query can be limited to show a limited number of total lines even if the query returns more.  In my example, 500 of 10245 results are shown.  If I export to Excel using the action menu on the left, I only get 500 rows.  If I export to Excel…

  • RE: Linking a Miscellaneous Table through SQL

    Hi Israel,

    I have yet another SQL question (sorry, we don't have a Crystal reports integration!). I want to use a subquery to extract the Average cost of each product by using the AVG aggregate, but I am unsuccessful in doing so. What I have so far is…

  • Linking a Miscellaneous Table through SQL

    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…

  • Query headings

    When you are in a query and you scroll down, you lose your headings.  any way around this other than showing less lines and using the right and left arrows?

  • RE: SQL - DateTime conversion error

    Hi Bob

    Thank you for the response. I used your outlined solution using Declare and Set operators, with the result:


    SET S.USRFLD1_0 = '01/01/2010'


  • SQL - DateTime conversion error


    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).