1 2 Previous Next 15 Replies Latest reply on Oct 9, 2013 4:04 PM by philmodjunk

    Database not computing

    GaryVogt

      Title

      Database not computing

      Post

           I wish I could just send the whole file.  

           I have a database that was working fine until i inserted records between existing records.  It is nothing more than getting the last value of a previous record and using it as the first value of the next record.  

            If(Get Record Number Previous=0; ""; GetNthRecord ( Ending Balance ; Get Record Number Previous ))

           Seemed to work fine until I inserted some new records.  the record ID has no correlation to the record number.  

           Very frustrating.  I should be able to get a value of a previous record and use it.  I can't.

        • 1. Re: Database not computing
          dbail22@comcast.net

               You cant INSERT a record between records.  You can create the record then sort it as desired to make the current sort in the order you desire.  Unfortunately there is no permanent sort in FM.  You must export while sorted then import to a clone. Remember RecordID and RecordNumber are different.

          • 2. Re: Database not computing
            philmodjunk

                 If by "insert" you mean that once sorted they are now between records created earlier, it sounds like all you need do is make your calculation field an unstored calculation instead of a stored or auto-entered calc and then they should update automatically.

            • 3. Re: Database not computing
              GaryVogt

                   Also, since I don't see a button to push to get to a new post, I'll post here.  

                   On previous versions of FileMaker, I could select the default font, font size, font color, etc and the look of the field.  I can't figure out how to do it on this version.  

                    

                   Come on guys, make it easy, not hard.

              • 4. Re: Database not computing
                GaryVogt

                     Every field is unstored.  

                     I know how to use filemaker.  I've been using it since it debuted in the 80's.

                     my records are sorted by date.  i inserted a record between other records and now the last several records don't update.  

                     I created a clone already and imported the records.  No change. the record number from Get ( RecordNumber ) and previous record number from Get ( RecordNumber )-1, work fine.  for some reason., it doesn't update correctly on the last several records.  All I get is "?" in the fields 

                      

                     Let me send you the file.  You figure it out.

                • 5. Re: Database not computing
                  philmodjunk
                       

                            I know how to use filemaker.  I've been using it since it debuted in the 80's.

                       As I have I But I didn't know that about you and what I posted matched what little you had posted about your issue.

                       There is also an upper limit as to how many records can be present in your found set before this method of referencing data in other records of your found set fails having reached a limit in FileMaker's system resources for supporting that method. In FM GO, this limit is reached with a much smaller number of records.

                       If this is the case, your last few records are probably showing question marks. Is this the case?

                       

                            Let me send you the file.  You figure it out.

                       No thank you.

                  • 6. Re: Database not computing
                    GaryVogt

                         Beginning balance: If (Purchase Invoice="Balance fwd"; ""; Ending Balance Previous)

                         Invoice: Number

                         Date: Date

                         Amount: Number 

                         New balance: Unstored, = Beginning balance + Amount

                         Payment: Number

                         Ending balance:  Unstored, = Beginning Balance + Amount - Payment Amount

                         Get Record Number: Unstored, = Get (Record Number)

                         Get Record Number Previous: Unstored, = Get (Record Number) - 1

                         Ending balance previous: Unstored, = If(Get Record Number Previous=0; ""; GetNthRecord ( Ending Balance ; Get Record Number Previous ))

                         Get REcord ID: = Get (RecordID)

                          

                    • 7. Re: Database not computing
                      GaryVogt

                           That's the entire database.  Like I said, it worked fine until I entered records near the beginning of the file.  Now they don't work near the end of the file.

                      • 8. Re: Database not computing
                        GaryVogt

                             I created a clone and tried exporting to the clone.  It creates something called .tab  Then I can't open .tab

                        • 9. Re: Database not computing
                          GaryVogt

                               Imported from .tab to clone.  still doesn't work

                          • 10. Re: Database not computing
                            GaryVogt

                                 the database has 80 records.  Certainly FM can handle 80 records

                            • 11. Re: Database not computing
                              philmodjunk

                                   Get ( RecordID ) and Get ( RecordNumber ) are two different functions with two different results. Get ( RecordID ) returns an internal serial number that uniquely identifies each record and numbers them in the order that they are created. The only way you can get a different value out of this function for the same record is to import the records into a different table. Get ( recordNumber ) returns the current position of a given record in the current found set or in a set of related records (such as it's position in a portal row.)

                                   Frankly, I wouldn't use getNthRecord for this as the same result can be set up much more easily with a running total summary field instead.

                                   I also would expect this to work with more than a found set of 80 records but suspect that the upper limit depends on many other factors--possibly including the specific computer on which you are doing this--but that's a guess on my part, I could easily be wrong.

                                   Are you seeing a blank field or question marks in the calculation field.

                                   Please keep in mind that I am an unpaid fellow database user who has chosen to answer your question simply because I enjoy doing so.

                                    

                              • 12. Re: Database not computing
                                dbail22@comcast.net

                                     The only time I have personally seen records with a ? in them is when something bad happened.  Bad meaning internally to a table.  The only way I have found fixes is to delete those records and start again.  I guess it is not the only way, I was able to use the recover process once and that worked.  Good Luck!

                                • 13. Re: Database not computing
                                  philmodjunk

                                       The question mark simply means that FileMaker is unable to display data in a field. That can be as simple as a field that is too narrow to fully display its contents, a calculation that produced a math error such as divide by zero or a recursive stack overrun--what can happen here with GetNthRecord, a syntax error in ExecuteSQL. And then it also can represent something more series due to a corrupted index--which can be fixed with out recovering the file or actual damage to the underlying table.

                                       Does that mean that you are not seeing any question marks?

                                       Can you upload a screen shot of exactly what you are getting?

                                  • 14. Re: Database not computing
                                    GaryVogt

                                         I used Running Total of Total and set the GetRecord to the previous running total.  It works that way.  Not sure why it wouldn't work based on my calculation fields and getting the previous record.  

                                    1 2 Previous Next