BFrost

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.

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

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.

Here's an easy way (attached) Running_Balance.fp7.zip

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?

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:

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

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?

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.

You've almost answered your own question. Make a new relationship to Parts with "All" as the qualfier.

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?

OH! That LHS and RHS! LOL. Thanks, Lee.

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.

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.

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).

Heather, I'll take a stab at this but I'm not sure I understand the terminology you're using. Are we talking Gregor Mendel stuff here? Colony: the parent File? Breeding: related records to show how the colony got to be the colony, chromosomally speaking? Please, tell me more or send a file by private topic and a clue or two. What version of FileMaker are you using? If the first record has no matches, the summary would show zero ("0") unless zeroes are suppressed in the number format. If there should be a non zero summary ("none" is an answer, after all), there's probably something about the match field definitions, indexing, or even overall data structure that needs attention. Or, did you solve the problem already?

If you want to avoid plug-ins, write a loop script which starts at the first portal record and sets your text collecting field (call it 'Collector' here) to whatever is already in 'Collector' concatenated with ( & ) whatever is in the portal record's text field followed by a "¶" mark; then go to the next portal record and do the same thing, exiting after the last portal record. Make sure 'Collector' is empty before the loop begins. If typography is subsequently involved, at the end of the collection replace double paragraph marks ("¶¶") and double spaces with singles so the typographer doesn't want to hurt you. This can be quite sophisticated with formatting substitutions stacked up, tab and indent styles set in 'Collector', and SGML codes embedded for commercial typography applications. This is particularly useful for maintaining things like college catalog course descriptions, procedural manuals, and the like, which require ongoing revisions but always need to be current. 'Collector' can be global or not depending on the table context.

When in doubt, Cheat! I liked hamelekim's global field method, pretty slick. Here's one that works reasonably well in lesser versions of FileMaker and in FMP8, too, if there aren't too many portal records to deal with. In the table/file with the portal records create a number field (indexible is OK). Create a bunch of sort scripts to satisfy your sorting needs in the same table/file. In the portal to that table/file, specify a sort order where the first priority is the number field and the additional sort priorities should be limited but the second (and additional sort priorities) could be something useful with regard to the data. The number field will do the work, as you probably see already. Create buttons at the portal end which goes to the related portal records, activates one of the predefined sorts in the portal record's table/file, and, as a last script step in the related records table/file, replace the contents of the number field with serial numbers. The 'Freeze Window' step is good way to start. Another method (in FMP 8) is to set up a portal so it's just the way you want it for one of the sorts. Each field in the portal placed to correspond to the sort order for that portal. When your happy, duplicate the portal layout as many times as you require and hide the clones from the layout menu. Change the sort priorities for each clone and reorder the fields in the portals to appropriately reflect the sort priority for each layout clone. This is easy and yields quick results without workarounds. If you've done it right, every element except the reordered fields will be in the same position among the clones. Click a column header label to change clones. A 'Freeze Window, go to layout by number, get(script parameter)' script makes the change look magical and you're only using one relationship to make it work. I recommend reordering fields because we westerners comprehend from left to right, top to bottom. Different sorts facilitate comprehension better when the data sequence is viewed in its intended priority. Caution, the magic part can be distracting. You'll find yourself leaving the mouse in one place in the header row and clicking your little heart out just to watch the data columns appear to dance before your very eyes. Don't say I didn't warn ya.

I'm having a little trouble visualizing the problem but it sounds like the old 'go to field()' script step could be put to use somewhere. In fact it could be the only script step associated with an 'update' button. With no field destination specified, 'go to field()' forces an update in a manner that tells FMP your done with the record but you don't want to go anywhere else, next field or a different record. I sometimes put a clear button over a field (not in the Tab order) where the only way the field can be entered is by clicking the botton activating a script that enters the field. That same script could require a pause with allow user abort set to 'Off' so the only way to continue would be to click 'Continue' in the status area (if it's visible), or to press the 'Enter' or 'Return' key (if the instruction has been made clear), or clicking an 'OK' button that has a 'go to field()' step in it with the button set to 'Resume' script. HTH Are you a fiddler?

You're welcome. I'm glad it's working. How are you setting the globals? If by script, don't forget to have an unspecific "Go To Field()" as the last step. This will force the current record's values to update. If it's still not updating right away, post the behavior and conditions leading up to it.

LaRetta, your right about the presence of a concat field which is prolly a number. I missed that part somehow and focused on the part . And it should only miss on unique combinations of last and 1st names. Good catch, same page.

I'll take a shot at it. Events In the current file, Events, have a script which sets two global text fields in the Hires file to the Event record's date range: Set Field Hires::Global Date From to the values in the Event Record to: DateToText(Event From Date. Set Field Hires::Global Date To to the values in the Event Record to: DateToText(Event To Date. Hires In the Hires file, create an unstored calculate when needed boolean number field to see if either a from date or a to date in the Hire file falls within the range defined by the two global date fields set from the Event File. A non zero result indicate an overlap between the two sets of dates: Date Test = If( (Hire From Date is greater than or equal to Global Date From AND Hire From Date is less than or equal to Global Date To) OR (Hire To Date is less than or equal to Global Date To AND Hire To Date is greater than or equal to Global Date From), 1, 0 ) Stock ID In the Stock ID file create a Status relationship to the Hires file based on Stock ID and Sort the relationship in descending order of the boolean result from the Date Test field above so overlapping date range records for that will be listed first. Create a Container Case calculation field where: 1. a "Reserved" value in the Status field in the Hires file = a system Global amber graphic from a system global container field. 2. a "Hired" value in the Status field in the Hires file = a system Global Red graphic from a system global container field. 3. no match value in the Status field in the Hires file = a system Global green graphic from a system global container field. Got a headache yet? Now all you have to do in the Event file is cue the script which sets the global date. 1. Custom scripted Go To Record Buttons (First, Previous, next, Last) and a custom scripted GoTo Layout button used to set the dates for the current record when the layout or record is selected. 2. Maybe change the Global From and Global To look up or calculate based on a relationship in the Hires file that has a field set to equal a calculate when needed "RecordID" in the Events file (distinct from the indexed "RecordID" in the events file. The Hires globals will point to the matching indexed "RecordID" in the Events file and steal the dates from it. There, I think that will do it. I think I got right.

LaRetta, BeckhamTX hasn't done a concatenation of last and first names yet. He has isolated only the last name and has successfully sorted the names alphabetically by last name. So we already know the field he has is not a number result. You are correct to remind us all to make sure a text result is specified when a text result is wanted. Providing portals from which selections are made is a good solution and may be appropriate for what BeckhamTX wants to do. Otherwise, as you reinforced in your post, duplicate names must append or prehend something that makes each selection possibility a unique possibility. I think using the unique record ID is the easiest, least complicated way to do that, as I posted above. And it could be used in reference list where both data are useful to use. I believe it to be an intelligent way to generate the kind of picklist solution sought in the initial post. Portals can be a better solution if you have the visual real estate to accomodate the additional perceptual information to make selections visually unique. City, State, Phone do not guarantee elimination of duplicates. Phone numbers especially can be an easy ambush for John Smith, Sr; John Smith, Jr; and John Smith, III could all live in the same place with the same phone and not use or provide the distinguishing suffixes. Not only that but 123-555-1234, (123)555 123 1234, (123) 555 123 1234, and 123 555 123 1234 will all dial to the same number but each is a different text string for indexing purposes. Daughters named for their mothers or grandmothers or both are not uncommon and there is no standard way to say Jane Smith, Jr. without getting funny looks. City and State are even worse for similar reasons and adds a significant degree of likelihood that otherwise unrelated people in either or both areas have the same name.

It's not the calculation field that is the problem. It sounds like you may have hit on the explanation with 'duplicate last names'. If you have the first names avalailable, how about making a calculation field which results in a directory style "Last Name, First Name, Middle Name" and use that field as the second field. That's the way I've been doing it for years and I seem to get 'em all. Although if two people had exactly the same name, you'd only see one picklist entry between them, too. To eliminate that possibility, append the record ID to the directory style field you created but in a 2nd new calculation field. (Having a directory style listing is useful all by itself and having a number tagged on the end would make it look dumb.) Since the Record Id is (presumably) unique, you can select 'display only the second field' in the pick list. I just tried what you described on a database with about 15000 names. There were about 15 last name 'Adams' records but only one showed up to pick from when sorting by the 2nd field (last name). So you did find your own answer. The indexing function only shows one of everything no matter how many of some may have duplicates. Therefore, fields used must each be unique to assure full representation in selection. Is that a PIA or what?

A State designation can narrow it down for you. The first 3 digits of an area code can narrow it down within a state. The last two digits (of a 5-digit Zip) even further reduction. Find a Post Office Wickipedia has a Explanation of ZIP Codes.