Jump to content
Sign in to follow this  
Baldrick

Auto Enter Value from previous record

Recommended Posts

Hi! This is probably a very stupid question, but how can I Auto Enter a value in one record based on the value of a previous record. What I'm trying to do is produce a milage book, where the end milage on one record (Day) is the start milage on the next. Look Up's seem to only work with other tables and I'm less than useless with scripts....

Any suggestions would be greatly appreciated,

Ben.

Share this post


Link to post
Share on other sites

this questions might concern two problems, first is how would u list or sort the records, since it will determine which is the previous record and which is the next.

the second problem may be solved by using a global field which stores previous record's end milage...

then what's left is how u would implement to control ur program flow to make it work...

Share this post


Link to post
Share on other sites

I have 365 records which are sorted by date. Each record represents a day in 2003 and contains fields for Start and End milage (currently empty), Work milage and Private milage.

My Work data is already in the database, as is the start milage on 1/1/03. I need to be able add Private milage to pad the total so it matches the vehicles actual milage at the end of the year. I then need to be able to print a report for each month with a total of Private and Work miles.

I'm sure this must be really easy, but apart from copying and pasting 365 times, I don't know how.

Cheers, Ben.

Share this post


Link to post
Share on other sites

Create another table occurrence of the first table. Relate the two by record number--the record number minus one from the second table to the record number of the first table. Then place the beginning mileage field of the second table as the ending mileage on the layout of the first table. All of this assumes, of course, that the records are contiguous.

Share this post


Link to post
Share on other sites

Transpower! Thanks for your suggestion.

Unfortunately I couldn't figure how to sort the Record number increment with just one table, but managed it with two. The problem now is that after editing a number, I have to Relookup Field Contents on three different tables. This only updates the entry immediately after the changed value. To make a change in January I'd have to cycle through the 3 tables and Relookup 1050+ times!

Would writing a script for this be a decent solution(I'm not sure how fast FM will do 1050 consecutive Relookups), or is there a better way of doing this?

Cheers, Ben.

Share this post


Link to post
Share on other sites

Great - I just threw my first script together to do this and it worked. Shame that the Relookups take so long, and that you have to hit the OK button before each one.

Arrrggh!!!!! I think that the End Loop if Record Number = 365 was a bit optimistic.

Share this post


Link to post
Share on other sites

Baldrick, there may be a simpler way for you. You don't need a script at all - or even repeated relookups, if I understand your situation correctly. All you need is to capture the prior Ending Balance from the last record. Then why are you sorting them? You won't need to sort them if the relationship looks one day backwards and not one record backwards.

I've attached a simple (demo) process which is very easy to implement. I have added field comments to hopefully clarify it. It requires one self-join from your Day (date field) to Day - 1 (date field). It also uses auto-enter serial number to increment the date by one each time a new record is added. If I were attacking this problem, this is how I would handle it. I hope you will find it easier than relookups or scripts. wink.gif

There are no records but I've pre-filled the starting global with 500 (indicating your starting mileage) to prime the process so replace that figure on the first record with your real start figure. You will notice that the starting 'serial date' is 731216 - which is 1/1/2003. Add a record, fill in your mileage, add another record and see how it works for you. The auto-enter incrementing date is unnecessary of course; but I liked it and thought you might find it useful. If you ever go back and change/add Personal Mileage - or even add all personal mileage once at year end, script with only one Relookup Field Contents [ No dialog; Day ] will suffice.

LaRetta

Mileage.zip

Share this post


Link to post
Share on other sites

Baldrick, I might explain further how to incorporate what you have into this. I was very short on time yesterday ...

Start with no records in this demo file. Be sure your start SerialDate is 731216 (check Auto-Enter serial). Import your work mileage directly into this as new records. No need to import your date because it will be created (I assume your work miles are in order). Be sure 'Perform Auto-Enter' is clicked because this will increment your serial and write your date. Then set your global Start Mileage to your real figure.

To pad your personal mileage in this process, you can run Replace Contents through it. What figure you use is up to you, but you probably don't want the same day mileage every personal day. Your real ending mileage - (starting mileage + Work mileage) will give you total personal. You could then determine the percentage difference of personal to work mileage (PersonalMileage divided by WorkMileage). If you don't care if the same figure PersonalMileage lists every day, then just divide by 365.

Then place your cursor in personal mileage, select Records > Replace Field Contents and enter calculation similar to: WorkMileage * .04 (or the percentage difference). This will insert PersonalMileage through all the records. Then perform Relookup on Day (your date field) and everything will adjust for you - line by line.

LaRetta wink.gif

Share this post


Link to post
Share on other sites

"Relate the two by record number--the record number minus one from the second table to the record number of the first table. " Transpower, it is never recommended to relate on record number. I assume you meant RecordID? If related on Record Number, any sort or find will mess it up. And the -1 would not work on RecordID anyway. Not a good recommendation, if that is what you meant. Relating on date makes more sense here anyway.

Share this post


Link to post
Share on other sites

LaRetta - You are an absolute star , Thankyou!

You've come up with exactly what I needed, and it works. I'm in a bit of a mad rush at the moment but I just wanted to let you know you've reduced my stress by about 95%.

Thanks again, Ben. Bow.1.gif

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Similar Content

    • By Tony Morosco
      I'm a botanist, and the tables I am working with are for tracking botanical garden collections. The data represents plants in the garden, and the plants are tagged and show up in the database.  The tables I am working with were created in FMP 7, and I'd like to open them up in FMP 11 (or later.)  The system hasn't been used in years, but still has valuable information.
      One of the tables is giving me problems using the FMP convert and recover commands.
      These tables are all inter-related.  The main table is the Accessions table, which contains records for all of one kind of plant, from the same source, received on the same date.  It is basically a museum standard.
      The other tables are related to each other through this one main table.  The Species table is related to the locations table through the Accessions table. 
      (i.e.  table A relates to table C through the table B, the intermediary)  
      From the Locations table, we can't see the the species information unless the accessions table is present.
      When issuing the open command on the main table to convert the database to FMP 11, I get the message:
      "Accessions.fmp7" is damaged and cannot be opened.  Use the Recover command to recover this file. When using the Recover command from v. 11, I get another message:
      WARNING: problems were detected while recovering the database.  Please review the Recover.log file to see where problems were found and their severity.  The recovered file should NOT be used going forward; copy only the most recent work from it into a backup copy of the original file. Recovery results:   File blocks: scanned and rebuilt 563 blocks, dropped 214 invalid data blocks.   Schema: scanned fields and tables, 1 items modified   Structure: scanned; 1 items modified   Field indexes: rebuilt  
      Opening the recovered database, there are only three records present.  There should be hundreds.  So obviously I am looking on how to wrangle this database open.
      I've attached the log file here, as well as the database structure map.  
      The other files have converted just fine.  But since the main table won't open, we are kind of stuck.
      I can share the files with you through Dropbox or whatever, if needed.
      Please let me know any thoughts you have, either basic or advanced.  And ask for any clarifications or additional questions.   :-)  
      Thanks!
      -Tony
      Recover.log

    • By Tumma K
      Hello, All!

      I am an aspiring developer for Filemaker. The company I work with is stuck in the past working off of Filemaker Pro 4.1

      I was given the task of bringing us up to Filemaker Pro/Server 13. So far my conversion prototypes are successful but we recently had a layout issue that can only be fixed in versions 3-6 (as the file is an .fp3) I work off of a macbook while our network is all Windows 7. In order for me to repair the layouts without tampering our active database, I decided the best option is to repair a copy of our solutions off the network. Unfortunately, when I go to download the trial version of Filemaker Pro 6 off of the respected website, the file is corrupt! I've tried multiple times, with different extraction apps and in different directories.

      My question is;

      Does anyone know a place where I could obtain version 6 (or better yet, 4.0) for an OSX computer? I've looked everywhere!
       
      Thank you for your time,
      Tumma K.
    • By MrEddByrnes
      I'm hoping my question can have a happy ending. In the mid-90's, I purchased Filemaker 3. When Filemaker 5.5 Pro was released, I bought the update CD, which requires the user to either have FM 3 installed or to have the installation CD for FM 3. I've used it all these years, most recently with Windows XP Pro, and it has worked just fine. The databases I began with were long ago converted to FM Pro 5.5 databases.
       
      I'm still using FM Pro 5.5 on a laptop with WinXP Pro, but in 2013, I purchased a PC with Windows 8. I haven't been able to install FM 3 on it, therefore can't install FM Pro 5.5. I am retired and rarely use Filemaker, but I have a few Filemaker databases I'd like to add to my Win 8 machine. I don't feel it's worth upgrading FM for the sake of using a couple of databases.
       
      Has anyone else run into this situation and/or have a (possible) solution? Is there perhaps any other software that can read FM 5.5 databases? Thanks in advance for your help.
       
    • By bmill
      I am using a custom filemaker solution for medical office billing written with fp5 running on a mac with snow leopard. In addition, I have a patient management db (which I wrote) that is linked through pt. ID number to the billing program allowing transfer of some demographic information (name, DOB, etc).
       
      Other than being limited by hardware restrictions, the billing program serves our needs for now and upgrading to fp12 will take some time (and money).  In the meantime, I am upgrading my pt. management program to fp13 and would like to move new patient demographic information from the billing program ( fp5 running on snow leopard through Parallels) and the new pt management program ( fp13 running on OS X 10.9) on the same mac.   
       
      Ideally, demographic information would be entered once into fp5 and then a scipt would make the data available for fp13.
       
      Any ideas on how to make this work?
    • By randyinla
      Hi, can anyone tell me why my on-line database might have stopped allowing me to delete records?  All of my access privileges and passwords are correct.
       
      thanks!
×

Important Information

By using this site, you agree to our Terms of Use.