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
User avatar
bruceholt
Veteran Member
Posts: 100
Joined: 2016-07-30 20:16
Location: Australia

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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pbottcher » 2019-07-07 09:35

Hi,

have a look at the memberinfo and link it to the membership owner table.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
bruceholt
Veteran Member
Posts: 100
Joined: 2016-07-30 20:16
Location: Australia

Re: Count for only users records

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

Hi pböttcher ,

Sorry I don't understand. :(

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pbottcher » 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?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
bruceholt
Veteran Member
Posts: 100
Joined: 2016-07-30 20:16
Location: Australia

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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pbottcher » 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()."'"); ?>
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
bruceholt
Veteran Member
Posts: 100
Joined: 2016-07-30 20:16
Location: Australia

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()."'"); ?>

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pbottcher » 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?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
bruceholt
Veteran Member
Posts: 100
Joined: 2016-07-30 20:16
Location: Australia

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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pbottcher » 2019-07-11 12:17

Hi,

can you share the table definition of your tasks (especially the primary key).
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
bruceholt
Veteran Member
Posts: 100
Joined: 2016-07-30 20:16
Location: Australia

Re: Count for only users records

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

Hi pböttcher,

Is this what you mean?

Image

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Count for only users records

Post by pbottcher » 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()."'"); ?>
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
bruceholt
Veteran Member
Posts: 100
Joined: 2016-07-30 20:16
Location: Australia

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