Search and get table name with parameter table name and have data inside. SQL

this query is useful to check table name with data inside after running the class SysDatabaseTransDelete.

i put another parameter table name, for only search only customize table on AX with Developer identifier.

preview :
Capture

code below

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'TID%'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF 
	(@table_name LIKE @search_string)
	BEGIN
		SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ')'+ 'PRINT ''' + @table_name +''''
		EXECUTE(@sql_string)
	END
    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur
Advertisements

Join ItemName from (EcoResProductTranslation ) and search name from InventTable to OnHand Form for search matter

it is common for user to search item based on name. but currently on AX 2012, onHand form can’t provide search based on item name or search name. It will take a little modification from technical side.

example how to do it :

1. you can join directly on form datasource or create view like example (i’m using the view that already created for another purpose)
Capture

2. on InventSumTable add relations like this.
Capture

3. then go to form onHand (InventOnHandItem), and add the view to datasource
Capture

4. open class InventDimCtrl_Frm_OnHand and modify method modifyQuery , on class declaration parameter, add new parameter FormDataSource with default null parameter. then define new variable QueryBuildDataSource.
Capture

5. on the last line of the method modifyQuery , add code like example :

if(_tidInventLookUp)
{
    qbsInventLookup = query.dataSourceName(_tidInventLookUp.name());

    //filter current company language
    qbr = SysQuery::findOrCreateRange(qbsInventLookup, fieldnum(TIDInventLookup, LanguageId));
    qbr.value(queryValue(CompanyInfo::languageId()));

    qbsInventLookup.addGroupByField(fieldNum(TIDInventLookup,Name));
    qbsInventLookup.addGroupByField(fieldNum(TIDInventLookup,NameAlias));

}

5. then back to the form, on datasource inventSUm, on executeQuery , modify the code from

void executeQuery()
{
    element.inventDimSetupObject().modifyQuery(inventSum_DS,inventDim_DS);

    super();
}

to

void executeQuery()
{
    element.inventDimSetupObject().modifyQuery(inventSum_DS,inventDim_DS,TIDInventLookUp_ds);

    super();
}

6. drag the field from newly add datasource on form to the grid :
Capture

7. Voila done, example result, :
Capture

note: remember to GROUPBY on code if you add new field to the form, or the field on form will show “UNRETRIEVED”