Count for only users records

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
bruceholt
Veteran Member
Posts: 42
Joined: 2016-07-30 20:16

Count for only users records

Post by bruceholt » 2019-07-07 02:26

Hi all,

I have a modified home page which shows a user how many tasks he/she has uncompleted but it is showing all open tasks from all users.

The code I am using is:

Code: Select all

<?php echo sqlValue("select count(1) from tasks WHERE completed IS NULL"); ?>
Can I limit it to only the users records?

Regards, Bruce

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 488
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pböttcher » 2019-07-07 09:35

Hi,

have a look at the memberinfo and link it to the membership owner table.

bruceholt
Veteran Member
Posts: 42
Joined: 2016-07-30 20:16

Re: Count for only users records

Post by bruceholt » 2019-07-08 09:26

Hi pböttcher ,

Sorry I don't understand. :(

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 488
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pböttcher » 2019-07-09 07:50

Hi,
in order to get more specific it would be necessary that you explain from whom you would need to show the records.

Do you want to show the user only the amount of his records, or the amount of records of the group the user belongs to?

bruceholt
Veteran Member
Posts: 42
Joined: 2016-07-30 20:16

Re: Count for only users records

Post by bruceholt » 2019-07-09 08:06

Hi pböttcher,

I have modified the homepage with and have some text that says "Good evening "user", you have 2 task(s) to complete. You have 1 equipment that the repair or maintenance is not completed and 1 equipment that need attention."

The tasks for example has a check box to indicate if the task is completed or not. If it is not completed then it is null. The count shows how many of those "completed" fields are null. ie - empty. It is showing the count OK but the problem is that it is showing the total in the table instead of just that users records. I hope I have explained what I am trying to do. I am not sure how to user the member records lookup.

Regards, Bruce

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 488
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pböttcher » 2019-07-09 13:39

Hi,

try

Code: Select all

<?php echo sqlvalue("select count(1) from tasks left join membership_userrecords on recid=table1.id WHERE tasks.completed IS NULL and memberid='".getLoggedMemberID()."'"); ?>

bruceholt
Veteran Member
Posts: 42
Joined: 2016-07-30 20:16

Re: Count for only users records

Post by bruceholt » 2019-07-10 09:57

Hi,

Oddly, it is showing all of the uncompleted tasks belonging to other members when logged in as admin but 0 when a user is logged in even though the user has tasks which are not completed.

What I have is:

Code: Select all

<?php echo sqlvalue("select count(1) from tasks left join membership_userrecords on recid=tasks.id WHERE tasks.completed IS NULL and memberID='".getLoggedMemberID()."'"); ?>

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 488
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pböttcher » 2019-07-10 10:22

Hi, are the task (=records) owned by the user?

It is hard to understand how your setup is.

How do you identify which task belongs to a user?

bruceholt
Veteran Member
Posts: 42
Joined: 2016-07-30 20:16

Re: Count for only users records

Post by bruceholt » 2019-07-11 08:24

Hi,

Yes, the records are owned by the user. They are not viewed by anyone else other than the user. The tasks are for the user only to be used as reminders to themselves. The table is tasks. I hope that helps. Thanks, Bruce

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 488
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pböttcher » 2019-07-11 12:17

Hi,

can you share the table definition of your tasks (especially the primary key).

bruceholt
Veteran Member
Posts: 42
Joined: 2016-07-30 20:16

Re: Count for only users records

Post by bruceholt » 2019-07-12 08:06

Hi pböttcher,

Is this what you mean?

Image

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 488
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pböttcher » 2019-07-12 09:29

Hi,

thanks yes.

Sorry, I had an error in the provided SQL.

Can you try

Code: Select all

<?php echo sqlvalue("select count(1) from tasks left join membership_userrecords on pkValue=tasks.id WHERE tasks.completed IS NULL and memberID='".getLoggedMemberID()."'"); ?>

bruceholt
Veteran Member
Posts: 42
Joined: 2016-07-30 20:16

Re: Count for only users records

Post by bruceholt » 2019-07-12 11:18

Thanks very much pböttcher.

That worked perfectly.

Regards, Bruce

Post Reply