Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Electronic reporting (...
Finance forum
Suggested answer

Electronic reporting (calc. field)

Posted on by 5

Hi
i want to create a ER as   item , sls qty, prod qty, pur qty  etc
for this
i am taking the datasource as InventTrans and i want to get the vlaues of Sales , prod, purhcase etc based on transorigin.ReferenceCategory (for exmaple if categoy is zero it is sales)
i am trying to create a calculated field with below formula (where @ is the inventtrans table)

1.) my question is how to sum the qty based on different categories
2.) do i have to create different calc fields for Prod and purch etc.,
3) when iam trying to create Calc field like below iam getting error

`*

if(@.‘InventTransOrigin.ReferenceCategory’==0,@.Qty,0)

*`

Syntax error: We found a problem with this formula; syntax error near ‘(’

  • Suggested answer
    Alireza Eshaghzadeh Profile Picture
    Alireza Eshaghzadeh 10,873 Super User on at
    RE: Electronic reporting (calc. field)

    You can use following calculated field on InventTrans

    You need to define Enum for InventTransType in advanced!

    WHERE(

      InventTrans,

      InventTrans.'inventTransOrigin()'.ReferenceCategory = Enum.InventTransType.Sales)

    Then on the field mapping you can define that quantity comes from that calculated field.

  • Abdullah.Y Profile Picture
    Abdullah.Y 5 on at
    RE: Electronic reporting (calc. field)

    i want to create a ER report  from inventtrans Table

     item | sls qty |prod qty |purch qty|

    pastedimage1676279976843v1.png

    for this i selected the inventtrans table  as datasource 

    now for getting sales value ( for example)  iam trying to create a calc field  using if statement 

    if the ref category is sales  ( "0" or   InventTransType.Sales) ,   then the value should be the inventrans.Qty else 0

    but iam getting error

    pastedimage1676280036725v2.png

    pastedimage1676280121094v3.png

  • Suggested answer
    Alireza Eshaghzadeh Profile Picture
    Alireza Eshaghzadeh 10,873 Super User on at
    RE: Electronic reporting (calc. field)

    I have check InventTransOrigin table and Reference(ReferenceCategory) field is related to InventTransType as Enum

    pastedimage1676279167791v1.png

    What is your business requirement? 

  • Suggested answer
    Alireza Eshaghzadeh Profile Picture
    Alireza Eshaghzadeh 10,873 Super User on at
    RE: Electronic reporting (calc. field)

    Hi Abdullah,

    Can you provide more info about your model so we can assist better.

    Regarding the formula you cannot have to equals, IF(@.‘InventTransOrigin.ReferenceCategory’=0,@.Qty,0)

  • Abdullah.Y Profile Picture
    Abdullah.Y 5 on at
    RE: Electronic reporting (calc. field)

    actually i tried that also but stil the same syntax error

  • Suggested answer
    Arcadi  Profile Picture
    Arcadi 780 on at
    RE: Electronic reporting (calc. field)

    Reference Category is not a number, is an Enum, you have to insert the enum "InventTransType" and then:

    if(@.‘InventTransOrigin.ReferenceCategory’==InventTransType.Sales,@.Qty,0.0)

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,914 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,549 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans