Jump to content


  • Posts

  • Joined

  • Last visited

Profile Information

  • Gender
  • Location
    Portsmouth NH

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

BFrost's Achievements


Explorer (4/14)

  • First Post
  • Collaborator
  • Week One Done
  • One Month Later
  • One Year In

Recent Badges



  1. Post Script (that's a pun ): created a new script in FP12 file to replace the one that didn't work correctly after conversion from the FP11 original. Works fine.
  2. Mac OS 10.6.8 Just sharing. It's not a big problem for me right now but it is aberrant to the script. I believe it's a conversion issue. I couldn't reproduce the problem in a new file. I did get the aberration converting from a FMP11 file, clone attached, no account/pw. Run the script "Create Records". Close the file. Convert the file to FP12. Close it and open it again. There are two fields in the FP11 ScrollToEnd file: RecNo (not indexed) and Sort populated with a random number on creation. There are two scripts. One creates 5000 records. The other executes when the file opens sorting the the records by the Sort field if there is a found set ≠ 0 then scrolling the window to the end of the file. ScrollToEnd_1.fmp11.zip
  3. I have the same problem as the original post. No portals involved. FP12.0v3 Advanced Check register kept sorted by Date (increasing) Credits (Decreasing) Debits (Increasing--largest debit first) Currently 4,529 Records On First Window Open trigger Scroll Window [End] or Go To Record [Last] Scroll Window [selection] Both currently end up at about record 4256. That's 273 records—at two lines per record— shy of the correct destination. Manual activation of the same script scrolls to the correct destination. Original DB in FP7 (FMP 11.0.4) operates correctly on opening.
  4. Here's an easy way (attached) Running_Balance.fp7.zip
  5. Good catch, Søren Dyhr, I do have a stored balance value that comes from a lookup referencing the running balance. Self join from within the same record to get the balance value. Relookup was forced by resequencing records. The getNthRecord function by statement period comes up with the right balances as long as the starting balance is the first record in the same table as all other deposits and withdrawals. I just verified that in my own file comprising 64 statement periods. There is no chained dependency, just a running balance retrieved by the GetNthRecord function on "Amount' with records sorted (as defined in the "Statement Period" relationship: deposits before withdrawals within date). I agree one column is all you need for Amount as long as the first record the Amount is the starting balance. However, for display purposes and to mimic certain banking statements, I like to parse Deposits & Withdrawals with (unstored) calculation fields. laneo, what both Søren Dyhr and I are saying is the fields and table for deposits and withdrawals should be the same, the distinction can be made by the arithmetical sign at the time of data entry in amount, positive (no sign) for deposits and negative (-) for withdrawals, or, an additional attribute field to indicate Deposit or Withdrawal. Having an additional attribute field does require you to cue addition or subtraction by a calculation recognizing that attribute to proceed with the appropriate arithmetic when calculating a balance. I suppose it's a mainly a matter of taste, but I like using the sign at data entry. It sounds like you're almost there anyway. ********************************* Søren Dyhr, perhaps you could expand on the term, Luca di Pacioli method, I did a search on the forum and in google. I found no description by that name, any help?
  6. Note: This requires the GetNthRecord function. I can't remember when that started. Create a global numeric field for Starting Balance. Deposit and Withdrawal entries are calculation fields where: anything greater than zero in the Amount data entry field is a Deposit, anything else is a Withdrawal. Create a field for numbering records in Serial order when all records are present and a script to reserialize before reporting. Create a record balance field to distinguish a starting balance from any other deposit or withdrawal that looks like this: If ( RecNo = 1 ; Starting Balance; 0 ) + Deposit + Withdrawal Create a running total field summarizing this balance field at each transaction. Define a calculation field identifying the Statement Period for each record concatenates the year and month of each transaction with the day number for the end of statement period. So Statement Periods ending on the 15th of every month would have numerical values in the form YYYYMM15: year(date) * 10000 + month(date) * 100 + 15. Create a self-join relationship by statement period specifying related records in date order with credits appearing before debits. Create a field that counts the number of records in the current Statement Period. Create a field using the GetNthRecord function that gets the running balance value for the first related record in the statement period MINUS whatever the amount is for that record. Create a field using the GetNthRecord function that gets the running balance value for the last related record (record count number)in the statement period. Run the reserialize script to assure records are in the proper order--really just to make sure the starting balance is the first record in the table so it is automatically included in the running balance. I suppose you could manually flag the the starting balance period and avoid reserializing. I might have overlooked something here but give it a try and we can refine as we need to. Alternatively, keep in mind you could total all deposits and all withdrawals in a statement period and have a calculation field that identifies the previous statement period. Create a relationship from the current statement period to the last statement period and catch the running total for that way. Keeping track of either method can give you headaches. :crazy2:
  7. I'll give it a shot. First, I'll use an example with two tables. One table (Aiming Table) will contain the record that has portal records in the other table (Target Table). The exercise is to duplicate a record in the Aiming Table and to duplicate corresponding related Target Table records so they are targeted only by the duplicate aiming table record in a one to many relationship. Let's assume that Aiming Table records point to Target Table records based solely on the unique record number in Aiming Table records. That is, a portal associated with an Aiming Table record would show only those Target Table records with the Aiming Table record's unique record number in the target record's target field. That's the overall relationship for viewing and it also could be enabled to create subrecords in the Target Table, if you want. Now, forget the Aiming Table for a moment. In the Target Table create a self-join relationship that involves: Field to same-field relationships for each datum to be duplicated, Global Number field to Target Number Field to be used by the Aiming Table duplicate record. Enable create new records for this relationship. Now, back to the Aiming Table. Create a script to: Isolate the record to be duplicated and duplicate it. Set the Global Number field in the Target Table to the unique record number from the duplicate record. Return to the original aiming record. Go to (show only) related records from the original Aiming Table record portal. Invoke a looping script that starts with the first related record to set a value in any field in the self join that's not involved in the creation relationship to any value you want. I often use someting like, "Set field Dupe::Account to Get ( AccountName )." The duplicate records have been created but are not visible during the process. Exit the loop after the last record. If you want, create a relationship between the Global Number field in the Target Records table and Record Number in the Aiming Records table and us it to return to the new aiming record (Go to Related Record) for proofing. I think that's it. Let me know if I can clear something up for you. HTH
  8. Maybe a little confusing but the portal records showing for the current record will show for an exact duplicate of the current record. This is because the field values pointing the relationship to the portal records are exactly duplicated and you have gone from a one to many relationship to a many to many relationship. enthusiast is correct. If you want to create a new set of records in a one to many relationship that is exactly analagous to what you had with the original record and its portal records, you can create a script that isolates the portal records you want to duplicate: create new and unique value or values in a field or fields that will point to the duplicated portal records. As each portal record is duplicated, it must be edited so its field or fields comprising the relationship target contain the value or values (to be) assigned to pointing field or fields in the duplicate of the original pointing record. I say field or fields for both the pointing and targeting values in the relationship because FileMaker Pro 8 allows complex relationships and can facilitate creating duplicate portal records under in a special relationship just for creating duplicates where only one value is changed among all the fields in the record to be duplicated -- such as the Record ID for the pointing record. All the rest of the fields can be part of the creating relationship. This method automatically duplicates all data when new a Record ID is assigned by the creating relationship. What version of FileMaker are you using?
  9. I may by unclear on what you're trying to do but the basic problem seems to be there is no relationship to follow from the Training Supervisor table to the Student table. The upper portal on the Training Supervisor Layout is mislabeled, it should be Training Session. The relationship for that portal is Training Supervisor::Training Supervisor. The lower portal has no related field in the active table to point to the appropriate student records: To which supervisor is a student assigned? Do you want the training session to be selected based on students' training types? If so, starting with the Students table, the Training Type field could point to the appropriate supervisor of Training Type and then from supervisor to location for Training Type. The pointing field could be conditional on passing the "graduate" status test. Similarly, Which supervisors have sessions at what locations. If you're aiming in the other direction (toward students), are graduates excluded from any type of training here? If so, the indexed field in the Student table could be conditional (as above) if Grad Status = "Graduate"; empty; otherwise indexed value. Please, say more about what you want to have happen.
  10. You've almost answered your own question. Make a new relationship to Parts with "All" as the qualfier.
  11. I don't understand why it would make a difference, either. It might help me to have the database to look at instead of trying to imagine it, though. I don't see why this wouldn't work in a multi user environment. Do you have a means of testing it now in that regard?
  12. OH! That LHS and RHS! LOL. Thanks, Lee.
  13. Comment, you're right about that. I wasn't clear but I was referring to switching portals in my hurried reply, not the numerical replacement. Switching poertals is quick, easy to set up, and involves no editing at all. My bad on the confusion. I shoulda, woulda, coulda, etc. been more attentive. Cood catch.
  14. I believe you are mistake in two ways. 1. Switching layouts is a local function. 2. Multi-user environments has been mentioned only by you but I can tell you that even without using server, switching portals in an LAN environment is not a problem. In fact it is probably the most efficient solution because no records are being edited just to be viewed in a different sequence. Any solution that involves changing any value in any record is an opportunity to have an editing conflict which Filemaker will not allow. When a proposed solution presents a requirement that all records (in the process) reevaluate themselves, the possibilty of an editing conflict approaches certainty. On the other hand, if you can demonstrate the truth of what you claimed, I'm ready to learn.
  15. I don't recognize what you mean by LHS or RHS. Is your sort defined by the relationship or by the portal (oops, your using fp5, right).
  • Create New...

Important Information

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