Okay
  Public Ticket #3653698
using Transform Value in a foreign key column
Closed

Comments

  •  1
    Alex started the conversation

    Hi..!

    The Transform Value feature is great. However, it could be improved in some aspects.

    It would be awesome if some advanced string manipulation was also possible, especially getting a substring out of a value.
    (A specified length counting from the beginning or end of string, or until hitting a specified character or string.)

    This is turning to a feature suggestion now, it seems... actually, what I was going to ask is the following.

    When working with a foreign key column, can you imagine any way (a hook or anything) to access the "store value" in addition to the "display value"..?
    There would be so many more possibilities if this was possible, in my case I'd like to construct a direct link to a master-detail page of another row.
    Yes, this would be a workaround for creating the "related" or "compatible with" functionality of which I've been putting some suggestions in earlier.
    Users would select the "display value", but the "store value" should be also passed forward along with it, in such a way that it could be used in the Transform Value column.

    Probably there are several quite complex ways to achieve this, I'm going to need some SQL triggers anyway, but accessing the "store value" directly somehow would be such a simple solution.

    Thanks,
    Alex

  •  1,729
    Miloš replied


    Hi Alex,

    Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. 

    I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.


    I am not sure if we fully understood what you wish to achieve.

    When it comes to the current 'out of the box' logic how our foreign key relation works;

    As you know, this only works for SQL based Tables and we need to set the Column to be stored in the Database as integer data type.

    So, the 'store value' is recommended to be picked as a unique integer Column from the foreign table since our foreign key relation can only be 1 to 1, we don't have the posibility for one to many or many to many relations with it.

    For example, if it picks up multiple same 'store values' as "id", then only the first one gets picked.

    Such as, if we have multiple 'names' as strings in the foreign table, like "John" as id 1 and also Anne as id 1;

    it will just pull up "John" for the "one value selectbox" for editing in our current SQL or Manual Table if we are editing a row.

    -

    So when you say 'access the store value and the displayed value at the same time', our Plugin is technically already doing that, but we must chose what will be the stored value and what is the displayed value.

    The stored one is saved as integer ( id) in the Database, while we choose the displayed value, as explained on the Documentation.

    -

    If you mean to somehow 'combine' or 'contatenate' displaying both store value ( for example if we pick some ID column as 'store') and to add the 'display value' via Transform Value, with all this being in the same cell, you are right, at the moment we don't have any solution to achieve something like that.

    -

    The only workaround is to make two foreign key columns, so in an additional column in this table as foreign key relation,  you can choose, let's say "ID" as 'store value' and also "ID" as displayed value;

    then in another Foreign Key Column you can choose, let's say "ID" as 'store value' and 'name' as 'displayed value' and in the Transform Value you can try concatenating them with 'column 1 placeholder' and 'column 2 placeholder' to combine them in the same cell and see if that works.

    Other than that, at the moment I don't have any other idea.

    -

    You can, of course, suggest your idea to our developers, though, describe what they need to add - they will do their best to make a solution in the future.

    Please feel free to search on our suggestions page

     to see if someone may be already suggested this feature. If you can't see it, feel free to add your suggestion there,  and as more people vote, the feature will move higher on the priority list.

    You can certainly follow our changeLog page if you'd like ( it is also available in the plugin dashboard), where we state any changes/new features/bug fixes during updates;

    and our newsletter, so you're informed about new features, bug fixes, freebies, etc.

    -

    If you have coding skills and wish to try to make a custom solution now,

    you can check out our available hooks for Developers on this documentation and see if you can find any hook that might help.

    Please be advised that custom solutions with hooks are not included in our support.

    You can also research resources such as Stack Overflow to see if any other user perhaps found a workaround.

    ( We do like to give examples for certain solutions, but for this use-case, we, unfortunately, don't have anything yet)


    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

  •  1
    Alex replied

    Hello Miloš,

    thank you for a comprehensive reply again, I thought I probably should also give a detailed example showing simply what I'd like to achieve, although you apparently understood the idea.

    No need for saving multiple values just yet, but it could be done by just having several colums dedicated for the same purpose, although not an optimal solution but at least makes handling and later editing the values way easier.

    Let's say I have a foreign table which stores only product names and corresponding id's for them.

    In the main table, I have a foreign key column named "compatible with".
    In that column I can choose any product name from the foreign table. Let's say I choose one having display value "Product-X" and store value (id) "24".

    Now in the Transform value settings of "compatible with" column, I'd like to have something like the following, to construct a link for the master-detail page of the chosen product:

    HREF=examplesite.com/product/?wdt_md_p_t_id=1&wdt_md_p_t_col_name=wdt_ID&wdt_md_col_value={compatiblewith.storevalue}>{compatiblewith.value}<
    (I couldn't figure out how to add html without it being rendered so it's a bit pseudo code now, but I think you get the idea anyway)

    In my example the id is used for matching a product in a wpDataTable and making a direct link to it, but basically the target link could be almost anything referencing an item with an integer id, WooCommerce for example.

    And to make all this convenient for the users (this is for a collaborative project), of course they need to see display values and not id's to choose from, hence the foreign key column. The id's mean nothing to them, and belong solely to the technical side of things.
    However in my opinion both values should definitely be available for use in the Transform value settings, to make the feature really really useful, as I hopefully presented in this example.

    So, the only problem here is, it seems currently only the display value can be accessed this way. That's why I was really hoping there would be a way to access also the store value.

    I do have some coding skills, but try to avoid any custom code, because as you said there's no support for that and also because the updates most likely break any custom solutions. Also I'm not familiar with the hooks yet, I'd need to see some actual examples how and especially where they could be used.

    And about the workaround you suggested, yes I already thought it would be a solution to have the id and display value in separate columns. However, it's not an option they both should be selected manually, that's an unnecessary extra step, and the users of the table certainly are only familiar with the display values.
    I could write an SQL trigger to automatically store the value in a hidden extra column though, and that's probably how I have to do it for now... but at least if (when) I would have multiple "compatible with" and such columns as I plan to do, that makes things overly complicated, as opposed to simply having access to both values directly in Transform value.

    I probably should make a feature suggestion then, although I'm not sure if this could even be regarded as an actual feature.

  •  1,729
    Miloš replied

    Hi Alex,

    Thank you for this clarification in more details and your example scenario.

    It certainly helped to further understand the goal.


    To summarize, I can see what you mean, but at the moment we don't have any workaround to achieve anything similar to that, such as the way to access both the store id and the displayed value as you described;

    or a workaround to somehow 'auto populate', let'say a row, if a user picks some 'displayed value' from the selectBox list during editing, and then to 'link' another column which would somehow automatically pull the corresponding 'id/store value' from the foreign Table.

    We did have a similar 'feature suggestion' before, such as auto-populating two columns from a foreign table without manually' matching' which one is the 'id/store value', and which automatically goes along to that 'displayed value' of the same row, and so on.

    In other words, all of that needs to be manually input right now and we can only have 1 to 1 relations.


    What you described here is slightly different, so I believe it is best if you can describe it as clearly as possible when creating a new Feature Suggestion  on our suggestions page.

    I know that technically we just named it as 'feature suggestions', but there you can write any kind of improvement or additional option you would like to see in the future.

    We just can't promise an ETA on it, but our devs will do their best.


    For the time being, you are right, if you make some custom solution and customize our Plugin's code, that would get overriden during an update;

    Only if you use custom CSS and JS, that won't get overriden, if you add it to the Customize/Custom CSS of a table or Main settings/Custom JS and CSS/Custom CSS ( or JS there);

    but if you edit any PHP file of the Plugin, JS file of the actual Plugin files via FTP, yes, that would be overriden during updates.



    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

  •  1
    Alex replied

    Thank you very much Miloš,

    a new improvement suggestion added:

    https://features.wpdatatables.com/447

    Kind regards,
    Alex

  •  1,729
    Miloš replied

    Hi Alex,

    Thank you for writing up the suggestion and going in details to clarify it.

    Our QA Team and the developers will go through all the details, then our management will consider it, and they will carefully handle all the priorities for developer tasks based on how many users request each improvement/feature and so on.

    We can't promise an ETA if or when this will become possible, but we will do our best to try to make a solution as soon as possible.


    Thanks again.

    If anything new comes up, please don't hesitate to open new tickets.


    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables