get filter value from grid

example code :

public void getQueryValue()
{
    QueryBuildRange range;
    int ct, i;

    ct = PMTRoutingTable_ds.queryRun().query().dataSourceTable(tablenum(PMTROutingTable)).rangeCount();

    for (i=1 ; i<=ct; i++)
    {
        range = PMTRoutingTable_ds.queryRun().query().dataSourceTable(tablenum(PMTROutingTable)).range(i);
        info(strfmt('Range Field – %1, Value – %2',range.AOTname(),range.value()));
    }
}

references :
https://jkmsdax2012.wordpress.com/2015/05/05/get-the-ranges-and-values-from-query-dynamicaot-using-x-in-ax-2009/

Advertisements

Shrink database SQL Query

Shrink database data.

USE DatabaseName;
GO
-- Shrink the truncated file to 1024MB.
DBCC SHRINKFILE (DataBaseFileName, 1024);
GO

Shrink database log.

USE DatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DatabaseFileLogName, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO

Reindex All Table On Database SQL Query

Script to re-index All Table On Database.

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases   
WHERE name IN ('DATABASENAME')   
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES 
  WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

		Print  ('ALTER INDEX ALL ON ' + @Table)
		SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
		EXEC (@cmd) 

       FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

add group by and ranges to Queries Forms example code

on form class declaration

public class FormRun extends ObjectRun
{
    QueryBuildRange             indentTransDateRange;
    QueryBuildRange             invoiceDateRange;

    QueryBuildRange             customerRange;
    QueryBuildRange             storeRange;
}

on init form method

public void init()
{
    super();

    indentTransDateRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldnum(TIDIndentOrderUninvoiceTrans, PaymentTransDate));

    invoiceDateRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldnum(TIDIndentOrderUninvoiceTrans, InvoicePostingDate));

    customerRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldnum(TIDIndentOrderUninvoiceTrans, CustAccount));

    storeRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tableNum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldNum(TIDIndentOrderUninvoiceTrans, Store));

    TransactionDate.dateValue(systemdateGet());
}

on execute Query datasource form

public void executeQuery()
{
    this.Query().clearGroupBy();

    indentTransDateRange.value(strFmt('<%1',(TransactionDate.dateValue() + 1)));
    invoiceDateRange.value(strFmt('>%1',TransactionDate.dateValue()));

    customerRange.value(strFmt(customer.text()+'*'));
    storeRange.value(strFmt(StoreId.text()+'*'));

    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,Store));
    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,CustAccount));
    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,SalesId));
    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,GrossAmount));
    this.Query().DataSourceNo(1).addSelectionField(fieldNum(TIDIndentOrderUninvoiceTrans,SumOfamountTendered),SelectionField::Sum);
    this.Query().DataSourceNo(1).addSelectionField(fieldNum(TIDIndentOrderUninvoiceTrans,OutstandingAmount),SelectionField::Sum);

    super();
}

Capture

error: The data source is not embedded within a (parent) data source.

This error will occur when you add new dataSource with function “addDataSource(tableNum(MyTable))” but not specify which it’s parent datasource

so example code before :

qbsInventDim = _query.addDataSource(tableNum(WMSLocation));

code after :

qbsInventDim = _query.dataSourceTable(tableNum(InventDim)).addDataSource(tableNum(WMSLocation))