covoljeremy

Thanks and this can be closed now

Hi Peter,
Thanks for your reply. Ive just relaised that I was been using the wrong dbtable! Ive changed it now and it works using my_field=$$USER$$.

Apologies for the time wasting!

Thanks, Jeremy

Hi Peter, using the view coding (Import scripts through Data Explorer) with an SQL query has worked and displays via Data Pub just as I wanted so thanks very much for your help.

Apologies for bumping this post….

Hi, Im in the same position as the OP (same circumstances for testing) but I cannot find the checkbox for removing this option.
Can you expand on where I can find this option in the settings menu.

Thanks, Jeremy

• This reply was modified 4 years, 2 months ago by covoljeremy.

Hi Peter Ive replied to you

Its the ‘Group By’ statement that has the greatest effect as when I remove that from the SQL query, I get the same results as the code being used above.

Is there a similar statemnent to ‘Group By’?

Thanks, Jeremy

Thanks Peter, but ive tried the OR and it then finds the person who has the most recent entry only :-/

This SQL statement returns the correct results;

SELECT Candidate_Name, MAX(Date_Trained) AS most_recent_date FROM wp_dbnamexxx GROUP BY Candidate_Name

The difference here is that it is finding the name first and then finding the most recent record (and then groups by name)

the problem with the current code is that it looks for the most recent date and then stops and returns a name (or based on a name if the “” where Candidate_Name = ‘Xxxx X’ “” statement is used. I am tryng to obtain everyone’s most recent date.

Im still trying to find a way so your continued help is invaluable

Thanks, Jeremy

Hi Peter, the above syntax returns no results.

Im trying various versions of this now but….

…using this code;
Date_Trained >= (select max(Date_Trained) from wp_database) where Candidate_Name = “Xxxx X”

gives me everyones records from the max(Date_Trained) record of Candidate_Name = “Xxxx X”

So, if I have the following records;
NAME Latest Record
Xxxx X 2018/10/06
Yyyy Y 2017/09/23
Zzzz Z 2018/11/15

the above code only returns Xxxx and Zzzz records and doesnt include Yyyy which would make sense as the date has been set by Xxxx latest record.

What I am after is everyones latest record.

Thanks, jeremy

Thanks Peter. With multiple WHERE clauses, what separator should I be using?

Ive tried colon/semicolon, comma, space but none seem to work?

I would like the WHERE phrase to be;

Date_Trained >= (select max(Date_Trained) from wp_database), Candidate_Name = “Xxxx X”, Candidate_Name = “Yyyy Y”, Candidate_Name = “Zzzz,Z”

Hi Peter and apologies for taking so long to reply.

Ive used your example above and made some progress however, this only returns the most recent record (by date) rather than showing the most recent record from each individual.

The custom SQL queries is as you have described above (typed into the ‘where’ field in Data Designer). I wasnt sure whether there is another field where you can add in custom SQL queries to manipulate the data prior to using it in a child page as in;

Main data table —> Custom queries —> Custom data Table for use in Data Project —> data used in Page

WP theme!

Hi Peter and thanks for the speedy response!

Do you see a message “No data found”? No messages appear. There are no messages displayed.

Is there any space between the column headers and footers? Are the footers visible? No, there is no space between header/footer. The column titles appear as a header and footer with nothing in between.

do you see any errors in the console? Yes! Ive jsut looked and found the following;
Uncaught ReferenceError: wpda_datatables_ajax_call is not defined
I cant find a table with that name?

If you use the inspector to view the table, does the table contain any data rows? I can see the data rows when using the ‘test’ button but thats all.

Do you have a where clause? No?

This can be a css or a sql issue. Can you add some screenshots? Yes.but where can I send them to?

If the table visible from outside? Unfortunately not. Its an internal web site