Re: Creating Decrementing value in Table
Hi All, Thanks for all your help i have managed to do now the incrementing in the table as well as decrementing it. Regards, Amey On 12/30/08, ccrashh ccra...@gmail.com wrote: Why would there be a perfomance impact? How are you adding this new record? You already know that the highest value is 5, so when you add a new record (in your example called Test6) that the new Order ID would be the current max order id + 1. So when you do a Push Fields to create a new record, you push 6 to the order id. I really am curious as to how adding a new record is causing you to have performance issues. Steve ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Creating Decrementing value in Table
Why would there be a perfomance impact? How are you adding this new record? You already know that the highest value is 5, so when you add a new record (in your example called Test6) that the new Order ID would be the current max order id + 1. So when you do a Push Fields to create a new record, you push 6 to the order id. I really am curious as to how adding a new record is causing you to have performance issues. Steve ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Creating Decrementing value in Table
I think the easiest approach is, as Mike said, an Update SQL statement: UPDATE childtablename SET counter = counter - 1 WHERE parentrequestid = '$request_id$' AND counter '$counterbeingdeleted$' childtablename = the schema of the child form counter = field on the childtablename that stores the 1, 2, 3, etc. parentrequestid = I am assuming you have some foreign key stored on the childtable that is the Request ID of the parent record. counterbeingdeleted = the counter value of the record being deleted (ie 2). This way you don't have to run through a Push Fields filter. Steve ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Creating Decrementing value in Table
One more request.i was earlier able to increment the counter in the table but having issues in performance now .so would you be able to guide me in how to implement the Increment value in Table same as to one you have given me for Decrementing. Acutal Scenario is that when user selects a CTI and raises a Ticket then on opening the Form there should be default values(records) loaded in the table.This table hold data of only those records which have a flag set to Yes. Currently i have a Add/Modify button where in a user selects values from drop down(these are the values like Test1, Test2, Test3 etc) option and then clicks on Add/Modify Button.If the there is any matching request then it should modify the previous request.and if if there is no matching request then it should create a new record with increment in the values currently in the table. for e,g These are the default values loaded into the table when the ticket is raised. Order ID Value 1 Test1 2 Test2 3 Test3 4 Test4 5 Test5 Now if i want to modify the second record then the order id should not change only the values in it should get modified. Now if i am selecting a new value from drop down menu say suppose Test6 and then adding it then that record should get the record id as '6'. So would like to know how this can be implemented without having performance impact? Regards, Amey On 12/29/08, ccrashh ccra...@gmail.com wrote: I think the easiest approach is, as Mike said, an Update SQL statement: UPDATE childtablename SET counter = counter - 1 WHERE parentrequestid = '$request_id$' AND counter '$counterbeingdeleted$' childtablename = the schema of the child form counter = field on the childtablename that stores the 1, 2, 3, etc. parentrequestid = I am assuming you have some foreign key stored on the childtable that is the Request ID of the parent record. counterbeingdeleted = the counter value of the record being deleted (ie 2). This way you don't have to run through a Push Fields filter. Steve ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Creating Decrementing value in Table
In a message dated 24/12/2008 18:09:05 GMT Standard Time, ameyb...@gmail.com writes: amey.x.bhos...@jpmorgan.com Hi Amey, Using an SQL statement will provide the fastest possible solution. I'll send you the details. Kind regards Mike ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Creating Decrementing value in Table
I'm assuming that whenever you 'delete' a record, you are simply changing the status so that it doesn't show up on the table, then refreshing the table. You could add a filter that runs on the child form, the one from which the table is pulling its data, and have that filter run whenever the status is set to inactive, delete, whatever it is getting set to. The filter will then do a push fields to the same form for any record where counter on the current ticket is less than the counter on any other ticket found. Then, simply do the push fields of Counter = Counter - 1. I think that this should work for you. Order IDValue 1 Test 2 Test1 3 Test2 4 Test3 If you deleted 2, then the filter would decrement test2 and test3, giving you the following: Order IDValue 1 Test 2 Test2 3 Test3 I think this will work. There might be a timing issue, however, on the refreshing of the table. Since you are using a filter, I'm afraid that your table might refresh prior to all of the tickets in the child form getting decremented. Let me know if it works. Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Amey Bhosale Sent: Wednesday, December 24, 2008 10:21 AM To: arslist@ARSLIST.ORG Subject: Creating Decrementing value in Table ** Hi All, I am currently working on ARS 7.1 and have a query to ask you all. I have created a Regular Form on which i have added a table field(holding data into anothe form) which displays only those records which have a flag set to Yes On this Regular Form i have added a Add/Modify and Delete Button with some selection fields on top of this Table field which user will select and either add to the table or select any record from the table and delete it. When user selects the value from selection field and clicks on Add button the record gets added into the Table Field with an incrementing Value i.e 1,2,3 and so on..which i managed to do so... But now i have a problem in where in if i select a record from Table and click on Delete Button then that particular record show go from the table and then the next records order id shuld get updated to previous one and same follows through rest of the table. for e.g Now suppose i have added records as below Order Id Value 1 Test 2 Test1 3 Test2 4 Test3 5 Test4 Now if i want to delete second record i.e Order id = 2 then when it gets deleted the third record i.e Order id =3 shuld get updated as Order id = 2 and then all the below record shuld similarly get updated i.e fourth record i.e Order id =4 should get updated to 3 and Order id =5 to 4 Can someone let me know how this can be implemented? Regards Amey Bhosale Remedy Application Developer J . P. Morgan Services India Pvt. Ltd. Cricket on your mind? Visit the ultimate cricket website. Enter now! http://in.rd.yahoo.com/tagline_cricket_1/*http://beta.cricket.yahoo.com __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___
Re: Creating Decrementing value in Table
You could use a single SQL statement in an Active Link to update records to order_id -1, where the order_id than that for the record just deleted. Just add an integer display only field 'zTmpCurOrderId' which is set to the order_id for the current record about to be deleted. Use this value in the SQL statement. The table refreshes correctly and the order_id's get updated as expected. Can send you the code off-line if it helps? I trust this is of some help. Kind regards Mike ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Creating Decrementing value in Table
Hi Mike, Can you tell me whether writing SQL statement will it affect the performance of checking each record and then updating it? Also would you be able to send me the code at amey.x.bhos...@jpmorgan.com id so that i can check and test the same? Regards Amey On Wed, Dec 24, 2008 at 10:20 PM, arlist...@aol.com wrote: ** You could use a single SQL statement in an Active Link to update records to order_id -1, where the order_id than that for the record just deleted. Just add an integer display only field 'zTmpCurOrderId' which is set to the order_id for the current record about to be deleted. Use this value in the SQL statement. The table refreshes correctly and the order_id's get updated as expected. Can send you the code off-line if it helps? I trust this is of some help. Kind regards Mike __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Creating Decrementing value in Table
Hi Opela, Thanks for the response. On Delete Button i have set an AL which set the record status to No which was initially Yes and hence the record will no longer appear in the Table as the qualification of table is to display only those records which are Yes.So now this record will still exist on the Child Form with status set to No but not in the table on Parent Form. Now as per ur suggestion of creating a filter on the child form of pushing fields to the same form for any record where counter on the current ticket is less than the counter on any other ticket can you elaborate on how to check counter on the current ticket is less than the counter on any other ticket? And if this works will it take a Large amount of time to update the records? Regards, Amey On Wed, Dec 24, 2008 at 9:57 PM, Opela, Gary L CTR USAF AFMC 72 CS/SCBAH gary.opela@tinker.af.mil wrote: I'm assuming that whenever you 'delete' a record, you are simply changing the status so that it doesn't show up on the table, then refreshing the table. You could add a filter that runs on the child form, the one from which the table is pulling its data, and have that filter run whenever the status is set to inactive, delete, whatever it is getting set to. The filter will then do a push fields to the same form for any record where counter on the current ticket is less than the counter on any other ticket found. Then, simply do the push fields of Counter = Counter - 1. I think that this should work for you. Order IDValue 1 Test 2 Test1 3 Test2 4 Test3 If you deleted 2, then the filter would decrement test2 and test3, giving you the following: Order IDValue 1 Test 2 Test2 3 Test3 I think this will work. There might be a timing issue, however, on the refreshing of the table. Since you are using a filter, I'm afraid that your table might refresh prior to all of the tickets in the child form getting decremented. Let me know if it works. Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Amey Bhosale Sent: Wednesday, December 24, 2008 10:21 AM To: arslist@ARSLIST.ORG Subject: Creating Decrementing value in Table ** Hi All, I am currently working on ARS 7.1 and have a query to ask you all. I have created a Regular Form on which i have added a table field(holding data into anothe form) which displays only those records which have a flag set to Yes On this Regular Form i have added a Add/Modify and Delete Button with some selection fields on top of this Table field which user will select and either add to the table or select any record from the table and delete it. When user selects the value from selection field and clicks on Add button the record gets added into the Table Field with an incrementing Value i.e 1,2,3 and so on..which i managed to do so... But now i have a problem in where in if i select a record from Table and click on Delete Button then that particular record show go from the table and then the next records order id shuld get updated to previous one and same follows through rest of the table. for e.g Now suppose i have added records as below Order Id Value 1 Test 2 Test1 3 Test2 4 Test3 5 Test4 Now if i want to delete second record i.e Order id = 2 then when it gets deleted the third record i.e Order id =3 shuld get updated as Order id = 2 and then all the below record shuld similarly get updated i.e fourth record i.e Order id =4 should get updated to 3 and Order id =5 to 4 Can someone let me know how this can be implemented? Regards Amey Bhosale Remedy Application Developer J . P. Morgan Services India Pvt. Ltd. Cricket on your mind? Visit the ultimate cricket website. Enter now! http://in.rd.yahoo.com/tagline_cricket_1/*http://beta.cricket.yahoo.com __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Creating Decrementing value in Table
Well, if you are wanting to increment the counter, then you are going to have to increment any ticket that has a counter greater than the counter on the current ticket. This could potentially get to be a very large update, depending on how many records are in this table. Now, how you accomplish this, let's say that you have your child form is named FormB and the counter field is called Counter and is an integer field. So, you put a filter on FormB that does a Push Fields. Server Name: CURRENT SERVER Push Value To: FormB Push Field If: 'Counter' $Counter$ If No Request Match: Take No Action If Any Requests Match: Modify All Matching Requests Fields: Name: Counter Value: Okay, scratch this idea. I forgot that on a push fields action, you have to push a value from the current window; you cannot use a value on the ticket to which you are pushing a value. I was going to have you put in 'Counter' - 1, but it won't work. Sorry for the delay. So, to still be able to do this using straight remedy: Let's say on your current form you have a table called Table1. The table has a few columns, some of which will be Counter from the child form, as well as Request ID from the child form. These can be hidden fields if need be. Now, whenever the user deletes a request from the table, you kick off an active link. The first thing this active link will do is to set a tmpInt field = $col_Counter$, which is the counter on the ticket that the user just deleted. The next thing this active link will do is to call an Active Link Guide. Make sure on this active link that you select Table Loop and choose the Table1. The active link guide will call another active link. This active link will do a set fields. The Run If on the Basic tab will be col_Counter tmpInt. The If Action tab will have a Push Fields action. The Push Fields will push to your child form where 'Request ID' = $col_Request ID$. The Name will be Counter (the Counter field on the child form) and the Value you are pushing would be $col_Counter$ - 1. I think this should work, sorry if it's brief, I would like to expand it more, but I'm heading out of the office for the week. I hope you all have a very very merry Christmas/New Year/Chanukkah/Qwanzaa/ and all of those other holidays/celebrations that I cannot spell. Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of AMEY BHOSALE Sent: Wednesday, December 24, 2008 12:18 PM To: arslist@ARSLIST.ORG Subject: Re: Creating Decrementing value in Table Hi Opela, Thanks for the response. On Delete Button i have set an AL which set the record status to No which was initially Yes and hence the record will no longer appear in the Table as the qualification of table is to display only those records which are Yes.So now this record will still exist on the Child Form with status set to No but not in the table on Parent Form. Now as per ur suggestion of creating a filter on the child form of pushing fields to the same form for any record where counter on the current ticket is less than the counter on any other ticket can you elaborate on how to check counter on the current ticket is less than the counter on any other ticket? And if this works will it take a Large amount of time to update the records? Regards, Amey On Wed, Dec 24, 2008 at 9:57 PM, Opela, Gary L CTR USAF AFMC 72 CS/SCBAH gary.opela@tinker.af.mil wrote: I'm assuming that whenever you 'delete' a record, you are simply changing the status so that it doesn't show up on the table, then refreshing the table. You could add a filter that runs on the child form, the one from which the table is pulling its data, and have that filter run whenever the status is set to inactive, delete, whatever it is getting set to. The filter will then do a push fields to the same form for any record where counter on the current ticket is less than the counter on any other ticket found. Then, simply do the push fields of Counter = Counter - 1. I think that this should work for you. Order IDValue 1 Test 2 Test1 3 Test2 4 Test3 If you deleted 2, then the filter would decrement test2 and test3, giving you the following: Order IDValue 1 Test 2 Test2 3 Test3 I think this will work. There might be a timing issue, however, on the refreshing of the table. Since you are using a filter, I'm afraid that your table might refresh prior to all of the tickets in the child form getting decremented. Let me know if it works. Thanks, Gary Opela, Jr. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Amey Bhosale Sent: Wednesday, December 24, 2008 10:21 AM To: arslist@ARSLIST.ORG Subject: Creating Decrementing value in Table ** Hi All, I am currently working on ARS 7.1 and have a query
