[Resolved] Displaying distinct values only

[Author]

Posts

[Christian Cox]

Sorry, I think I misunderstood what you meant by saving to a mySQL view. I thought you meant that you would run a query on the existing table to select unique results. Then, you would use the results of that query to programmatically generate new custom posts in a custom post type, like "Clients". This is the only way you would be able to see Clients as a data source in the Content Selection image you shared above. This area represents different Post Types. Therefore, you would be required to create a custom post type called "Clients" in Toolset Types, then use your custom query to generate new Client Posts using the information from each unique Acuity Field Post.

Here is another idea I just considered. Toolset Views are designed to display Post Types, Taxonomies, or Users. You can filter those items by ID. So if you write a custom shortcode that returns a list of comma-separated IDs using your custom query, you can use it as a shortcode filter. For instance, if you write a custom shortcode "unique_clients" that uses your custom query to return a list like "1,2,3,4" where each number represents a unique Acuity Field ID, then you can pass that list of unique IDs into a View of Acuity Fields to use as an ID filter:

[wpv-view name='your-acuity-field-view-slug' ids="[unique_clients]"]

Then in your View settings, you can apply a Query Filter that filters Acuity Fields by ID, based on the shortcode attribute "ids". This is called passing arguments into a View, and we have more info about it here:
https://toolset.com/documentation/user-guides/passing-arguments-to-views/

So this would be a way to use a custom MySQL query without requiring the need for an additional custom post type "Clients".

[Jeffrey Veffer]

Hi Christian-
I like where this is going!
So if I understand this custom query "unique clients" give the ability to filter and pass the list to a view.
Here is my thinking: each client has numerous IDs (which correspond to a specific appointment date).

FirstName LastName Email Appt Date ID
Jeffrey Smith j.effreyveffer@gmail.com 09-Feb-15 8870604

Jeffrey Smith j.effreyveffer@gmail.com 02-Apr-15 8870617

Jeffrey Smith j.effreyveffer@gmail.com 12-Feb-15 8909208

Jeffrey Smith j.effreyveffer@gmail.com 09-Apr-15 9034962

Jane McTest je.ffreyveffer@gmail.com 10-Apr-15 9034983

So I can do a custom query on last name to filter that and pass that to a new view

[wpv-view name='client_last_names' ids="[unique_clients]"]

only display one unique client in the list. Would this work?

Thanks!

[Christian Cox]

That's what I was thinking, yes. Your unique_clients shortcode would be designed to return a comma-separated list of prefiltered Acuity Field IDs. This could be passed into your View as an ID filter, resulting in only those unique Acuity Field posts being displayed in the results of the View.

[Jeffrey Veffer]

Hi Christian-
I am having a little trouble with the order of operations to get the unique clients to display by last name.
I have created a new view called 00.Client-display and it orders by Last Name and filters by shortcode IDs (see attachment)

But again, the field acuity_id is unique *but* there are several acuity_ids for each client as the acuity_id responds to each appointment date and clients usually book multiples. Then they show up in duplicate for the listing.

Setting up a custom field is where I think there is the disconnect - if the custom field is unique_clients, how can I set this up so that it essentially "groups" by the clients last name? Or am I missing the intention?

thanks!

[Christian Cox]

But again, the field acuity_id is unique *but* there are several acuity_ids for each client as the acuity_id responds to each appointment date and clients usually book multiples.
Right, this is the whole point of creating a custom shortcode which returns a list of Acuity IDs that refer to unique clients. The custom query is where the magic happens, as it will filter out the same client's multiple Acuity IDs. Only one Acuity ID will be returned for each client - it doesn't matter which of the multiple Acuity IDs for this client is returned, since they all have the same information you need like First Name, Last Name, etc.

Setting up a custom field is where I think there is the disconnect
I don't think a custom field is needed here, so I must have not explained well, sorry. What I was proposing is to create a custom shortcode that returns a list of Acuity IDs. How you generate that list of Acuity IDs is up to you and the custom query. More information about the shortcode API in WordPress:
https://codex.wordpress.org/Shortcode_API

Then you would implement this shortcode as you showed earlier:

[wpv-view name='client_last_names' ids="[unique_clients]"]

This means your View loops over Acuity ID posts and finds all the rows with Acuity IDs that match the IDs returned by the unique_clients shortcode. When you loop over all those rows, you will display information about each row - which corresponds to Client information. Something like this example in the Loop Output:

Last Name: [types field="last-name"][/types]
First Name: [types field="first-name"][/types]

In your image above, the Query Filter shows "Acuity Email is a string equal to View param ids". This seems inaccurate, because you need to filter Acuity IDs records by ID. Again, your unique_clients shortcode should return IDs, not emails to be used for comparison.

Let me know if that clarifies things for you, or if there's still a disconnect.

[Jeffrey Veffer]

Thanks Christian - its much clearer now
this is something that probably has more to do with me using multisite than Toolset, but I am using a "sandbox" site to test these changes before rolling them out to client sites. The subdomain architecture is app.wellclientapp.com (sandbox) and the client sites are client001.wellclientapp.com etc.
With WPMultisite, there is no editor to add custom code in each subdomain, and I want to avoid if possible adding it to the Network Admin site for fear of disrupting the other sites
In Toolset I noticed that in settings->Front-end Content there is ability to add short codes and in settings->Views Integration you can register frameworks.
The one I want to experiment with starts with this code I put together to pull posts and display 3 posts (of course I would change it to meta_query), but I would start with this to customize for IDs:

#
#  Shortcode Display latest posts
#
function display_latest_post($atts)
{
    extract( shortcode_atts( array(
        'numberofpost' => '3',
        'categoryid' => '',
    ), $atts ) );
 
    $result = '';
    $querystring = 'posts_per_page='.$numberofpost;
    if(strlen($categoryid) > 0)
        $querystring .= '&cat='.$categoryid;
    $query = new WP_Query( $querystring );
    // The Loop
    while ( $query->have_posts() ) : $query->the_post();
        $result.= '<li>';
        $result.= '<a href="'.get_permalink().'">'.get_the_title().'</a>';
        $result.= '</li>';
    endwhile;
 
    // Reset Post Data
    wp_reset_postdata();
 
    //return the result
    return $result;
}

 
function register_shortcodes(){
  add_shortcode( 'DISPLAY_LATEST_POSTS', 'display_latest_post' );
}



 
add_action( 'init', 'register_shortcodes');

Is there a way I can add this code without going back to the Network admin and adding it to functions.php?

Thanks!

[Christian Cox]

I'm not sure how it works in multisite, but a code snippet plugin like https://wordpress.org/plugins/code-snippets/ might be helpful if you don't want to modify the core theme files.