Thursday, September 15, 2011

Add complex conditions in AOT Query in Dynamics X++

Sometimes you need to add complex conditions in your query to acheive the requirement. Below example will help you to achieve this


So i want to add these condition on query
BudgetSourceTracking.Category == BudgetControlCategory::Original || ( BudgetSourceTracking.Category == BudgetControlCategory::CarryForwardBudget && BudgetSourceTracking.Status == BudgetSourceTrackingStatus::Confirmed) || ( BudgetSourceTracking.Category != BudgetControlCategory::Original && BudgetSourceTracking.Category != includeCarryForward)
----
rangeCrtiteria is any string data type, you notice that i have added the condition of Status field too, but its alos working in this case
 rangeCriteria = strfmt('((((%1.%2 == %3) || ((%1.%2 == %4) && %7)) && (%1.%5 == %6)) || ((%1.%2 != %3) && (%1.%2 != %4)))',
                                    query.dataSourceTable(tablenum(BudgetSourceTracking)).name(),
                                    fieldstr(BudgetSourceTracking,Category),
                                    any2int(BudgetControlCategory::Original),
                                    any2int(BudgetControlCategory::CarryForwardBudget),
                                    fieldstr(BudgetSourceTracking,Status),
                                    any2int(BudgetSourceTrackingStatus::Confirmed),
                                    any2int(includeCarryForward));
       
query.dataSourceTable(tablenum(BudgetSourceTracking), 1).addRange(fieldnum(BudgetSourceTracking, Category)).value(rangeCriteria);
       
queryRun = new QueryRun(query);

No comments:

Post a Comment