Filtering report with Setfilter/Navision Expert Needed

zulqzulq Member Posts: 204
I am developing a report which shows employees who've not paid their contributions. All employees are in the employee table and all contributions are in a contributions table. Below is what I've done so far:
Contributions
Employee (indented).

Now my problem is when I try to filter employees who are not in the contributions table the filter doesn't work.
Here's my filter and I tried placing it on both PreDataItem and AfterGetRecord
Employee.SETFILTER("No.",'<>Contributions."Employee No.");
I also tried this but gets an error:
SETFILTER("No.",'<>',Contributions."Employee No.");

I've tried several times altering the filter and even using setrange to no avail.
Any Ideas please!




Also our company is looking for a navision expert who will be working for us as consultant/developer. Interested individuals can send me a private message. ](*,)
Few years ago we were not existing and few years to come we would be in the grave! So what will benefit us in the grave?

Comments

  • AlbertvhAlbertvh Member Posts: 516
    Hi Zulq,

    Better to reverse the dataitems

    Employee
    ->Contributions

    on contribution properties DataItemLinkReference Employee No.=FIELD(No.)


    Hope this helps :)
  • sggsgg Member Posts: 109
    If what you want is the List of Employees, then you could do the following:

    Employee (DataItem)
    ContribRec (Variable SubType = Record, Value = Contributions Table)

    OnAfterGetRecord trigger of Employee Dataitem

    ContribRec.SETRANGE("Employee No.",Employee."No.");
    .... Other Filters
    ....
    ....

    IF NOT ContribRec.Find('-') then
    CurrReport.SKIP;



    Or you Could Create a FlowField (ContribValue say) that contains the Value Contributed by each Employee.

    Set Filter on the Employee DataItem for the FlowFilters related to the FlowField (ContribValue)

    OnAfterGetRecord trigger of Employee Dataitem

    Employee.CALCFIELDS(ContribValue);
    IF (Employee.ContribValue = 0) then
    CurrReport.SKIP;
    Sunday, Godwin G
  • zulqzulq Member Posts: 204
    Thanks Albertvh and sgg but your methods ain't working.
    Let me explain more- The report I want should list all employees except the ones in the contributions table. The unique key linking the employee and contributions tables are "No." and "Employee No." respectively.
    For example if it was mysql i would just say:
    Select * from employee where employee.No. != Contributions.'Employe No.'

    NB: I've tried sgg's method only as Albertvh's response obviously will not work in this scenario.
    Few years ago we were not existing and few years to come we would be in the grave! So what will benefit us in the grave?
  • AlbertvhAlbertvh Member Posts: 516
    Hi

    Use sgg method but change the code

    IF NOT ContribRec.Find('-') then
    CurrReport.SKIP;

    to

    IF ContribRec.Find('-') then
    CurrReport.SKIP;
  • zulqzulq Member Posts: 204
    Thanks a lot....Albertvh and sgg.....
    My problem is solved. \:D/ \:D/ \:D/
    Few years ago we were not existing and few years to come we would be in the grave! So what will benefit us in the grave?
  • vikram7_dabasvikram7_dabas Member Posts: 611
    u can do this with only one Dataitem named As employee put the field As Contribution code and relate it with Contribution table by using table relation ok.then on the properties of Employee Dataitem Select Dataitemtableview and in key firstly make the key named "Contribution code" and in table filter select the contribution code in field tab selct filter and in the value tab write this symbol (' ') then apply it.oku'll find the right answer.
    Now tell me that u told that u require navision expert please teel me ur personal mail idand mail it on my personal id i.e.,vikram7_dabas@yahoo.co.in,vikram.dabas@gmail.com and my mobile no. is 09355712543.i am from india.
    Vikram Dabas
    Navision Technical Consultant
  • vikram7_dabasvikram7_dabas Member Posts: 611
    zulq wrote:
    I am developing a report which shows employees who've not paid their contributions. All employees are in the employee table and all contributions are in a contributions table. Below is what I've done so far:
    Contributions
    Employee (indented).

    Now my problem is when I try to filter employees who are not in the contributions table the filter doesn't work.
    Here's my filter and I tried placing it on both PreDataItem and AfterGetRecord
    Employee.SETFILTER("No.",'<>Contributions."Employee No.");
    I also tried this but gets an error:
    SETFILTER("No.",'<>',Contributions."Employee No.");

    I've tried several times altering the filter and even using setrange to no avail.
    Any Ideas please!




    Also our company is looking for a navision expert who will be working for us as consultant/developer. Interested individuals can send me a private message. ](*,)
    tell me that how i can join ur company.i am Navision tehnical consultant in india
    Vikram Dabas
    Navision Technical Consultant
  • lubostlubost Member Posts: 628
    Change structure of your report in a way as Albertvh wrote.
Sign In or Register to comment.