Finance | Project Operations, Human Resources, ...

Add condition to fieldMapping for Query::insert_recordSet

(1) Share

Report

Posted on by AbdullahAhmed_

513

I'm using Query::insert_recordset to insert from 2 different qbds and here's the code:

**Code example is updated

	
    	
    	Query                       q;
    	QueryBuildDataSource        qbds;
    	QueryBuildDataSource        qbds_UserRange;
    	QueryBuildRange             qbr;
    	QueryBuildFieldList         qbfl;
    	QueryBuildFieldList         qbfl_UserRange;
    	QueryRun                    qr;
    	Map                         fieldMapping;	

        q               = new Query();
        qbds            = q.addDataSource((tableNum(SalesTable));
        qbds_UserRange  = q.addDataSource(tableNum(UserRangesTable));

	qbds.addSelectionField(fieldNum(SalesTable,SalesId));
	qbds_UserRange.addGroupByAndSelectionField(fieldNum(UserRangesTable,UserId));

        qbfl            = qbds.fields();
        qbfl_UserRange  = qbds_UserRange.fields();

        qbfl.dynamic(QueryFieldListDynamic::No);
        qbfl_UserRange.dynamic(QueryFieldListDynamic::No);

        qbds.clearRanges();
	qbds.addRange(fieldNum(SalesTable,CustGroup)).value('Free Cust');

        qbds_UserRange.clearRanges();
        qbds_UserRange.addRange(fieldNum(UserRangesTable,UserId)).value('TestUser');

	fieldMapping = new Map(Types::String,Types::Container);
	fieldMapping.insert(fieldStr(XDSSalesTableRegular,SalesId)   ,[qbds.uniqueId(),fieldStr(SalesTable,SalesId)]);
        fieldMapping.insert(fieldStr(XDSSalesTableRegular,UserId),[qbds_UserRange.uniqueId(),fieldStr(UserRangesTable,UserId)]);
        Query::insert_recordset(xDSSalesTableRegular,fieldMapping,q);

The result from this query is set of records of SalesId from SalesTable depending on ranges I passed and only 1record UserId from UserRangesTable...

I want the query to insert for every SalesId a UserId, but this doesn't happen and it only inserts SalesId!

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Mohamed Amine Mahmoudi 26,794 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @AbdullahAhmed_

You must add selection field.

qbds.addSelectionField(fieldNum(SalesTable, SalesId)); qbds_UserRange.addSelectionField(fieldNum(XDSUserRangesTable, UserId));

Best regards,

Mohamed Amine MAHMOUDI

Was this reply helpful? Yes No
AbdullahAhmed_ 513 on at

Like (0)

Report
Copy link

Link copied!

Thank you but this doesn't change anything!

Was this reply helpful? Yes No
Martin Dráb 238,828 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Your code defines a query called q, but then you ignore it and use userQuery instead. I'm assuming you wanted to use q, therefore you have a bug there.

Was this reply helpful? Yes No
AbdullahAhmed_ 513 on at

Like (0)

Report
Copy link

Link copied!

Sorry for that.. this code isn't in one function, I have a method that returns q and then I use it.

I added an example at the end of my question of exactly what I wanted to do.

Was this reply helpful? Yes No
Martin Dráb 238,828 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

It'll be helpful if you prepared simplified source code and share it with us, because reviewing some other code that you're actually using for testing doesn't make a good sense.

Was this reply helpful? Yes No
AbdullahAhmed_ 513 on at

Like (0)

Report
Copy link

Link copied!

Thank you, Martin.. I updated the code and I hope my idea now is clear.

Was this reply helpful? Yes No
Martin Dráb 238,828 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Let me start by refactoring your code, because simpler code is easier to understand and work with.
Query q = new Query(); QueryBuildDataSourcesalesTableQbds = q.addDataSource(tableNum(SalesTable); salesTableQbds.fields().dynamic(QueryFieldListDynamic::No); salesTableQbds.addSelectionField(fieldNum(SalesTable, SalesId)); salesTableQbds.addRange(fieldNum(SalesTable, CustGroup)).value(queryValue('Free Cust')); QueryBuildDataSource userRangeQbds = q.addDataSource(tableNum(UserRangesTable)); userRangeFields = userRangeQbds.fields().dynamic(QueryFieldListDynamic::No); userRangeQbds.addGroupByAndSelectionField(fieldNum(UserRangesTable, UserId)); userRangeQbds.addRange(fieldNum(UserRangesTable, UserId)).value(queryValue('TestUser')); Map fieldMapping = new Map(Types::String,Types::Container); fieldMapping.insert(fieldStr(XDSSalesTableRegular, SalesId), [salesTableQbds.uniqueId(), fieldStr(SalesTable, SalesId)]); fieldMapping.insert(fieldStr(XDSSalesTableRegular, UserId),[userRangeQbds.uniqueId(), fieldStr(UserRangesTable, UserId)]); Query::insert_recordset(xDSSalesTableRegular, fieldMapping, q);

The first suspicious thing I see is that you added two root data sources; the UserRangesTable isn't related to the SalesTable data source in any way. Is it really what you wanted? We can't know because you've never explained your intentions. You should provide some context next time.

Was this reply helpful? Yes No
AbdullahAhmed_ 513 on at

Like (0)

Report
Copy link

Link copied!
My intensions is to get records from SalesTable that meets the range value and insert SalesId from these records into XDSSalesTableRegular along with UserId which is a fixed value..

This idea could be done using insert_recordSet like this:

str User = 'TestUser'; salesTable salesTable; xDSSalesTableRegular xDSSalesTableRegular; insert_recordset xDSSalesTableRegular(SalesId,UserGroupId) select SalesId,User from salesTable group by SalesId where salesTable.CustGroup == 'Free cust';

and it returns the desired result... but I wanna use Query::insert_recordSet for other reasons like the range value is dynamic and I could join many ranges...

Was this reply helpful? Yes No
Martin Dráb 238,828 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

The query in your insert_recordset fetches data from SalesTable only. There is no UserRangesTable, therefore including it in the other code goes against your own requirements. Remove all code about UserRangesTable data source and add mapping for SalesTable.UserGroupId.

Was this reply helpful? Yes No
Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report
Copy link

Link copied!

To make your query work, you either need to make a join in your existing query. Let's say you have two datasources. You added both the datasources to the query. It doesn't work that way. You need to specify join.

After seeing your insert record set code example, the user value is simply hard coded. In that case if you are expecting many more ranges, then you need to introduce may be another table like user related table and perform a join and pass the value as a range. And also in your actual query, you need to add the user datasource to SalesTable as a join.

Was this reply helpful? Yes No