'makerphile

Apologies, I am with you on the recursive custom functions. I mean if I want to implement the functional equivalent of a for loop, I would need to have some kind of counter that increments each time I call the custom function, some sort of test within the custom function to see if the custom function should be called again based on whether the counter exceeds some value, and some kind of calculation to be carried out each time the function is called. Would the typical filemaker functions in the calculation engine that would be needed inside such a custom function be Let (e.g. to establish the counter and perhaps variables and values to be used in the calculation) and If (for the test)? Any other filemaker functions I should pay attention to, such as Evaluate? Thanks

Thank you very much for the detailed reply, which helped me get off the ground scripting a for-loop-style implementation. I have tried the script approach along the model you laid out, though not yet using global fields. I have used nested loops, the outer one cycling through each square X and the inner one cycling through each square to see whether its contents can reach square X, and if so, I append the contents/piece to the list of pieces in a "reachable_by_pieces" field for square X. The script took a substantial amount of time and I am thinking for-loop-style implementations would run quicker using the while function or recursive functions pre-filemaker-18, for example to make reachable_by_pieces a calculation field. Without the while function, would the main functions for implementing for-loops in calculations be let, evaluate, and getfield? Thanks

Hi, this is not exactly my application but mimics the structure of what I am trying to do. Imagine a chessboard, with each record in a squares table specifying in separate fields the row, column and chess piece (if any) filling it. In each record (for each square) I also want to list all the chess pieces that can reach that square in one move. To do that, for each square X, I want to look at each other square, see what piece is in it if any, and then calculate whether the piece can reach square X, and then enter data in square X's record indicating that that kind of piece can reach square X. That kind of thing would be pretty easy to do in C using a for-loop, but I'm not sure how to approach it in Filemaker. Is it possible to run a script that loops through every square other than X, reads off the piece in that square if any, does a calculation to see whether the piece can reach square X and then makes a change in square X's record indicating that X is reachable by the piece, exiting the loop after every square has been examined? To do the calculation, one would need to extract information about the row and column of each square and what kind of piece is in it. But I don't see how to extract that information. The script functions I see only allow insertion of information into a field not extraction of information from the field. I am not just interested in whether scripts are the best way to perform this job, but also whether scripts can do this kind of thing at all. Of course I am also interested in whether there is a better approach. If scripts are not best for executing c-style for-loops, does one need to do everything within a calculation field? Using recursive functions? If so, I don't get why Filemaker does not make this easier by just having for loops more straight-forwardly available within calculation fields the way they have if functions available. Apologies if this a really basic question and/or if it is in the wrong area within the forum. I use filemaker 8 advanced (no option to select that in my profile). If this kind of thing is much simpler in new versions, please let me know.

I have two sets of data: rankings (Name, Rank) and partner_selections (Name, Name_of_Partner if have one yet). I want for each person to calculate the best-ranked potential partner among people who do not already have partners (i.e. among records in which Name_of_Partner field does not contain a value, sort for the best associated ranking). I can do this by using a calculation field to hold the pairing_status (1 if have partner, 0 if don't), create a global field "Zero_placeholder" that holds the value of 0, and then do a self-join where names are not-equal and Zero_placeholder = pairing_status and sorting for the highest associated ranking. I would be interested in knowing whether there are more elegant ways to accomplish what I want without using a self-join and/or a global field. Would I want to create something like a for-loop that goes through each record, tests to see if the partner field is empty, then alters the value of a "highest_ranked_unpartnered_person" field if appropriate? Thanks P.S. I solved some problems with indexing in my previous attached examples. Zero_placeholder_test_stripped.fp7

The inability to use the self-join with not-= to look up the previous serial number appears to be based on the timing of when the self-join match criterion is evaluated relative to when the serial number field data is committed after creation. Here is an even simpler example database to exhibit the problem. The example database consists of one table and one field: serial_number, which is auto-entered upon creation. A self-join is done where serial_number is not-equal to serial_number from the duplicate table. Upon creation of the first record, as long as the cursor stays in the serial_number field for that record, a portal showing the serial_number field in the duplicate table shows 1, even though the first record is not supposed to satisfy the match criterion for the first record, which is not-equal. But when clicking out of the serial_number field, 1 disappears from the portal. Likewise, when the Nth record is created, which places the cursor in the serial_number field for that record, N shows up among the values in the portal. When clicking out of the serial_number field, N disappears from the values shown in the portal and only values 1 through N-1 remain. Oddly, when less-than is used as the match criterion, it is not necessary to click out of the serial_number field. When record N is created, only values 1 through N-1 show up in the portal. I have attached the puzzling simple example. self_join_test.fp7

I have represented a bug in a simple exercise: a field "previous_serial_number" in a table looks up the auto-entered serial number from the just-previous record in the table. Record 2 should have 1 in the previous_serial_number field, for example. Something is not working: record 2 has 2 (it's own serial number) in the previous_serial_number_field. I'm doing a self-join, with the match field being the serial number and the relationship being not-equal, sorting the matching records on the serial number in descending order. Record 2 is not supposed to be matching with itself, but it is. New records are listing their own serial numbers in the previous_serial_number field. When the relationship is "<" the previous_serial_number field shows the correct values. Is this an issue of using Filemaker Pro Advanced 8 in Windows 10, a bad install, or am I not understanding something basic? I have attached a stripped down example with one table, two fields, and one defined relationship, both where I have used not-equal and used "<". Thanks self_join_only_matching_other_records_in_table_ok.fp7 self_join_only_matching_other_records_in_table_fail.fp7

Sorry it took so long to thank you. That worked.

Suppose I have have two fields: "double" and "half". For a given record, the value of double will be twice that of half (e.g. double = 4, half = 2). Sometimes it will be convenient to enter data for half (e.g. 2), in which case one wants data to be calculated and entered automatically for double (e.g. 4). At other times, entering data into double will be more convenient, in which case one wants half to be calculated automatically. What are some approaches to providing flexibility like this in choosing among fields for data entry?

Thanks for your thoughts. I had been hoping for something like a portal to an itineraries table that would show ALL the acceptable routes between specified termini (e.g. each field representing a node on the route). Your first approach suggests to me a script that systematically works its way through all the possible routes branching out from the origin until limits are met or exceeded and further branching is thus cut off. Doing this kind of thing in C is easy, using "for" loops. Would scripting be the right approach here? I admit I have not done scripting with much ambition. The second suggestion sounds appealing. For other projects I've tried something that I take to be a cascading calculation in a recursive relationship and it didn't work (I got question marks where I was expecting other data). Maybe I don't understand what is a cascading calculation in a recursive relationship or how to set them up properly. What would that involve? Is there a convenient sample database I could look at?

I hope more tractable. This is like an airline flight search, where the number of stops/changes and overall cost and/or time of travel can sharply limit the itineraries considered. It's not yet an optimization problem (though one could sort by overall distance, etc.) and doesn't require traveling a path through all locations. Hope that shrinks the problem enough to make a difference.

What would be a good database design strategy for something like a road trip planner? One starts with data in a LEG OF TRIP table whose fields are: START LOCATION, FINISH LOCATION, DISTANCE BETWEEN START AND FINISH. When a user inputs an overall start location, an overall finish location, a minimum and maximum number of trip legs, and a minimum and maximum distance of travel, the database would display a list of possible acceptable itineraries connecting the overall start and finish locations, perhaps sorted based on some user-specified parameters such as by number of trip legs. Let us suppose that the number of trip legs and combinations thereof are too large to simply make a table of all possible itineraries between all possible combinations of start and finish locations. Thanks for any suggestions, I'm at a loss.

Thanks for the response, Soren, which helps to clarify the motivating issue. I'm guessing Soren's question is why I use a Set Field script step rather than a Lookup to enter data into a field in the portal row, since Soren's examples all involve using Lookups to get portal row field data from previous portal rows. The answer is that in these examples (and in my example back when I tried using only Lookups rather than a script), new populated portal rows don't get created automatically. You have to enter data into at least one field in a portal row and then commit the record to activate the lookups, it seems. The Set Field script step is merely providing this activation. I want automatic creation and population of portal row records so that the unallocated portion of a group payment automatically gets assigned to an individual in a portal row, where that individual is selected based on looked up/calculated data as in Soren's examples.

Hi, I think my script ("test committing script") incorporates your suggestion, but still requires clicking outside the portal rather than tabbing out. Here is the script: If [group payments::Excess Amount > 0] Go to Portal Row [select; Last] Go to Portal Row [select; Next] Set Field [payments for individuals::amount for individual; group payments::Excess Amount] Commit Records/Requests [] End If More details. The parent table is "group payments" and the child table is "payments for individuals." group payments::Excess Amount is a calculation field that incorporates the script trigger, where group payments::Excess Amount = group payment amount - Sum ( payments for individuals::amount for individual ) & If (payment amount - Sum ( payments for individuals::amount for individual ) ≠ 0 ; XMpl_StartScript( Get(FileName) ; "Test Committing Script" ) )

Hi folks, I use an event-based script trigger to populate the next portal row with looked up or calculated data when data for a field on the previous portal row gets edited, provided that one first clicks outside the portal. But clicking outside of the portal to trigger a script isn't much easier than clicking a button to trigger the script. It would be nice to be able to tab out of the edited field (an action that now tabs me into the next portal row, which stays blank) and have the tabbing trigger population of the next portal row. Is this possible?

Sorry for the late thank you, I didn't open your attachment correctly so that I couldn't see what you had done. Now that I've got it working, it looks very nice. Is it possible to get the thing to supply possible values without clicking or tabbing out of the boxes, so that you can see whether continued keystrokes are narrowing entries down to a unique entry? That's the nice aspect of autocomplete of which I was trying to take advantage. Thanks again

I am having trouble using autocomplete within a portal using a value list drawn from a matching field of a different table. Is there a workaround or should I be using a different strategy? I hold classes with variable attendance, charging per class attended. For each class date, I want to enter a list of attendees onto an attendance list. Then I want that attendance list to generate line items for billing purposes, one line item per student per class. Here is the tricky part. I want to enter, on each line of the attendance list, a couple of keystrokes representing initials or parts of first names, which are non-unique but easier to remember than a student id number. I want those few keystrokes to produce a cue (via autocomplete, ideally) as to something that uniquely identifies the student, so that the line items generated will be associated with the right student (presumably through a serialized student id #). For example, I type in "Da" and autocomplete gives me a choice among Danny Daniel Johnson #62 Dan Daniel Smith #123 David David Jones #231 David David Jones #342 DA Devin Andrews #164 (The third and fourth suggestions listed above would be two different students - as indicted by different student id#'s - that coincidentally have the same names.) The autocompletion could theoretically use a value list drawn from the CompoundIdentifier field of a StudentInfo table, which would have records like the following: Nickname/Initials: Danny FirstName: Daniel LastName: Johnson StudentID#: 62 CoumpoundIdentifier: Danny Daniel Johnson 62 The problem is, when I want to enter data into the CompoundIdentifier field of the LineItems table, viewed through a portal in a ClassesTaught table, I cannot autocomplete using a value list from the matching CompoundIdentifier field of the StudentInfo table. In other words, there would be a ClassesTaught table with a Date field, and a LineItems table with a CompoundIdentifier field and a Date field. I create a relationship matching the Date fields from the two tables and allow for creation of records in the LineItems table using that relationship. Then I create a portal in the ClassesTaught table that shows the CompoundIdentifier field from the LineItems table. Now for each new record in ClassesTaught, I enter a date and then start entering names (in the form of compound identifiers) in the portal. When done entering names, the finished new record in ClassesTaught would show my attendance list, and a new LineItems record would have been created for each attendee listed. Within the portal in ClassesTaught, I would think I could autocomplete entries in the CompoundIdentifier field of the LineItems table using a valuelist from the matching CompoundIdentifier field of the StudentInfo table (which could include students who never had classes before). But this autocompletion based on a value list from StudentInfo::CompoundIdentifier can be done when creating new records directly in the LineItems table, but not when creating those records within the portal in ClassesTaught. Is there a trick for getting autocomplete to work here? Is it a problem that got fixed in FMA 8.5 (I use 8.3)? Is there a better way to do quick data entry that is less of a kluge than the strategy of using compound identifiers with autocomplete? (What I really want in LineItems records for the purposes of billing summaries is the StudentID#, not all the information in the CompoundIdentifier, since the Nickname part of the CompoundIdentifier could conceivably change within a billing summary cycle.)