13 Replies Latest reply on Oct 3, 2011 1:46 PM by mgores

    Updating record field from same field different record

    michael_1

      Title

      Updating record field from same field different record

      Post

      Hi all I have a database running which when people go to sea they call in and are logged in. Now when they return they log off. They all have a unique number so when they sign off I want to have the origional record signed off as well.

       

      Can anyone help?

        • 1. Re: Updating record field from same field different record
          fmchris

          There are probably several ways to do this. One would be via scripting, and I'm sure someone would advise on that.

          The way I'd do it is to establish a relationship between the two files based on the seaman's unique ID. Then retire the "Sign Off" field in the original file (I would keep it for a while, or even permanently). Instead, define a Portal that contains the Sign Off field from the second file. That way you will be able to see the status from the second file as if it was there in the original file. And if you have other fields or scripts or calculations based on the original file's Sign Off button, you can make them dependent on the related Sign Off field.

          Basically, you don't actually need to import the Sign Off status from one file to another - relationships are a more elegant solution.

          • 2. Re: Updating record field from same field different record
            michael_1

            I must not have explained it properly but there is only one file with two tables. The tables are related by the membership number. From this I have one form which is the entry screen and many records come from this. Every time someone logs in a new record with a lot of information on it including a sign off field which would at this stage be empty and so it goes on and on but when someone signs off a new record is made but this time the sign off field is ticked now from this I want to sign off the origional record which could be bacl 50 records or more.

            • 3. Re: Updating record field from same field different record
              fmchris

              I don't understand why a new record is created when the person (who already has a Sign On record) signs off? Why not have the Sign Off field in the original record, and have it updated when the person signs off? (I'm not understanding the design very well).

              • 4. Re: Updating record field from same field different record
                michael_1

                It is not possible as you have many records and when someone calls in you don't know until near the end of the call he is signing off he could be signing on and I have made the form full of drop down and check boxes to speed up entries so to search for the member would be difficult it he wasn't there. There is a second reason as each seperate call is recorded for statistics so someone calling in to sign on and to call in to sign off is two records for statistics.

                I can achieve what I want manually by using the reports and ordering by number as the calls are adjacent so it can be done there but I want to get it a bit better.

                The way the script should work is.

                When the sign off is completed by a check box the script goes and finds exactly the same record by member number and as the sign off is blank it then updates the previous record to sign off.

                I can get a script to work but it updates all the records irrespective of member number.

                • 5. Re: Updating record field from same field different record
                  fmchris

                  Ok, I think I understand.

                  Any chance you could paste your script in here?

                  • 6. Re: Updating record field from same field different record
                    michael_1

                    I will have to go and make it up again as I deleted it when it didn't work.

                    • 7. Re: Updating record field from same field different record
                      mgores

                      Would it work to have a script that checks when you enter someones name whether they have an "active" record, find memberID and IsEmpty(signoff), if true it brings up that record so that you can check the sign off field, if false create a new record. 

                      • 8. Re: Updating record field from same field different record
                        fmchris

                        I'm not quite sure how you are defining "active record", but as for the rest of it -

                        Find MemberID - check

                        If Found

                             do IsEmpty(signoff) - check

                        (But)  if True, you simply want to Set Field (signoff)

                                 if False, then create new record - check

                        Else error

                        End if

                        • 9. Re: Updating record field from same field different record
                          mgores

                          by "active" record I meant someone who was signed in but not signed out, assuming that once they were signed out the record becomes a history of that deployment and anyone having a record that does not have a value in the signoff field is at sea.

                          I would set up a layout with a global field to enter the member number when the call comes in, then have a script perform a find with 2 requests,  GlobalField =memberID  and IsEmpty(signoff).  If there is a found record (the member was at sea and is calling to sign off) go to the layout where you can edit that record.  If the find returns a "no record found" error (the member is not at sea and is calling to sign in), go to the layout where you enter data and create a new record.

                          • 10. Re: Updating record field from same field different record
                            fmchris

                            Sounds good - except for "go to the layout where you can edit that record" - you can use the Script to Set Field (signoff) to whatever value you want in there.

                            • 11. Re: Updating record field from same field different record
                              mgores

                              I thought that too, but the statement "you don't know until near the end of the call he is signing off "  made me think it might be better to just go to the layout first and let the user determine whether it was really a signoff call or maybe some other data needed to be modified.  After re-reading that paragraph, you may also want to have the script check that the MemberID is valid - in case the call is for a new member and a new member record is needed.

                              As far as having 2 records to record the statistics on the # of calls - you could have a calculated field that Case(IfEmpty(signoff); 1: 2), then total that calculated field to have the total # of calls.

                              Another option may be to show the signon and signoff fields in a selfjoined portal (filter be decending dates so the most recent is on top).  This would allow you to enter the data in the sign off field without have to search back through all of the records.

                              • 12. Re: Updating record field from same field different record
                                fmchris

                                Mark, I've been replying to you as though you were the OP !! (I didn't have the energy to re-read the whole thing each time!)

                                Now I see you're another helper, and the OP was Michael.Embarassed

                                Silly me Laughing

                                • 13. Re: Updating record field from same field different record
                                  mgores

                                  NP  Laughing.  Thought we were bouncing ideas off each other.  Sometimes going back and forth with the other helpers gives me ideas or helps me get through me through my own problems in developing.