Ranges, Datasources and Grids

Hi, I have a form with a few grids all linked to a different datasource. I am finding it difficult to add ranges to datasource of a grid that displays all records excluding those that have FieldA and FieldB and FieldC = True. I have used ranges as follows : qbDS.addRange(fieldNum(Ineterviews, ConfirmedA)).value("!" +NoYes::Yes); qbDS.addRange(fieldNum(Ineterviews, ConfirmedB)).value("!" +NoYes::Yes); qbDS.addRange(fieldNum(Ineterviews, LetterSent)).value("!" + NoYes::Yes); But it doesnt seem to apply it to the grid. What do i need to add after the ranges to apply it to the grid so it only shows those records i that fit the criteria above. Thanks Theo.

What’s with the not Yes. Why not write No?? like .value(queryValue(NoYes::No)); and maybe your fields are not on the qbDS datasource.?? how do you select the datasource into qbDS variable? qbDS = query.dataSourceTable(tableNum(Ineterviews)) ??

Hi Ivan, I am trying to display all records that dont have 3 fields poulated. If all three 3 fields equal Yes then i dont want them displayed. Yes i have done qbDS = query.dataSourceTable(tableNum(Ineterviews)) I think my problem is in the ranges im trying to set.

well, than. If you need to select all records except the ones, where all three fields are NoYes::Yes, then you should go from the other direction and select the fields where at least something is NoYes::No. :slight_smile: (that’s the same thing) and your query should look something like this: qbDS.addRange(fieldNum(Ineterviews, ConfirmedA)).value( strFmt(’((%1 == %4) || (%2 == %4) || (%2 == %4))’, fieldStr(Ineterviews,ConfirmedA), fieldStr(Ineterviews,ConfirmedD), fieldStr(Ineterviews,LetterSent), queryValue(NoYes::No)));

Hi Ivan, I eventually figured out “Expressions in Query ranges” today, your post reassured me i was on the right track … i have made my code look like yours please see below :- qBDS.addRange(fieldnum(MPBInterviews,ConfirmedDR)).value (strFmt(’((%1 == %4) || (%2 == %4) || (%2 == %4))’, fieldStr(MPBInterviews,ConfirmedDR), fieldStr(MPBInterviews,ConfirmedABM), fieldStr(MPBInterviews,InterviewLetter), queryValue(NoYes::No))); But i get an error at runtime “unable to parse the value”. Thanks for your help so far its been great.

hmm. Weird. Well, the best thing I can think of is: try changing the amount of brackets, because they have always been a problem for the parser. Try removing the outer brackets or add on another pair. Try the same with the inner brackets. Or try substituting == with = or adding extra brackets around (%4) And, if none of that helps, try adding the name of the table for each of the == like this: %5.%1 and the fifth parameter for strfmt() would be qbDS.name() But it is supposed to work without that if all the fields are in the same table.

I am sorry I can’t answer you with a real sample of code, I don’t have Axapta under hand, so can’t test the string myself

Hi Ivan Did what you said and didnt work I did the following and it worked (strFmt(’(%1 != %2)’, fieldStr(MPBInterviews,ConfirmedDR), fieldStr(MPBInterviews,ConfirmedABM), fieldStr(MPBInterviews,InterviewLetter), queryValue(NoYes::No))); Therefore, there might be a problem when (ConfirmedDR == No) What do you think can it have something to do with the fact that the fields are enums NoYes. Theo

Hey, ragsy_theo I think here is your solution. I just tried it in SQL You have to put %4 into “%4” :slight_smile:

No, the problem was not in the fact that they were enums. It’s just that I forgot that you have to include “” if you are specifying a value, not another field

Thanks Ivan It worked and im in runtime … now the question is where do i place this code in the datasource … currently its in the init() is this the best place for it ? The code in full looks like this Query q = new Query(); QueryBuildDataSource qBDS; QueryBuildRange qBR; Str queryExpression; ; super(); qBDS = q.addDataSource (TableNum(MPBInterviews)); qBDS.addRange(fieldnum(MPBInterviews,ConfirmedDR)).value (strFmt(’((%1 == “%4”) || (%2 == “%4”) || (%3 == “%4”))’, fieldStr(MPBInterviews,ConfirmedDR), fieldStr(MPBInterviews,ConfirmedABM), fieldStr(MPBInterviews,InterviewLetter), queryValue(NoYes::No))); info(qBDS.toString()); this.query(q); Theo

well, actually Best Practice tells us to set range values in ExecuteQuery(). so all the code stays in the init method, and in executequery() you write this.query().Datasourcetable(tableNum(MPBInterviews)).findRange(fieldNum(ConfirmedDR)).value(strfrm(…)); but I just checked it too, it seems to work only if I specify 0 in place of %4 (integer, i mean). So I tried a couple of other things. Here is the final version. :slight_smile: (I hope) qbDS.addRange(fieldNum(MPBInterviews, ConfirmedDR)).value(strfmt(’(%1 == %4) || (%2 == %4) || (%3 == %4)’, fieldStr(MPBInterviews, ConfirmedDR), fieldStr(MPBInterviews, ConfirmedABM), fieldStr(MPBInterviews, InterviewLetter), str2int(enum2str(NoYes::No)))); I guess there is a problem with enums… I will have to research more into it.

or even better: int2str(NoYes::NO)

Ivan… thank you sooo much Just for the rest of the forum users the final code looks like public void init() { Query q = new Query(); QueryBuildDataSource qBDS; QueryBuildRange qBR; Str queryExpression; ; super(); qBDS = q.addDataSource(TableNum(MPBInterviews)); qBDS.addRange(fieldnum(MPBInterviews,ConfirmedDR)).value (strFmt(’((%1 == %4) || (%2 == %4) || (%3 == %4))’, fieldStr(MPBInterviews,ConfirmedDR), fieldStr(MPBInterviews,ConfirmedABM), fieldStr(MPBInterviews,InterviewLetter), int2str(NoYes::NO))); //info(qBDS.toString()); // Displays SQL this.query(q); } public void executeQuery() { this.query().DataSourceTable(tableNum(MPBInterviews)).findRange(fieldNum(MPBInterviews,ConfirmedDR)).value (strFmt(’((%1 == %4) || (%2 == %4) || (%3 == %4))’, fieldStr(MPBInterviews,ConfirmedDR), fieldStr(MPBInterviews,ConfirmedABM), fieldStr(MPBInterviews,InterviewLetter), int2str(NoYes::NO))); super(); } Thanks again … Theo.

actually you are supposed to write the super() call in executeQuery before you modify it. Otherwise it will erase all your changes where the super method is called. (for example, if the user changes the the filter on the form)