Lookups and Filters

  • Resolved measy

    (@measy)


    2 years, 9 months ago

    Hi Peter,
    having solved my previous issue with dynamic Project-page WHERE conditions, I’m now facing with a similar problem:
    referencing the standard Dept/Emp model, assume you need to manage Sales_Orders:
    – create a custom table SalesOrder (say SO), SO.columnA being Deptno, SO.columnB being OrderMgr, SO.columnC being OrderNo, etc..
    – create a projectTemplate/page to transact on SO;
    – define a relation on it, so that columnB (Order Manager name) get selected by an LOV on EMP.

    Now assume you need to filter columnB values by $USER$ (each Dept manage its SalesOrder).

    My idea is to create a custom table grantedDepts where you store, by manually copying usernames from Usermeta and granted Deptnos (say max 3); thus:
    —————–
    userA, 10, 20
    —————–
    userB, 10, 20, 40
    —————–
    userC, 10
    —————–
    I then create an LOV which joins EMP, DEPT and grantedDepts, so that an username is linked to Deptnos, linked to Emps assigned to those Deptnos.

    But: how to filter this LOV values per $USER$ value?
    Conceptually, a WHERE condition for LOVs, similar to the template/page ones, on the template Relationship WPDA tab would be perfect..

    This is a common requirement: data fully partitioned per User (userA only CRUD salesOrder for Dept=10 and Dept=20, userC only for Dept=10).

    Any suggestion would be more than appreciated.

    Thanks in advance,
    Marco

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Marco,

    I understand your need to filter a LOV with user based data, but this is currently not possible. The database connection just does not know anything about the WordPress user.

    You cannot add a custom where clause to your LOV directly from Data Projects (which would solve the issue). But you can create a view, use that view for your LOV and add a filter to the view. You still will not be able to filter on WordPress user info in your view, but maybe your application allows another way to filter…?

    Does this help?
    Peter

    Thread Starter measy

    (@measy)

    Hi Peter,
    first of all, thanks a lot for your reply.

    I see your point and I’ll look for other ways to filter data, in case.

    Anyway, the more I test your plugin the more I find it great!
    So, I had a look at Code Manager stuff, but – not being a programmer – I’m not able to investigate further before quitting…
    I agree with you: when I define my view, I cannot refer to $$USER$$.

    But, our fantastic page-WHERE condition, deptNo being a column in my base table:
    exists (select null from myGrantedDepts where (dept1 = deptNo or dept2 = deptNo or dept3 = deptNo ) and grantedUser=$$USER$$)
    works perfectly.

    I understand WPDA run the query on my base table by adding the WHERE condition at run-time, when $￡USER$$ is defined and valued.

    If I only could write for my LOV (somewhere in my template/page) something like the above filter, we could fix the issue, giving complete consistency to data partitioning.
    Am I wrong?
    Marco

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Marco,

    >>> Am I wrong?

    You are absolutely right! ??

    But that filter needs to be added to the lookup in your template, which requires a plugin update to allow users to enter the filter and the plugin to use it. At this moment there is so much on my desk that I just don’t have the time to do it now… Sorry!

    Best regards,
    Peter

    Thread Starter measy

    (@measy)

    Hi Peter,
    first of all, let me say I’m proud not to be wrong ??

    >>> I just don’t have the time to do it now…
    I didn’t expect any immediate fix, really.
    I just ment to suggest you the general argument (say, data privacy), for the benefit of your plugin.

    Thanks for your time and attention.

    Best regards,
    Marco

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Marco,

    >>> I just ment to suggest you the general argument (say, data privacy), for the benefit of your plugin.

    Absolutely! ??

    That’s why it is on my to do list now! ?? I just can’t promise a deadline…

    >>> Thanks for your time and attention.

    You’re welcome!

    Thank you for your change request,
    Peter

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Lookups and Filters’ is closed to new replies.