9 Replies Latest reply on Oct 21, 2009 9:15 AM by philmodjunk

    Delete portal row fails on first time only

    dwight

      Title

      Delete portal row fails on first time only

      Post

      I have a portal on a layout that is giving me some problems. I have written a script to handle the portal record deletions, when all of the fields in one portal record are blank the script will delete the portal record. This script is fired on record commit on the layout. Most times this script works fine. But if I do these steps first thing after opening Filemaker the Delete Portal Row will fail and a blank record (or more) will be saved to my table - Add a portal record by keying something, erase what you have keyed (leaving a blank portal record), commit all the records. Now if I go to the table those portal records should be in using a simple one-table layout and go to the end based on my sequencially numbered key I can't see the record. When I do a find using the sequentially numbered key > 0 I see one more record than when I do show all. What is going on here?

       

      I sure could use some ideas on fixing this. I am using FM pro advanced 10.0v3 on MAC OSX 10.5.8. Not using IWP or CWP.

        • 1. Re: Delete portal row fails on first time only
          philmodjunk
            

          Do you have a loop to check each related record?

           

          Post your script here so we can see it and maybe we can spot the problem for you. (To copy and paste a script, print the script as a PDF and copy the PDF text or use FMP advanced to generate a database design report and copy from it.)

          • 2. Re: Delete portal row fails on first time only
            dwight
              

            Yes, I have a loop to check every record. Now, since the Delete Portal Row will not delete the blank portal rows I even have a counter to exit the script after 24 tries failing to delete (normally 8 portal records is my max.), otherwise I had an infinite loop. (Thanks for the tip on copying and pasting a script.) I hope you don't find an error in the script, I am new to Filemaker but I have been a programmer a very long time.

             

             

             

             

            • Set Error Capture [ On ]
            • #
            • #
            • #
            • # Edit the BATTERY portal
            • Set Variable [ $LoopCount; Value:0 ]
            • Set Variable [ $SeqList; Value:"" ]
            • Set Variable [ $LastSequence; Value:0 ]
            • # Read the first record
            • Go to Field [ location_BATTERY::Sequence ]
            • Go to Portal Row [ Select; First ]
            • Loop
            • # Are we looping?
            • Set Variable [ $LoopCount; Value:$LoopCount + 1 ]
            • Exit Loop If [ $LoopCount = 24 ]
            • # This read previous switch is used when deleting portal records
            • Set Variable [ $Previous; Value:"N" ]
            • # If model is entered manufacturer must be entered also....
            • If [ not IsEmpty ( location_BATTERY::Model ) ]
            • If [ IsEmpty ( location_BATTERY::Manufacturer ) ]
            • Show Custom Dialog [ Title: "Error"; Message: "Manufacturer must be entered when Model is entered."; Buttons: “OK” ]
            • Go to Field [ location_BATTERY::Manufacturer ]
            • Exit Script [ Result: False ]
            • End If
            • End If
            • # Manufactured date can not be greater than today + 3 months....
            • If [ not IsEmpty ( location_BATTERY::MfgDate ) ]
            • If [ location_BATTERY::MfgDate > ( Get ( CurrentDate ) + 91 ) and location_BATTERY::MfgDate ≠ Date(1;15;3000) ]
            • Show Custom Dialog [ Title: "Error"; Message: "Manufactured date must be earlier than today. If unknown enter 1/15/3000."; Buttons: “OK” ]
            • Go to Field [ location_BATTERY::MfgDate ]
            • Exit Script [ Result: False ]
            • End If
            • End If
            • # Sequence number is the only field filled in....
            • If [ not IsEmpty ( location_BATTERY::Sequence ) ]
            • If [ IsEmpty ( location_BATTERY::Manufacturer ) and IsEmpty ( location_BATTERY::Model ) and IsEmpty ( location_BATTERY::MfgDate ) and IsEmpty ( location_BATTERY::Siemens ) and IsEmpty ( location_BATTERY::FloatVoltage ) and IsEmpty ( location_BATTERY::LoadVoltage ) ]
            • Set Field [ location_BATTERY::Sequence; "" ]
            • End If
            • End If
            • # Sequence is not filled in and other fields are....
            • If [ IsEmpty ( location_BATTERY::Sequence ) ]
            • If [ not ( IsEmpty ( location_BATTERY::Manufacturer ) ) or not ( IsEmpty ( location_BATTERY::Model ) ) or not ( IsEmpty ( location_BATTERY::MfgDate ) ) or not ( IsEmpty ( location_BATTERY::Siemens ) ) or not ( IsEmpty ( location_BATTERY::FloatVoltage ) ) or not ( IsEmpty ( location_BATTERY::LoadVoltage ) ) ]
            • Set Field [ location_BATTERY::Sequence; Get ( PortalRowNumber ) ]
            • End If
            • End If
            • # Sequence must not duplicate another....
            • If [ not IsEmpty ( location_BATTERY::Sequence ) ]
            • Set Variable [ $Check; Value:"/" & location_BATTERY::Sequence & "/" ]
            • If [ PatternCount ( $SeqList ; $Check ) ]
            • Show Custom Dialog [ Title: "Error"; Message: "Duplicate sequence numbers"; Buttons: “OK” ]
            • Go to Field [ location_BATTERY::Sequence ]
            • Exit Script [ Result: False ]
            • End If
            • Set Variable [ $SeqList; Value:$SeqList & $Check ]
            • End If
            • # When the entire line is empty, delete the portal record....
            • If [ IsEmpty ( location_BATTERY::Manufacturer ) and IsEmpty ( location_BATTERY::Model ) and IsEmpty ( location_BATTERY::MfgDate ) and IsEmpty ( location_BATTERY::Siemens ) and IsEmpty ( location_BATTERY::FloatVoltage ) and IsEmpty ( location_BATTERY::LoadVoltage ) ]
            • # Get the row number
            • Set Variable [ $RowNumber; Value:Get ( PortalRowNumber ) ]
            • # We can't delete the last line, so we need to see if there is another line
            • Go to Field [ location_BATTERY::Sequence ]
            • Go to Portal Row [ $RowNumber + 1 ] [ Select; No dialog ]
            • If [ Get ( LastError ) = 0 ]
            • # Delete the blank line
            • Go to Field [ location_BATTERY::Sequence ]
            • Go to Portal Row [ $RowNumber ] [ Select; No dialog ]
            • Delete Portal Row [ No dialog ]
            • If [ Get ( LastError ) = 0 ]
            • # Re-read the record number that was just deleted and set the read previous switch
            • Go to Field [ location_BATTERY::Sequence ]
            • Go to Portal Row [ $RowNumber ] [ Select; No dialog ]
            • Set Variable [ $Previous; Value:"Y" ]
            • End If
            • End If
            • End If
            • # Read the next record
            • Go to Field [ location_BATTERY::Sequence ]
            • Go to Portal Row [ Select; Next; Exit after last ]
            • If [ $Previous = "Y" ]
            • Go to Portal Row [ Select; Previous; Exit after last ]
            • End If
            • End Loop
            • #
            • # Exit the script
            • Exit Script [ Result: True ]
            • 3. Re: Delete portal row fails on first time only
              philmodjunk
                

              I can't quite follow what you mean by this statement:

               

              "Now if I go to the table those portal records should be in using a simple one-table layout and go to the end based on my sequencially numbered key I can't see the record. When I do a find using the sequentially numbered key > 0 I see one more record than when I do show all."

               

              This doesn't make since to me since you cannot see more records than those visible after selecting "show all records". Can you spell this out in more detail.

               

              Some basic design suggestions:

               

              It'll likely be much simpler if you use Go To Related records to process the portal records for embedded duplicates on your

              • 4. Re: Delete portal row fails on first time only
                philmodjunk
                  

                I can't quite follow what you mean by this statement:

                 

                "Now if I go to the table those portal records should be in using a simple one-table layout and go to the end based on my sequencially numbered key I can't see the record. When I do a find using the sequentially numbered key > 0 I see one more record than when I do show all."

                 

                This doesn't make since to me since you cannot see more records than those visible after selecting "show all records". Can you spell this out in more detail.

                 

                Some basic design suggestions:

                 

                It'll likely be much simpler if you use Go To Related records to process the portal records for embedded duplicates on your "one table layout" that displays the records from your portal. You won't have to manipulate the portal rows to get what you need and the empty last row for adding new records won't be there to be treated as a special case. Once your script loops through all the related portal records it can return back to your original layout to display the results, if any. To further simplify the script you might remove the code that checks for individual blank fields and use Field Options to specify a validation rule for them instead. That why filemaker can trap the error and display a message without you having to check for it in your script.

                 

                Your script would have the following general outline:

                 

                If [Count (PortalTableOccurrence:: PortalKeyField) > 0 /* there are related records to process for blanks */]

                  Freeze Window

                  Go To Related Records [Show only related records; From table: "yourportaltable"; Using layout: "Yourportallayout" (yourportaltable)]

                  #Put your loop here to step through the records, deleting blanks etc. here

                 

                 

                  Go to Layout [Original Layout]

                End If

                 

                Go To Related Records is a complex, poorly documented tool that nonetheless can be very useful. To learn more see this thread:

                 

                The Complete Go To Related Record

                • 5. Re: Delete portal row fails on first time only
                  dwight
                    

                  Actually, I do see more records when I do a find and select my key field > 0 than when I click show all records. I guess no one will believe that, but I believe that is why my Delete Portal Row can't delete the row. Or it is as if the record is only partly deleted.

                   

                  Thanks for the suggestions on simplifing my script. I will see if I can do some re-writing. I'd still like to understand why FM can't or doesn't handle what I wrote correctly 100% of the time.

                  • 6. Re: Delete portal row fails on first time only
                    philmodjunk
                      

                    Show all records shows all the records in the current table. You can't see any more records than that, hence my questions. Either there's some confusion as to which table is showing all records (this only applies to the table referred to in Layout setup..., it doesn't have anything to do with records displayed in the layout's portal), or your file has a damaged index and then nothing will work correctly.

                     

                    Simplifying your script, will help narrow down the possible issues and get us closer to making this work for you.

                    • 7. Re: Delete portal row fails on first time only
                      dwight
                         Picture 1
                      • 8. Re: Delete portal row fails on first time only
                        dwight
                          

                        I have written a script to use the GTRR command. And it is pretty slick what it can do for me. My problem at the moment is if I have an error message generated from the original layout and I execute a GTRR in my script I get an error 507 and I'm not sure what I should be doing about that.

                         

                        http://i210.photobucket.com/albums/bb98/demnoscom/Picture001.png

                         

                        This is a screenshot of record 121828 out of 121827. If I exit Filemaker and start it again that record will be 121828 out of 121828. 

                        • 9. Re: Delete portal row fails on first time only
                          philmodjunk
                            

                          Going by your posted picture, I believe your file has a corrupted index and possibly other damage to it's structure. There are several ways to repair this, let's try the simplest method first:

                           

                          Recover the file and see what messages are displayed by the recover process. If the recover process reports that the repaired file is OK to use or that no problems are found, test the recovered copy to see if the problem persists. If the recovered file works, replace it with an undamaged back up copy if at all possible. If not possible, I'd rebuild this file from scratch, copying or importing tables, records, layouts, scripts into the new file. It's a major chore but is one way to be sure that no hidden damage remains in your file.

                           

                          You can also try turning indexing on and off for key fields, saving a compressed copy of the file or importing all your data into a clone of your file. These methods can repair an index, but if there's other damage to the file, that damage remains and may affect your ongoing use of the file.

                           

                          When I looked up Error 507 in the filemaker help system, I found it means: "Value in field failed calculation test of validation entry option" -- so I'd check your field validations to see what might be the cause of that.