steveald

Thanks. I tried searching for it as well as variations on the address. But nothing came up.

Do you have an updated link? This one no longer works.

If it helps at all, my thought process thus far has been... Normally, after setting up the necessary relationships between fields in the main and lookup tables, I would configure a field as a Looked-up value that would pull the appropriate value from the first matching related record. Unfortunately, only 2 of the 4 required data points can have relationships set up between the two tables. I don't know of any way to add conditions to the Looked-up value option. Alternatively, I thought about configuring the field as a calculation centered around the Lookup () function, but I haven't figured out a way to incorporate the 2 non-related values into the calculation either. A Lookup () alone, of course, finds the first matching instance of the 2 related fields - so it never looks past the first 4 records in the lookup table. Adding conditions to the Lookup () function (If (), Case (), etc.) result in nothing being returned. The obvious answer, it would seem, would be to set up fields in the main table for the 2 non-related data points and create relationships between them and their corresponding fields in the lookup table. But, one of them (Code) would need to be any one of 38 different values at the same time and the other (Gen) would need to be either of 2 values at once. I somehow need to be able to look up 76 unique values (38 Code values and 2 Gen values) based on how the 2 related fields are set so that those values can be used in the calculations described above. One of those lookups could be: If Base = "IA" and Enh = "N," then the min age "55" lookup value for Code = "WESC" and Gen = "F" would be 349.2198.

Or, that it is a large and complex solution. With a limit of 256 million total fields per record, don't underestimate the capabilities of FileMaker.

Okay, some more details. The first screenshot is from my database file, showing (among others) the two tables I referred to above - the first being what I referred to as the UI table and the last (CI Rates) being the lookup table. Users only see/have access to layouts from the first table. The second screenshot is a sample of a layout from that lookup table showing all 8 records containing the Code "WESC", as well as the related fields Base and Enh, the non-related field Gen and the 12 lookup fields that will be sent to the users table. The 12 fields represent minimum ages. The way it needs to work is this: Once Base and Enh values are selected by the user, then - for each Code and Gen entry - the database needs to lookup the appropriate value for one of the 12 age fields. For example, if Base is set to "AA" and Enh is set to "Y," then one lookup that needs to be done would be for Code = "WESC" and Gen = "M" and minimum age = 55. The correct value that needs to be returned from the set above in this case is 352.1616. Another lookup would also need to be done for Gen = "F". And each of those would need to be done for the other 11 minimum ages. And each of those would need to be done for the other 37 Code values. So, you are correct, I don't need 912 fields. That would only cover one of the two Gen options. I need 1,824 values returned - if not as fields, then something else that can be input into some fairly complex calculations. The user exerience is this: They make a handful of selections including those for Base and Enh. Lookups are done behind the scenes based on those two selections. The lookups are applied to complex calculations that include values determined by other selections that were made. One result is calculated for each of the 38 Code items and those results are added together. This is done for each of the two Gen entries. The net result is the user sees two rows (one for each Gen) of numbers for the 12 max ages. Hopefully that explains where I am coming from. Again, if I need to look at this from a very different angle, I am open to suggestions.

I am building a database that includes one table for lookup data and another table acting as the user interface, containing all of the calculations and scripts. The lookup table contains 304 records where the primary field can be one of 38 different values and 3 other fields can each be one of 2 different values. In other words, there are 8 records for each of the 38 primary values depending on which option is listed for the other 3 fields. Each record then contains 12 fields of lookup data I need to be able to lookup from the main UI table. Two of the 3 fields in the lookup table are related to fields in the UI table since their values can be changed by the user. I can easily set up lookup fields in the UI table for each of the 12 values. But, since only 2 fields are related, it only pulls data from 4 of the records in the lookup table. I need to be able to automatically perform lookups where the criteria includes one of the 38 primary field values and one of the 2 options on the non-related field. That means 912 lookup fields in the UI table (12 lookups for each of the 38 primary field options and each of the 2 non-related field options). But I don't know how to set up the UI table's lookup field to check each lookup table record for a specific primary field value and non-related field value. I tried an If () calculation, but it only looks at the 2 related fields. It's entirely possible I am not looking at this the right way. So I am open to any suggestions that may require reconfiguring the lookup table or anything else. Please let me know if I did not explain it clearly enough. Perhaps a screenshot or two will be necessary. Thank you in advance for any direction you might be able to provide.

I would like to use the OnLayoutSizeChange script trigger to control how my WebDirect app window appears on various devices. But I can't find any script steps that are compatible with WebDirect that I can use to make this happen. I had hoped it was as simple as something like If Get(Device) = 4 (iPhone) then Set Zoom Level [50%]. Am I limited to having the script trigger go to different layouts created at different sizes? I would rather not have to duplicate all of the details of the app's layouts for multiple device scenarios.

I'm not sure if this is the right forum for asking this question. If someone knows a better place and can do so, please feel free to move it. I have to create many layouts in our main database using formatted static text mixed with merge fields. Attached is an example of what three sections of this text might look like. The dollar amounts are merge fields which display data that is calculated based on selections made elsewhere within each record. I use merge fields to make the surrounding text move based on how many characters are in the merge field data. Normally, I create each layout knowing exactly which sections I want to appear on that page. If there needs to be a handful of combinations of sections, I just create a layout for each one. But I now have a situation where any number of sections (usually between 1 and 8) will need to appear on a page. And there are 32 sections to select from - each with merge field data unique to the record they are found in. Obviously, creating one layout for each possible combination of sections is not realistic. So, I thought it would be nice to be able to create a layout where I could drag each required section to it's own place on the layout. Drag and drop between container fields won't work because we are talking about text with merge fields containing data that needs to change based on selections made elsewhere. And I think moving text between fields could too easily open up the possibility of errors. If it helps, the merge fields (and possibly even the static text) could also be displayed as Edit boxes or something else. Thoughts?

I think I see how that works, comment. But I'm confused by the calculation line: spaces = Char ( 82030820308203082030820308203082030820308203 ) // / 9 x zero-width space

Thanks, webko. I saw where it talked about how to generate a de-incrementing list of numbers. But I need to be able to specify the members of the list as they are often not equally incremented - like in the example comment posted above. Any thoughts on that?

Thanks. I'll see what I can do with that. Unfortunately, some of those lists contain 60 values.

Is there any way to control the order that the members of the List appear in the Pop-up Menu? The reason I ask is - I am using this process frequently and, in some instances now, I need the numbers in the Pop-up Menu to appear in reverse order with the largest number at the top. In the calculation above, it doesn't matter which way I have the numbers entered in v = List - 125 ; 250 ; ... ; 2500 or 2500 ; 1250 ; ... ; 125 or even scrambled. The numbers always appear in the Pop-up Menu in normal numerical order with 125 at the top and 2500 at the bottom. That makes me think there is some function of Pop-up Menus that dictates the order in which a list appears. Does that mean there is a setting somewhere, or I function I can add to the calculation, to override the default order?

Thanks again, comment. Oddly enough, the AppleScript worked on my home computer but not my work computer (both running current versions of everything) with the on idle steps in place. And putting the AppleScript lines into a Perform AppleScript step set to Native AppleScript did nothing on either machine. The minute I took out the on idle steps, it worked everywhere, but I think I lost the process of checking the VPN connection periodically when I did that. I'll try your suggestion on that count. The AppleScript in it's original context was supposed to be saved as a stay-open application, like you said. Any thoughts on how to achieve the same goal on a Windows 8 machine? I've heard I might need to use Send Event or VBScript. Should I post that request elsewhere to get the full attention of the Windows experts here?

XPOST Our company is switching to VPN for remote users to to connect to our FMS-hosted databases. So I want to incorporate connecting to VPN into our already established procedure for accessing those databases via a FileMaker script. Our VPN connection is up and running. And I found an AppleScript that works well making and continually checking the VPN connection. But I don't know enough about AppleScripts and how they apply to FileMaker scripts to make it work. From what I could find on the subject, I need to modify the AppleScript steps to work in FileMaker; such as adding a \ in front of each double-quote. But, apparently that's not all. Here is the raw AppleScript code: on idle tell application "System Events" tell current location of network preferences set myVPN to the service "VPN NAME" if myVPN is not null then if current configuration of myVPN is not connected then connect myVPN end if end if end tell return 60 end tell end idle And here's what I did: I created a new FileMaker script and added one step - Perform AppleScript. In the step options, I selected Native AppleScript and entered the above code. (Using Calculated AppleScript didn't appear to be the right choice for this method.) I saved the script and tried running it, but nothing happens. No errors, nothing. From what I was able to find on the subject, I assumed the problem lies with how the AppleScript text is formatted in the script description. But, nothing I tried worked. Can someone show me what needs to be done with the above AppleScript to make it work in a FileMaker script? One major issue may be the restriction: "Indenting of repeat loops and conditional statements is not supported." This may limit me to stripping the AppleScript down to just the connect VPN portion. But, I really liked what the AppleScript offered in the way of checking regularly to make sure the connection is still active and to re-connect if it isn't. So, if there is another way to do this, I'm all ears. Finally, just in case there is a better method for all of this that I am just not aware of, here is the "big picture": My remote users each have a small database file they open every day to access our FMS Server-hosted databases. The file contains one button which activates a script that opens the required databases. Switching to VPN simply meant setting up the VPN connection on each computer and changing the External Data Sources in the local database. What I hoped to do is to modify the script so that it first makes the VPN connection and then, once that's completed, opens the databases. An alternative process might be to create a separate script to make the VPN connection and that script could be set up as a Script Trigger which activates when FileMaker is first started up or the file is first opened. Just to complicate matters, I also need a similar FileMaker script that will connect to VPN on a Windows 8 machine - which, of course, precludes using AppleScript.

You nailed it! I told you this was an old database. The SameRecord relationship is defined by a Serial Number field containing a value that is unique to each record. The trouble is, that first record and a handful of other records created years ago all have the same value - 0000. I'll have to see if I can override the values in those records with something unique to each one. Thanks again.