Record lock issue when performing lookup within current file
Josh Reader
josh.reader at mercuryelectronics.com
Fri Jun 17 08:46:34 PDT 2016
Hi Nancy,
Thanks for taking the time to explain everything, now I know what commands
are needed and when to use them properly. I’ll change my matching to go
off record # and I added a write command to the lookup that is doing the
updating about 10 minutes ago and activated it live. So far no calls from
the purchasing team…
Josh
*From:* Nancy Palmquist [mailto:nlp at vss3.com]
*Sent:* Friday, June 17, 2016 11:36 AM
*To:* Josh Reader; nancy.palmquist at vss3.com
*Cc:* filePro Mail Group
*Subject:* Re: Record lock issue when performing lookup within current file
Josh,
On 6/17/2016 11:02 AM, Josh Reader wrote:
Hi Nancy,
I believe I am doing the lookup the same way you mentioned. I am looking
up by PO# via an index that sorts by PO#, Line, then Release. Then I'm
stepping through all records for the PO match and only updating those
releases for the line I'm on. I'm also skipping the record I'm standing on
by checking to see if the release #'s match (I'm already matched for PO#
and line # at this point so it is unique). I have some questions about
lookups:
Better to check RECORD NUMBER than checking for matching data. You may not
get the same results.
1. What is the rule of thumb for closing lookups? I used to close them
every time but now I find things run faster if I put a line that says IF
@RS eq @TS, then close. That way I close it at the end on the last record.
If you are making changes to the records, use WRITE to post the changes.
When you are done with the lookup section, you can use CLOSE to release the
handle to that lookup. A WRITE unlocks the record and writes the record.
A CLOSE does that and in addition disconnects the connection to that file.
So if you do a new lookup to that file, you have to reconnect. You will
find that in a posting processes, issuing a WRITE instead of a CLOSE on
each record will speed up the process in a noticeable way.
BTW - CLOSING at @RS eq @TS is a waste of effort since the end of the
process will close everything anyway. But you should issue WRITE commands
on each record as you complete your posting to that lookup.
2. Does the close command affect anything with record locking?
YES - CLOSE unlocks the record as does WRITE.
Thanks,
Josh
On Fri, Jun 17, 2016 at 10:33 AM, Nancy Palmquist via Filepro-list <
filepro-list at lists.celestial.com> wrote:
Josh,
If you are sitting on one record in a group and you want to update all the
records in that group, you can do lookups to each record and before trying
to post to them, check to see if the record you just found is the current
record number.
For example,
lookup mypo=myporecords k=(ponum) i=a -nxp 'index A is built on PO
Number and variable ponum is defined appropriately.
loopit If not mypo
Then: goto finish
if: mypo(1){"~" ne 1{"~" 'assumed number might be literal
then;goto finish
if: mypo(@rn) eq @rn 'this is checking to see if
I am looking at myself
then: 5=5+whatever;getnext mypo;goto loopit
If: 'this is
updating all the others.
then: mypo(5)=mypo(5)+whatever;getnext mypo;goto loopit
All this assumes you are on one of the records assigned to that PONumber
that you want to adjust.
Some things might cause this to conflict with other users. You want to do
your lookup in such way that you start on your PO number and go next to
next until you hit the next po number and then stop. If you are looking at
all the records, you could certainly hit another user's record.
I hope my rough programming is readable.
Another way to deal with record locking is to put the users in another file
entirely and just do lookups to the file(s) in question. I use this method
on complex processing and I know one user is never going to be dealing with
the data another user might use. Eliminates all kinds of issues, but the
programming can be a bit more complex since all data must be pulled in,
processed and written out again.
Nancy
On 6/15/2016 4:57 PM, Josh Reader via Filepro-list wrote:
1. CentOS 7 and filePro v5.8
2. It's triggered on save
3. Yes there could be someone else updating or creating a new record but
it shouldn't be for the same PO #
On Wed, Jun 15, 2016 at 4:47 PM, Richard Kreiss <rkreiss at verizon.net> wrote:
First Question: what Operating system and version of filePro are being run?
Second question: Is this subroutine triggered by an @key? If so, that
will lock the record the user is looking at. There really is no way around
that issue.
Third Question: Are multiple people putting in details at the same time as
someone is trying to run this routine?
Richard Kreiss
GCC Consulting
-----Original Message-----
From: Filepro-list [mailto:filepro-list-
bounces+rkreiss=verizon.net at lists.celestial.com] On Behalf Of Josh
Reader via
Filepro-list
Sent: Wednesday, June 15, 2016 3:27 PM
To: filePro Mail Group <filepro-list at lists.celestial.com>
Subject: Record lock issue when performing lookup within current file
Hi All,
I am trying to program a feature for our purchasing dept and running
into some
lock issues. Currently when our buyers have a PO line with multiple
releases they
have to manually add the total cost of the line and then hand-type that
into
filePro for each release. If they make a mistake, then they have to
touch all
releases on the line to manually synchronize the line cost. I added
code to do a
lookup within the file and to only apply for my own ID while I tested
it. During
test everything worked perfectly.
I didn’t have to enter the line cost field at all. As I added releases
it
automatically added the totals and then went back through the other
records
and synchronized as expected. Once I made it live however, and multiple
people were using it at the same time, I started getting calls about a
message
saying waiting for record # to unlock. I commented out the subroutine
that runs
the sync after the screen command @update, got everyone back to the main
menu and in again and they proceeded on the old way. Is it possible to
sync a
field across multiple records of a file that
2-3 people are adding records to regularly? If so what is the proper
way to do
the lookup?
Below is the subroutine that does 2 lookups. The first lookup adds up
the qty
and cost for the line and stores them in variables. The second lookup
applies
those values to all releases for the line. This happened a few weeks
ago and I
believe I removed the close command from the lookups thinking that may
make
a difference but hadn’t tested it since then. Also I’m using the same
lookup
name but it is my understanding that only one lookup to a file is
allowed at once
and when a second lookup is invoked the first one closes automatically.
We are
on filePro v5.8 running on CentOS 7. Any help would be greatly
appreciated.
syncLN:' ---------------------------------------------------------------:
' Subroutine to sync line qty and line cost:
:' Set variables for line cost and line qty :lc=79; lq=43:
:' Lookup within reqform by po #
:lookup req = reqform k=24 i=E -nx:
:loopSYN:not req
:goto syncLN2:
:req(24) ne 24 ' PO #'s no longer match, return
:goto syncLN2:
:req(22) ne 22 ' Lines are not equal, get next record
:getnext req; goto loopSYN:
:req(23) eq 23 ' Don't access current record
:getnext req; goto loopSYN:
:' Add line cost and line qty
:lc=lc+req(79); lq=lq+req(43):
:' Get next record in lookup and process it :getnext req; goto loopSYN:
syncLN2:' Lookup within reqform by po #
:lookup req = reqform k=24 i=E -npx:
loopSY2:not req
:return:
:req(24) ne 24 ' PO #'s no longer match, return
:return:
:req(22) ne 22 ' Lines are not equal, get next record
:getnext req; goto loopSY2:
:req(23) eq 23 ' Don't access current record
:getnext req; goto loopSY2:
:' Update record with line cost and qty
:req(78)=lc; req(44)=lq:
:' Get next record in lookup and process it :getnext req; goto loopSY2:
Thanks,
Josh
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.celestial.com/pipermail/filepro-
list/attachments/20160615/9c378937/attachment.html>
_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes
http://mailman.celestial.com/mailman/listinfo/filepro-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <
http://mailman.celestial.com/pipermail/filepro-list/attachments/20160615/4d768f00/attachment.html
>
_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes
http://mailman.celestial.com/mailman/listinfo/filepro-list
--
Nancy Palmquist MOS & filePro Training Available
Virtual Software Systems Web Based Training and Consulting
PHONE: (412) 835-9417 Web site: http://www.vss3.com
_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
Subscribe/Unsubscribe/Subscription Changes
http://mailman.celestial.com/mailman/listinfo/filepro-list
--
Nancy Palmquist MOS & filePro Training Available
Virtual Software Systems Web Based Training and Consulting
PHONE: (412) 835-9417 Web site: http://www.vss3.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.celestial.com/pipermail/filepro-list/attachments/20160617/0bf3ecec/attachment.html>
More information about the Filepro-list
mailing list