Need help limiting number of records allowed

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
mgain2013
Posts: 29
Joined: 2013-02-16 16:12

Need help limiting number of records allowed

Post by mgain2013 » 2014-01-10 17:13

Does anyone have any idea how to limit number of records created? Let Me try to explain what I mean. I have a main form and then a sub form that is connected to the main form. I need to restrict the subform to only 1 record created per request. The main form is what I call an ARC request, it is filled out by a resident of our community requesting a modification to his property. In turn a member of the ARC Committee reviews the request and creates the subform, which shows the status of the request. The problem is I only want one ARC Status per request. I am not sure how to limit the records per Staus to one. Does any one know how to limit one record per request. Keep in mind that many people use this database. So I don't want to limit only record for the entire table, only one connecting record. I hope this doesn't sound to confusing. If you have any question please ask me. I really need a solution to make this the best application possible.

Thanks,
Michael

Bertv
Veteran Member
Posts: 65
Joined: 2013-12-11 15:59

Re: Need help limiting number of records allowed

Post by Bertv » 2014-01-11 15:18

Michael,
in the table_name_before_insert() hook you can count the ARC_requests connected to the ARC_status. If not 0 than RETURN must be false. See example below:

function ARC_status_before_insert(&$data, $memberInfo, &$args){
$b_return = true;
// only one ARC_status allowed for an ARC_request
if ($data['sts_req_id'] != "") {
// count the linked ARC_requests
$sql_string = "SELECT ifnull(count(*),0)
FROM ARC_status
WHERE sts_req_id = " . $data['sts_req_id'] . ";";
$countARCrequest = sqlValue($sql_string, $o);

if ($countARCrequest != 0) {
// insert NOT allowed
$b_return = false;
}
}

return $b_return;
}
Bert
I am using Appgini 5.75

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: Need help limiting number of records allowed

Post by KSan » 2014-01-11 22:01

Alternative thought... Since there can only be one response to one request why not combine all into one table? First half of your fields would be the ARC request. 2nd half would be the response. Ways to have 2 or more views into one table has been discussed in the past. Depending on group membership you can hide/show fields for example.

So a resident logs into your system and clicks new ARC request and he/she is presented with the relevant fields.

Then a committee member and is presented with ARC requests where review fields are empty.

Could be a straightforward way to deal with your need. Just an idea.

mgain2013
Posts: 29
Joined: 2013-02-16 16:12

Re: Need help limiting number of records allowed

Post by mgain2013 » 2014-01-16 00:59

Thank you both for your suggestions, I will try them both and see what works best!!

Michael

mgain2013
Posts: 29
Joined: 2013-02-16 16:12

Re: Need help limiting number of records allowed

Post by mgain2013 » 2014-01-24 02:54

Bert,

Can you tell me what I'm doing wrong with the code you gave me? It isn't limiting the ARC Status to 1 record per ARC Request.

Here are the actual values for the id for each table as well as the table names.

Table ARC Request is arcrequest
the id is id

Table ARC Status is arcapproval
the id is arcrequestid

Here us how I changed the code you gave me:

function arcapproval_before_insert(&$data, $memberInfo, &$args){
$b_return = true;
// only one ARC_status allowed for an ARC_request
if ($data['sts_arcrequestid'] != "") {
// count the linked ARC_requests
$sql_string = "SELECT ifnull(count(*),1)
FROM arcapproval
WHERE sts_id = " . $data['sts_id'] . ";";
$countarcrequest = sqlValue($sql_string, $o);

if ($countarcrequest != 0) {
// insert NOT allowed
$b_return = false;
}
}

return $b_return;
}


Thank You in advance for your help!!

Michael

Post Reply