[Resolved] Displaying distinct values only

[Author]

Posts

[Jeffrey Veffer]

Hi there!
In the screenshot below, I have displaying client records, but instead of duplicates, I would like to display them only once
I have tried several query filters, but can't get them to display correctly.
Is there a way to do this by filter ?

thanks

[Christian Cox]

It looks like the ID for each row is different. That would imply that these aren't duplicates, they just have similar content. Am I misunderstanding? If so, please provide some screenshots that show your View settings and any Content Templates you may be using to render this View. Thanks!

[Jeffrey Veffer]

Sorry Christian - Wrong screenshot :<
This is the correct one that shows how it lists clients, but many rows for the same one. Is there a way to only have one client distinct?

[Christian Cox]

By design, a View looks for all matching results in the database. If there are multiple Client records in the database that include the same name, email address, and phone number, then those records will all appear in the table, and will appear to be duplicates. What I'm not clear about is why there are duplicate Clients in the database. Can you share the settings from your View and any Content Templates present that are used to display the table you have pictured there?

[Jeffrey Veffer]

I store each client with their last visit for other processes I do to determine if they are still active after 30 days.
But in this case, I just want to display their Name, phone, email and referral data.
Here is the view settings in the screen shot and here are is the html code from the loop.

Thanks!!

<!-- 00.Client-Referral [working] -->
[wpv-layout-start]
[wpv-items-found]
<div class="container">

<!-- Jumbotron -->
<div class="jumbotron">
<h1>Clients</h1>
<p class="lead">Browse / Edit Clients</p>
</div>

<hr class="bs-header-separator"><!-- ======================================================== -->

<section id="Clients">
<div class="entry-content">

<div class="row-fluid">
<div class="span12">

<div class="cred-group">
<div class="cred-header"><h3>Clients Data</h3></div>

<!-- <p class="btn btn-success">Add New Client</p>-->
<p class="btn btn-success">hidden link" target="_blank">Add New Client</p>

</div><!-- end cred-group -->

<hr /><!-- ======================================================== -->

<div class="cred-header"><h4>Existing Clients</h4></div>
<table class="table table-striped table-bordered">
<thead><tr>
<th class="text-center">[wpv-heading name="types-field-acquity-first-name"]First Name[/wpv-heading]</th>
<th class="text-center">[wpv-heading name="types-field-acquity-last-name"]Last Name[/wpv-heading]</th>
<th class="text-center">[wpv-heading name="types-field-acquity-email"]Email[/wpv-heading]</th>
<th class="text-center">[wpv-heading name="types-field-acquity-phone"]Phone[/wpv-heading]</th>
<!--<th class="text-center">[wpv-heading name="types-field-acquity-date"]Appointment[s][/wpv-heading]</th>
<th class="text-center">[wpv-heading name="types-field-acquity-id"]ID[/wpv-heading]</th>-->
<th class="text-center"colspan="2">[wpv-heading name="client-referral"]Referral[/wpv-heading]</th>
</thead>
<tbody>

<!-- wpv-loop-start -->
<wpv-loop>

<tr>
<td class="text-center">[types field="acquity-first-name" output="raw"][/types]</td>
<td class="text-center">[types field="acquity-last-name" output="raw"][/types]</td>
<td class="text-center">[types field="acquity-email" output="raw"][/types]</td>
<td class="text-center">[types field="acquity-phone" output="raw"][/types]</td>
<!--<td class="text-center">[types field="acquity-date"][/types]</td>
<td class="text-center acquity-id">[types field="acquity-id" output="raw"][/types]</td> -->
<td class="text-center min-width">[wpv-if evaluate="'[wpv-post-taxonomy type="client-referral" separator=", " format="text" show="name" order="asc"]' = ''"]

<!-- Button trigger modal (ADD Referral) -->
Add

<!-- Modal (ADD Referral) -->
<div class="modal fade" id="myModal1-[wpv-post-id]" tabindex="-1" role="dialog" aria-labelledby="myModalLabel-[wpv-post-id]" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h4 class="modal-title">Add Referral</h4>
</div>
<div class="modal-body">
[cred_form form="1656" form_name="00.Client Referral EDIT"]
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
<!-- <button type="button" class="btn btn-success">Save</button> -->
</div>
</div><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
</div><!-- /.modal -->

[/wpv-if]

[wpv-if evaluate="'[wpv-post-taxonomy type="client-referral" separator=", " format="text" show="name" order="asc"]' != ''"]
[wpv-post-taxonomy type="client-referral" separator=", " format="text" show="name" order="asc"]
[/wpv-if]

<!-- END Button trigger modal (ADD Referral) -->

</td>
<td class="text-center min-width">

[wpv-if evaluate="'[wpv-post-taxonomy type="client-referral" separator=", " format="text" show="name" order="asc"]' = ''"]
[/wpv-if]

[wpv-if evaluate="'[wpv-post-taxonomy type="client-referral" separator=", " format="text" show="name" order="asc"]' != ''"]

<!-- Button trigger modal (EDIT Referral) -->
Edit

<!-- Modal (EDIT Referral) -->
<div class="modal fade" id="myModal2-[wpv-post-id]" tabindex="-1" role="dialog" aria-labelledby="myModalLabel2-[wpv-post-id]" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h4 class="modal-title">Save</h4>
</div>
<div class="modal-body">
[cred_form form="1656" form_name="00.Client Referral EDIT"]
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
<!-- <button type="button" class="btn btn-success">Save</button> -->
</div>
</div><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
</div><!-- /.modal -->

[/wpv-if]

<!-- Button trigger modal (EDIT Referral) -->

</td>
</tr>

</wpv-loop>
<!-- wpv-loop-end -->

</tbody>
</table>

<!--
Pagination HTML
-->

<!--<hr />=================================================================-->

<div class="bs-example">
<ul class="pagination">
[wpv-filter-start hide="false"]
[wpv-pagination]

[wpv-pager-current-page style="link"][/wpv-pager-current-page]

[/wpv-pagination]
[wpv-filter-end]

</div>

<!--<hr />=================================================================-->

<hr /><!-- ======================================================== -->

<!-- <p class="btn btn-primary">Edit Existing Clients</p>-->
<p class="btn btn-primary">hidden link" target="_blank">Edit Existing Clients</p>

<hr /><!-- ======================================================== -->

</div>
</div>
</div><!-- .entry-content -->

</section>

</div> <!-- /container -->

[/wpv-items-found]

[wpv-no-items-found]
[wpml-string context="wpv-views"]

<div class="container">

<!-- Jumbotron -->
<div class="jumbotron">
<h1>Clients</h1>
<p class="lead">Browse / Edit Clients</p>
</div>

<hr class="bs-header-separator"><!-- ======================================================== -->

<section id="Clients">
<div class="entry-content">

<div class="row-fluid">
<div class="span12">

<div class="cred-group">
<div class="cred-header"><h3>Clients Data</h3></div>
<!-- <p class="btn btn-success">Add New Client</p>-->
<p class="btn btn-success">hidden link" target="_blank">Add New Client</p>

</div><!-- end cred-group -->

<hr /><!-- ======================================================== -->

</section>
</div> <!-- /container -->

No items found[/wpml-string]
[/wpv-no-items-found]

[wpv-layout-end]

[Christian Cox]

If there are duplicate entries in the Acuity Fields post type, then you will have duplicate results in this View. There's not a good way to compare the results to each other and filter out similar items. Instead, I would try to design my View to loop over items that do not repeat themselves. For instance, if each row of this table represents a Client, then I would consider selecting the Clients post type instead of Acuity Fields. If you need to filter those Clients, then you should set up a custom field on the Clients post type that holds the information you need to filter upon. I might be able to help you set up this field if you can help me understand why you have chosen the Acuity Fields post type instead of the Client post type - is there something captured by the Acuity Field post type that is not captured in the Client post type?

[Jeffrey Veffer]

Hi Christian:
The client info is being pulled from the cpt-acuity-fields table so we have the first and last name, but every time they are entered there is a unique ID and a calendar ID from acuity so each gets listed as an individual appointment. It was chosen by my types developer, but he was still learning so there may be a better way to do it!

I would like to consolidate so that even if the client has many appointments, in this view, they only get listed one time. Then we can have a breakout page that can list all their appointments by date time if we want.
Attached are the different types.
Does this make sense?

Thanks!!

[Christian Cox]

The client info is being pulled from the cpt-acuity-fields table so we have the first and last name
Oh okay, so Client isn't even a post type right now. I think this is the real core issue we need to resolve. Somehow, we need to create a Client post for each unique "client" in the Acuity Fields table, without duplicates. Then, instead of setting up a View of the Acuity Fields post type, you will display a View of Clients here. This will eliminate the extra matching rows. It will also set you with a Clients post type, where you can add a View of Appointments for each Client.

What can you tell me about how items get added to the Acuity Fields table? Ideally, instead of adding the client names to each row in this table, you would instead add a Client's ID somehow, either with a custom field, or by implementing a parent / child relationship. Either way, you must create individual Clients before new Acuity Fields rows are created. With the Client created, you'll have access to their ID. Then you know which Client ID should be associated with each row. Again, this seems like the ideal setup if I wanted to start from scratch.

[Jeffrey Veffer]

The info comes from a scheduling application called Acuity Scheduling that we connect through their API. We check every 15 minutes or so for updated records and then add those to the table. We use the function get_appointments which pulls the info into Aqcuity fields with firstname, lastname, appointment date etc.

Thanks!

[Christian Cox]

What do the "Acuity Username" and "Acuity ID" represent in the image you provided? Are either of those unique to Ms Torres-Darlington, i.e. all of her appointments have the same username or id value? If so, then this indicates that the Acuity system stores unique clients. If you're able to access their API to populate these Appointments, then perhaps it would be possible to access their API to populate Clients. I'm not the best source of information about their software, so you may need to reach out to their support team.

[Jeffrey Veffer]

Hi Christian-
Acuity username is the unique ID of the calendar - basically the user account ID which is the same for all the clients in a given user's calendar.

Acuity ID is a unique transaction code given to every calendar entry so it can be tracked independently.

[Christian Cox]

I see. In that case, it may not be possible to generate unique Clients - so it's not possible to create a View that will show only unique results given the available data. If you have the ability to evaluate each item as it is imported to the table, like an API after import callback, then you might be able to write some custom code that compares each new item to the other items in the table. If it finds a matching item, then you can use update_post_meta to set some custom field (maybe called "is_duplicate") value to "1" to indicate this row is a duplicate of another row. Then you can filter a View of Acuity Fields by this custom field, and show only results where the custom field is not equal to 1. Of course, this means you would have to manually add this field value to all existing duplicates somehow.

[Jeffrey Veffer]

Hi Christian-
Would it be possible to use an mySQL query against the table containing the client info to select the distinct client and save that to an mySQL view? Does toolset work that way?

thanks

[Christian Cox]

Assuming you can import data into the WordPress database in a way that creates unique Client posts, Toolset can certainly read those posts and query them in a View. However, Toolset doesn't include an API for importing or querying directly from another database or custom table, so those queries that import the data would have to be custom code.

[Jeffrey Veffer]

Thanks Christian
Would I be able to add this mySQL view somehow as a new "data source" so I would be able to select it and sort on it? (see the attachment)

Thanks!