Limit grid to one record only

[[email protected]]

Hi, I have 2 grids that are linked. One is the parent and one is the child. I need to limit the number of children records to 1 for each parent record, is this possible?

I don't mind how it is achieved as long as the user can't add more than 1 child record... so can I somehow hide the "new record" link after one record has been saved or deny the user somehow when the "new record" link is clicked?

Thanks in advance.

[Keith Hubert]

Re: Limit grid to one record only

Hi Richard,

Have you tried this? See attachment.

[richatal5soft]

Re: Limit grid to one record only

deny the user somehow when the "new record" link is clicked?

This might be long and convoluted, but how about maintaining a logical session variable/flag set in onGridInitialize (true if record already there, false otherwise) and then use it to disable/hide the add/new button on the child grid?
When the record is added and saved, set the said session variable/flag to true in the afterSaveRecord event.

[Davidk]

Re: Limit grid to one record only

I would probably do something like this.

Get into the Update Settings of your grid and set the Number of new record rows to 1... and then set the New Records Edit Style to ShowOnDemand and check on the Hide new record row after insert

Now... I'm not crazy about the Hyperlink to add a new record so, in the New Records button label put this... it just hides the hyperlink, 'cause I want my own nice button. If you want the hyperlink then you can alter what's below to disable or hide the hyperlink instead of a button.

<div id = "newRecs" style="display: none;">New Records</div>

Add your own New Record button.

Finally, in the Client-side event, onGridRenderComplete, put this...

if ({Grid.object}._rowsInGrid > 1)
	{
	$('{Grid.ComponentName}.ACTIONBUTTON.ADDNEW.G0').disabled = true;
	}

You'll have to determine the name of your Action button using Firebug under Firefox. So, if the count of rows in the grid is greater than 1 then the button gets disabled.

[gkpurcey]

Re: Limit grid to one record only

I don't normally ask the "why" question when someone is trying to do something. But I am curious in this case as to why you have decided to even have a child table to begin with.
If you only want a single child record, why not include that information in the parent table, and just use a single table?

In the "old" days, when memory and hard drive space was limited, I could maybe see that. But nowadays, doesn't seem like that would be much of an issue. Unless of course, there is something unique about the child table that lends itself to being in its own table.

I guess I'm wondering this more for my own education, as I am wondering under what circumstances I might want to do the same?

I'm not suggesting that you should change your approach.

Just have the proverbial "inquiring mind"...

Thanks!

[Steve Wood]

Re: Limit grid to one record only

Optionally combine this with the earlier suggestion to hide the Add New Record link. Put this in the canNewRowsShow event of the child. It will disable but not hide the Add New Record link if a child record exists. Also alter your child so only one Insert row will ever appear.


var ed = e.rowsInEditMode;
if (ed >= 1) {
return false;
}
else {
return true;
}

[DBM]

Re: Limit grid to one record only

This is also soemthing similar

http://msgboard.alphasoftware.com/al...289#post550289

[[email protected]]

Re: Limit grid to one record only

Thanks for all the great replies. I should have been more specific and said that my gird has a detail view part. Anyway here is a pic of my grids to make it more apparent. I want to hide or disable the New Records link once 1 Discharge Summary record has been added.

A51.jpg

Thanks in advance

[LukeS]

Re: Limit grid to one record only

I have run into this issue a few times in my app. The problem comes up when you want to add a new record if one does not exist or edit a record if one already exist. If a record already exist alpha still shows the new record row. It would be nice if alpha added a option to suppress new insert records rows if data is present in the grid. You can hide the new record row though, here is how I accomplished the task.

- For each field in the grid set the show hide expression to: (grid.recordsInGrid=0) or (data.rowNumber>0)
- In Grid --> Properties - Set "rows of data" to 1, uncheck "add records only", check "suppress empty rows"
- In Grid --> update settings - uncheck "allow delete", check "allow update", check "allow insert", set "number of new record rows" to 1, set "new record row position" to bottom

[The Mage]

Re: Limit grid to one record only

If I'm understanding what people are trying to do, I just use the single record form template, deselect the Show record navigator buttons to suppress any navigation.
On the customize toolbar template, I insert the default, and then cut out the save and insert new record from the list.

As long as the Show new record if no record in query is selected, which it should be by default, then you can add a new record if one doesn't exist, only show that record, but if it is deleted, the record disappears. Although closing and opening it, or navigating to another record and back will bring it back.

[Steve Wood]

Re: Limit grid to one record only

This may not fit the requirement, but at the IADN site I have one Developer table, and then a couple additional tables related one-to-one for each Developer record. Since in this case I know in advance that each Developer will eventually enter some data in these related tables, I simply create a blank record in the related table when a new Developer is added (using the Developer_ID as the key in both tables). The main Developer grid has a bunch of Linked Content areas -- any that are related one-to-one have their linked grid restricted to only allow Update, and to show only one row. No mess.

[njguy]

Re: Limit grid to one record only

Hi, I have 2 grids that are linked. One is the parent and one is the child. I need to limit the number of children records to 1 for each parent record, is this possible?

I don't mind how it is achieved as long as the user can't add more than 1 child record... so can I somehow hide the "new record" link after one record has been saved or deny the user somehow when the "new record" link is clicked?

Thanks in advance.

Hello Richard

I have had the same type of reguest but also wanted to do this within one grid (see both parent and child record). In the attached image the display fields are the parent records and the date entry fileds are the child records. The grid is based on MS SQL table with a query that will return all parent records regardless if there is a child record for a particulare userid

SELECT ( a bunch of fields)
FROM Ideas Ideas
LEFT OUTER JOIN Ratings Ratings
ON Ideas.IdeaSeq = Ratings.IdeaSeq AND Ratings.UserId = :UserId


I then modified the onBeforeSQLCommandExecute Event that first checks to see if a Child Record has been entered. if one does not exisit my code handles the insert procedure If this was an update the SQL code generated by alpha is correct.

What I liked about this solution is that it makes it transparent to the user if he is doing and update or insert all within one grid for a parent child releationhip.

dim Rdata as n
Rdata = eval("e.rv.v.r" + e.rownumber + ".IDEASEQ1")
if Rdata = 0
e.SQLCommandWasHandled = .t.
e.cn.PortableSQLEnabled = .t.
e.sqlType = "INSERT"
e.primaryKeyWhereClause = null_value()

sql = "INSERT INTO Ratings (IdeaSeq,Userid,Rating1,Rating2,Rating3,Rating4,Notes) Values(:New_IDEASEQ1,:New_USERID,:New_RATING1,:New_RATING2,:New_RATING3,:New_RATING4,:New_NOTES)"

dim Idata as n
Idata = eval("e.rv.v.r" + e.rownumber + ".IDEASEQ")

dim UserId as n
User = a5ws_getcurrentuser() ' Get Current user logged in

dim flag as l
dim args as sql::arguments
args.xml = e.argsxml
args.add("New_IDEASEQ1",Idata)
args.add("New_USERID",User)
args.add("New_RATING1",args[1].data)
args.add("New_RATING2",args[2].data)
args.add("New_RATING3",args[3].data)
args.add("New_RATING4",args[4].data)

if args[5].data = 0 then
args.add("New_NOTES",null_value())
else
args.add("New_NOTES",args[5].data)
end if

flag = e.cn.execute(sql,args)
if flag = .t. then
e.flagError = .f.
e.errorText = ""
e.rowsAffected = e.cn.affectedRows()
e.SQLToDisplay = sql
e.ArgsToDisplay = ""
else
e.flagError = .t.
e.errorText = e.cn.callresult.text
e.rowsAffected = 0
end if
end if


ORDER BY Ideas.Refrence

[aburningflame]

Re: Limit grid to one record only

Add a unique index on your database.

Example:
(only 1 project per use)

project table
project_id
user_id <<< Create Unique Index


User will try to assign more than 1 project, and will get an error message.

ALternatively, use the CanInsertRecord/CanUpdateRecord event and write a bit of code to check.

dim userid as N=convert_type(DataSubmitted.userid,"N") 'grab the value for userid
dim user_project_count_c as C=convert_type(sql_lookup("::name::conn","project","user_id="+userid,"COUNT(*)"),"c") 'grab count as C (convert to c used because alpha returns a weird null if the record isnt found)
if user_project_count_c<>"" then
       'record found
       'convert the C variable to N for comparison against >0
       if convert_type(user_project_count_c,"N") > 0 then
               Result.Cancel=.t.
               Result.ErrorHTML="User can only have 1 project"
       end if
else 
    'problem with the execution for some reason
    Result.Cancel=.t.
     Result.ErrorHTML="Could not add record [Could not verify Project Count]"
end if

Both these solutions will do exactly what you require. Good luck.