Ranges in form for View fields

(0) Share

Report

Posted on by CU17071440-0

Hi,

I have a view which is a datasource of a form. Now I want to add ranges in execute query such that the lines which has Onorder =blank && physicalreserved = blank && total amount=0 and totallines=0. those lines should not show.

If any one of these 4 fields has value, that line should be shown

These fields are computed column in view. and the view is the datasource to the form.

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

You can't simply add ranges for the individual columns (e.g. OnOrder != ''), because they would be combined with AND instead of OR. You'd have create an expression on your own.

A better approach may be creating a computed column for the (boolean) value and simply filtering by this computed column.

Was this reply helpful? Yes No
Layan Jwei 8,097 Super User 2025 Season 2 on at

Like (0)

Report

Hi Community member,

Is this a customized form, where you created the view and form yourself? if yes, and you are saying that those 4 fields are all computed columns. Then you need to create a new view, which should contain the original view, and add the 4 ranges for the 4 computed columns individually (since you want all of them as &&). Then add this new view to the form instead.

Thanks,
Layan Jweihan
Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future

Was this reply helpful? Yes No
CU17071440-0 on at

Like (0)

Report

Hi Layan,

I tried this but the view ranges are taking all as Or condition and not the and condition. So it is not working for me

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

Like (0)

Report

Yes, adding a separate range for each field isn't a solution, as I already mentioned in the first reply. And I suggested two ways that actually solves the problem.

Was this reply helpful? Yes No
CU17071440-0 on at

Like (0)

Report

Hi Martin,

Can you explain this a more. I am not able to get it "A better approach may be creating a computed column for the (boolean) value and simply filtering by this computed column."

Thanks in advance

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

Like (0)

Report

Instead of writing the combined condition in a form, you could do it in a computed column. So there you'd have SQL code like 'T1.OnOrder <> 0 OR T1.PhysicalReserved <> 0 etc.). The result would be boolean column, let's call it HasValue. In the form, you'll filter by this HasValue column, which is trivial.

Was this reply helpful? Yes No
Suggested answer

Layan Jwei 8,097 Super User 2025 Season 2 on at

Like (0)

Report
Hi Community member,

normally when i add multiple ranges on a view, they appear as AND and not OR. Not sure if it behaves differently for computed columns.
For example, I have this view, and as you can see it has 2 different fields as a range. And the SQL output for the view treated them as AND

SELECT T1.ACCOUNTNUM, T1.ACCOUNTSTATEMENT, T1.BLOCKED, T1.CREATEDDATETIME AS CREATEDDATETIME1, T1.PAYMTERMID, T1.DATAAREAID, T1.PARTITION, T1.RECID, T2.CUSTGROUP, T2.NAME, T2.PAYMTERMID AS PAYMTERMID1, T2.DATAAREAID AS DATAAREAID#2, T2.PARTITION AS PARTITION#2 FROM dbo.CUSTTABLE AS T1 INNER JOIN dbo.CUSTGROUP AS T2 ON T1.CUSTGROUP = T2.CUSTGROUP AND T1.DATAAREAID = T2.DATAAREAID AND T1.PARTITION = T2.PARTITION WHERE (T1.TAXGROUP = N'STD') AND (T1.ACCOUNTNUM = N'11' )

But yes another way would be the computed column. You need to create a computed column, check this link if you don't know how to create it
https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/data-entity-computed-columns-virtual-fields#example-create-a-computed-field

and this would be the method you need for the computed column, then on the view, you need to set range of this computed column as 1 (not 100% sure of the syntax)
public static server str returnRecords() { //!(Table.Field1 == '' && table.Field2 == '' && table.field3 == 0 && table.field4 == 0) List andConditionList1 = new List(Types::String); andConditionList1.addEnd(SysComputedColumn::equalExpression(SysComputedColumn::comparisonField(tableStr(View1),identifierStr(Table1), fieldStr(Table1, Field1)), SysComputedColumn::comparisonLiteral(''))); andConditionList1.addEnd(SysComputedColumn::equalExpression(SysComputedColumn::comparisonField(tableStr(View1),identifierStr(Table1), fieldStr(Table1, Field2)), SysComputedColumn::comparisonLiteral(''))); andConditionList1.addEnd(SysComputedColumn::equalExpression(SysComputedColumn::comparisonField(tableStr(View1),identifierStr(Table1), fieldStr(Table1, Field3)), SysComputedColumn::comparisonLiteral(0))); andConditionList1.addEnd(SysComputedColumn::equalExpression(SysComputedColumn::comparisonField(tableStr(View1),identifierStr(Table1), fieldStr(Table1, Field4)), SysComputedColumn::comparisonLiteral(0))); return SysComputedColumn::if(SysComputedColumn::and(andConditionList1), SysComputedColumn::returnLiteral(0), SysComputedColumn::returnLiteral(1)); }

I think you might still need a new view when you create this computed column, where this new view will contain the original view. But first try to do it on the original view itself, it might work

Thanks,
Layan Jweihan
Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future

Was this reply helpful? Yes No
Verified answer

CU17071440-0 on at

Like (0)

Report

Hi,

(1) I created a Computedcolumnstring field (IsEmpty) in my view (StockSummary) and assigned the below method to it which calculates the total of the fields.

private static server str CalculateTotal()
{
StockSummary StockSummary;
str viewField, viewField1, viewField2, viewField3, viewField4 ;
DictView dictView;
str NoOfRecords;

dictView = new DictView(tableNum(StockSummary));

viewField = dictView.computedColumnString(tableStr(View1),fieldStr(View1, field),FieldNameGenerationMode::FieldList,true);
viewField1 = dictView.computedColumnString(tableStr(View2),fieldStr(View2, Field),FieldNameGenerationMode::FieldList,true);
viewField2 = dictView.computedColumnString(tableStr(View3),fieldStr(View3, Field),FieldNameGenerationMode::FieldList,true);
viewField3 = dictView.computedColumnString(tableStr(View4),fieldStr(View4, Field),FieldNameGenerationMode::FieldList,true);
viewField4 = dictView.computedColumnString(tableStr(View5),fieldStr(View5, Field),FieldNameGenerationMode::FieldList,true);

NoOfRecords = strFmt('count(%1) + count(%2) + count(%3) + count(%4) + count(%5)',viewField ,viewField1,viewField2,viewField3,viewField4);

return NoOfRecords;
}

(2) I created a new View and added the previous View to it as a datasource with the Range IsEmpty !=0.

(3) Added this new view of point(2) to my form datasource and it worked fine as I was expecting the records to filter out.

Was this reply helpful? Yes No
Layan Jwei 8,097 Super User 2025 Season 2 on at

Like (0)

Report

Hi Community member,

I'm glad your issue is fixed. But I think you should verify the answers that helped.

For example Martin suggested to create a computed column, so this is answer should be verified.

For me, i also suggested you to create a computed column. And I've mentioned in two replies that you need to create a new view and put it as a new datasource on form. And it seems you had to do that so i think it needs to be verified too.

Thanks,

Layan Jweihan

Was this reply helpful? Yes No