TerenceM

Sounds like you should be using a calculation field in the stock table to check if the item has ever been sold, as opposed to a lookup. Something like: if (IsEmpty(Invoice::InvoiceNumber);"in stock";InvoiceNumber) Note that sorting this field may be screwy because it would need to be a text result. If your InvoiceNumbers aren't all the same number of digits, then you may want to use 0 instead of "in stock" to indicate its state, and not display the 0 value using the number formatting options. Then you can add a criteria to the relationship that checks for the oldest item still in stock, using a global with the value "in stock" to only pull up those stock items that haven't been sold. -Terence

Sounds like you want a relationship from Invoice to Stock based on ProductCode and sorted by date (ascending). Then check to see if the creation date of the current item being invoiced is <= the creation date of the first Stock item via this relationship. You could build this into the validation calculation for the serial number field, although it makes for an unfriendly error message (since the validation can fail for either of two different criteria, and you can only have one message). Also, I don't remember the execution order of validation calculations vs. lookups. You may need to pull up the creation date of the current item being invoiced directly in the validation instead of relying on the separate lookup field because the lookup may not occur until after the validation calculation occurs. Hope this helps. -terence

Make your "Name" field an auto-enter calculation. That way, "Name" will be calculated the first time that data is available for "Namebase", but the auto-enter won't bother recalculating every time the number of related records changes. You can uncheck the "do not replace existing value of field" box if you want "Name" to update if you make changes to "Namebase", but this will lead to the same time of problem you're trying to avoid -- having the unique ID of a record change (albeit only because its Namebase changed, and not because of changes in other records). -Terence

You should have this set up with two tables, Items and MoveDates, so that you can pull up all MoveDates for a given Item with a relationship between the two tables (Items::ItemID = MoveDates::ItemID). Sort the MoveDates side of this relationship by Date (decending), and the first record pulled up will be the most recent MoveDate. You should avoid creating multiple date fields in the Items table (or a repeating field), because there's no easy way to get data out of them. -terence

You need to set up a relationship that pulls out all the plaintiffs or all the defendants on a given lawsuit, and then use Count to see if there is more than one record that matches that relationship. You have a few options for where to do this calculation, but since you

I must be missing something here. If the purpose is to update your lookup when any field value on the layout has changed, can't you achieve the same thing with an auto-enter modification time field? It sounds like the original Case function was designed to update key_company_ID when one of the three fields being audited had changed (and don't bother if any other fields had changed). If you now want to update the lookup used for the audit when any field had changed, the good old "If ( ModTime ; key_company_ID ; key_company_ID)" will serve the same purpose. How you design the audit lookup to cover all fields I don't know -- can you post your example? You need to check off "Preview Reply" in order to attach a file. -Terence

Assuming I understand your question correctly, your key words must be stored in a field, and to input or view the contents of that field it must exist on a layout. Use the File:Define:Database command to look at the fields and figure out the name of the keywords field. Then use View:Layout Mode and place that field on your layout. If it already exists but you cannot enter new data into it, use the Format:Field Behavior command to allow entry into the field while in Browse mode. If I'm off the mark, then please elaborate on your question. -Terence

I think GetField is the function you're looking for, but I suspect it can only return the contents of a single field, so you'll have to use something like: GetField ( LeftWords ( MiddleValues ( FieldNames ( Get ( filename ) ; Get ( layoutname ) ) ; 1 ; 1 ) ; 999 ) and have separate repetitions of this statement for each of the first three fields. You could write a custom function to concatenate the values of every field on the layout, which would probably accomplish what you're going for. But I'm confused by the statement: Case(company_name; full_name ; phone_number ; key_company_ID;key_company_ID) Shouldn't this just return the value of full_name? Do you mean: Case(company_name OR full_name OR phone_number ; key_company_ID;key_company_ID) Is there an advantage of this over using an auto-enter modification time field? It seems like it would accomplish the same thing, although I'm not sure what your purpose is. -Terence

Try searching the forums for 'clairvoyance' or 'type-ahead'. The basic idea is to create a calculation field in your Customer table which has a value list of all versions of the beginning of the customer name. e.g. Bruce Bruc Bru Br B Create a relationship from your entry field on Equipment to this calculated field (I think it needs to be an auto-enter calculation so that it will index properly for the relationship to work). You'll find a lot more about how to work this from searching the forums. -Terence

Perusing the custom functions on Brian Dunning's web site, I found a much simpler way to remove text formatting in FP7. Tim Cimbura posted the following solution: Evaluate ( Quote ( text ) ) Luckily for me, its trivial to change the Custom Function in my databases to use this much simpler calculation! -Terence

For your purpose you probably want to create a Join table that stores all the info for each purchased item, and view that table in a portal on your PO table. The structure would be like this: Table: POs POnumber as number Table: POJoin POnumber as number ArrivalDate ProductID ProductDescription (auto lookup from Catalog) QuantityOrdered Price (auto lookup from Catalog) Table: Catalog ProductID Relationships: POs:POnumber to POJoin:POnumber, set to allow creation of records in POJoin table POJoin:ProductID to Catalog:ProductID Your PO layout should show the current PO, with a portal showing all the related records in the POJoin table. To enter a new item on the PO, simply type in the ProductID on the last line of the portal. This will create a new record in the POJoin table, with the POnumber set to the current PO, and the ProductID as you just entered, and will trigger the auto lookups. This way you can deal with the price changing on future orders without affecting your old POs. -Terence

Thanks Fenton! That's perfect! -Terence

I haven't done much with Applescript, so I'm having a hard time figuring this out. I have a solution that automatically sends e-mails, using the Send Mail script step. It works fine, but different e-mail applications behave differently. Mail and Entourage will both send the e-mails automatically (when "show dialog" is unchecked), but Eudora "queues" them until the user manually tells Eudora to send the Queue (or upon quit, but who quits applications anymore?). For the life of me I can't find a setting anywhere in Eudora to automatically send the queue every 10 minutes, even though I could swear that used to be an option (this is with the latest version). If someone can tell me how to fix this behavior, it would solve my problem. Assuming I'm stuck with Eudora and its Queue, I figured I would use AppleScript to tell Eudora to send the queue after the script runs. But only if Eudora is already running (I don't want to launch Eudora if the user uses a different e-mail client but has Eudora installed for whatever reason). I tried the following: tell application "Finder" if the processes contains "process "Eudora"" then display dialog "Eudora running. Sending queue." tell application "Eudora" connect with sending end tell end if end tell I assume my problem has to do with the processes being returned as a list, and I'm not determing if it contains "process "Eudora"" correctly. What is the correct syntax for this If statement? Thanks in advance. -Terence

I suspect Filemaker uses the default date format specified by the OS. On a Mac, you would change your date format settings in International Preferences, and FP7 should start using dd-mm-yyyy for data entry. I imagine there's a similar setting on Windows XP. Of course, that would change your date format in all other applications, so it depends on what you want. You can also use the old "stacked fields" trick (see attachment), with a text field that you enter date into and a date field superimposed on top of it for viewing. But you'll still have to include data validation to make sure the date is realistic (without the validation, incorrect dates get scaled in interesting ways. e.g., Jan 32nd gets changed to Feb 1, and the 1-15-2004 gets changed to March 1, 2005. Makes sense, but probably not what you want). I thought you might be able to use a Self-Join lookup to enter the formatted date back into the text field, but it didn't work (the lookup pulls the date out in the format specified by the OS, not by the layout). Without this, it becomes tricky to search the date field. So in the end, you're better off sticking with just a text field and doing everything (formatting/validation) yourself, or changing the OS preferences. -Terence dateformat.fp7.zip

You could also leave all the menu options turned on (as opposed to editing or minimum), but change the "User" account privileges to not allow deleting records, or only under certain conditions. I use this to only allow deleting records when certain conditions are met (e.g., an order can be deleted as long as it hasn't been placed yet, after which it is permanent). This can be useful when you don't want to disable many of the other menu commands (such as Find). -Terence

Your right -- I neglected to deal with checking the 2nd or 3rd positions for @ or / characters. I know / will register as a word delimiter, so groups of the format A/A will be caught, but I'm not sure what happens with //A or AA/, or if @ will also serve as a word delimiter (I imagine so, but I have Filemaker parsing a 150 MB file in the background right now so I can't check). I'm still puzzled as to why the nested Case approach isn't substantially hit by all the nested Length(Filter(Upper(Middle statements. That is, when evaluating a string of 40 groups, the Case approach only takes 27% longer than the Middles approach. Each version has to evaluate the following: Case: 40 Middles 40 Uppers (2 characters each) 40 Filters (2 characters each) 41 Lengths 1 PatternCount (160 characters) 1 IsEmpty 44 logicals Middles: 40 Middles 2 Uppers (160 characters each, 1 could be eliminated in the Let statement) 2 Filters (one of 160 characters, the other of 40 characters) 2 Lengths 2 WordCounts (1 could be eliminated in the Let statement) 5 Logicals Evaluating and storing the 40 Middles in the Let statement takes a majority of the execution time (15 seconds). So it may simply be that Logical/Length/Filter/Upper are all much faster than Middle and don't have as big a hit. Also, evaluating 40 Filter/Uppers on 2 characters each may not be much worse than executing a single Filter/Upper on the entire string. I'm also wondering if storing data in the Let statement has a significant performance penalty -- I'll probably play around with these questions more over the weekend. Also, my guess about efficiency of the Middle statement was wrong -- evaluating Middle statements is no more or less efficient if the data exists (e.g. Middle ( groups ; 4 ; 1 ) and Middle ( groups ; 160 ; 1 ) take the same amount of time to execute on an 11 character string). Hmmm. I wonder if we've scared Chalkster off yet -- I imagine this is becoming a bit more than the expected response. -Terence

Note that you can have Entourage (or any client) send the e-mail immediately by unchecking the "show dialog" check box in the Send Mail script step. Otherwise it assumes you want to preview your e-mail before sending it. Also be aware that Entourage and Mail will both send the e-mail immediately when "show dialog" is unchecked, but Eudora will dump it into the Queue which may not be sent until you quit Eudora. If anyone knows an easy fix for this last problem in Eudora, I'd love to hear it. I was thinking of an AppleScript to send Eudora a "Send Queue" command. But I want to keep the client-independent nature of the Send Mail script step, so I guess I would have to check if Eudora is running first, otherwise anyone who happens to have Eudora installed on their computer would mysteriously have Eudora launch even if its not their default client. Any suggestions? -Terence

I've also seen variation in behavior between e-mail clients depending on if I've defined a "To:" address in the e-mail script step. That is, for testing purposes I would leave it blank (and leave "show dialog" checked), expecting a draft e-mail to appear without an address specified, but with some clients you won't get anything at all. -Terence

Yeah, I was deceived at first by the description of TrimAll, which makes it sound likes it primarily useful for dealing with reformatting text around non-roman characters (which isn't high on my list). But its good for much more (in this case, reducing all spaces to single-space, or it can also be used to remove all spaces). The question of speed got me to tinkering and comparing the different approaches. Here are times for 4 different validation-style calculations: "Cases" is Queue's nested case function, taken verbatim "Middles" is my version with 40xMiddles, taken verbatim from above "ModMid" is similar to Middles, but splits the Middles into two calculations of the first 10, and the remaining 30 if needed "Recursive" is using a FP7 recursive function Each calculation was run 1000 times on text fields with different numbers of groups (Len). All the text should generate a "valid" result, requiring the validation to run through the entire sequence. Here are the results: Len Cases Middles ModMid Recursive 40 0:00:33 0:00:26 0:00:27 0:00:30 20 0:00:17 0:00:21 0:00:21 0:00:15 10 0:00:10 0:00:19 0:00:09 0:00:08 5 0:00:07 0:00:17 0:00:08 0:00:05 3 0:00:06 0:00:17 0:00:07 0:00:04 2 0:00:06 0:00:15 0:00:07 0:00:03 1 0:00:04 0:00:16 0:00:07 0:00:03 (all run on a 500 MHz G3 iBook, so all the times are really negligible for the purpose of validating a single field) I was a bit surprised by the results. Running through the 40 nested Case statements takes marginally longer than calculating the 40 Middle statements, and that disadvantage is quickly negated when analyzing fewer words/groups (as Queue predicted). Even the ModMiddle approach, which only calculates 10 Middles for the examples with 10 or fewer words/groups, is still a bit slower than the nested Cases. So I'm thinking there may be a hidden speed hit when trying to evaluate a Middle statement beyond the end of the string, since the "Middles" calculation avoids the length/filter/upper/middle calculation on every word which should have given it an advantage. Note that Queue's formula would also run a bit faster by pre-processing the text with TrimAll, and then just using the nested Case statements to check if each 4th position is a space (combined with a test to make sure the number of spaces = number of words - 1 ). Its also nice to know that a simple recursive custom function outpaces all the other approaches. They really should have included the ability to write custom functions in the standard version of FP7 -- they're useful for more than just developers. -Terence

Oops. You'll also need to add a test to make sure the number of words agrees with the total length, to catch examples like "AAA A A AAA". Add this to the Case statement: WordCount ( groups ) * 4 - 1 <> Length ( groups ) ; 0 ; -Terence

-Queue- definitely deserves his "Carpal Tunnel" rating for that one. Since you're running v7, you could also reformat the field to remove any extra spaces, and then validate to make sure all words have three letters. For reformatting, set the field to have an Auto-Enter Calculation (uncheck "do not replace existing value") with the formula: TrimAll ( groups ; 0 ; 0 ) This will remove all leading and trailing spaces, and reduce all spaces between words to a single space. For the validation, you probably want to check for any invalid characters, and also check that all the words have three letters. So you can use the following: Let ([ Spaces = Middle ( groups ; 4 ; 1 ) & Middle ( groups ; 8 ; 1 ) & Middle ( groups ; 12 ; 1 ) & Middle ( groups ; 16 ; 1 ) & Middle ( groups ; 20 ; 1 ) & Middle ( groups ; 24 ; 1 ) & Middle ( groups ; 28 ; 1 ) & Middle ( groups ; 32 ; 1 ) & Middle ( groups ; 36 ; 1 ) & Middle ( groups ; 40 ; 1 ) & Middle ( groups ; 44 ; 1 ) & Middle ( groups ; 48 ; 1 ) & Middle ( groups ; 52 ; 1 ) & Middle ( groups ; 56 ; 1 ) & Middle ( groups ; 60 ; 1 ) & Middle ( groups ; 64 ; 1 ) & Middle ( groups ; 68 ; 1 ) & Middle ( groups ; 72 ; 1 ) & Middle ( groups ; 76 ; 1 ) & Middle ( groups ; 80 ; 1 ) & Middle ( groups ; 84 ; 1 ) & Middle ( groups ; 88 ; 1 ) & Middle ( groups ; 92 ; 1 ) & Middle ( groups ; 96 ; 1 ) & Middle ( groups ; 100 ; 1 ) & Middle ( groups ; 104 ; 1 ) & Middle ( groups ; 108 ; 1 ) & Middle ( groups ; 112 ; 1 ) & Middle ( groups ; 116 ; 1 ) & Middle ( groups ; 120 ; 1 ) & Middle ( groups ; 124 ; 1 ) & Middle ( groups ; 128 ; 1 ) & Middle ( groups ; 132 ; 1 ) & Middle ( groups ; 136 ; 1 ) & Middle ( groups ; 140 ; 1 ) & Middle ( groups ; 144 ; 1 ) & Middle ( groups ; 148 ; 1 ) & Middle ( groups ; 152 ; 1 ) & Middle ( groups ; 156 ; 1 ) & Middle ( groups ; 160 ; 1 ) ]; Case ( // check for invalid characters Upper ( groups ) <> Filter ( Upper ( groups ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ@/ " ) ; 0 ; // check for invalid word lengths Spaces <> Filter ( Spaces ; " " ) ; 0 ; Length ( RightWords ( groups ; 1 ) ) <> 3 ; 0 ; WordCount ( groups ) > 40 ; 0 ; 1 ) ) This should accomplish the same thing, but execute a bit faster than Queue's approach. Since most groups are 5 or fewer words, you could subdivide the Spaces calculation into the first five, and then the rest if there are more words, but it doesn't seem to take very long to run all 40 Middle statements. It would be more elegant with a recursive custom function, but I doubt it would be any faster. -Terence

In FP6, you can also use Set Field ( TextField , TextField ) to strip out any text formatting and only use the formatting defined in the layout (or Replace Field Contents). That ability is gone in FP7, although I hobbled together a custom function to do the job (see thread on Unformatting Text in the Tips forum). -Terence

Under the Field Format options, you can choose which repetition of a field to display. So if you want one repeating field with 12 repetitions, and each repetition to appear at a different place on the layout, you can create a single field, place it 12 separate times on the layout, and set each one to display a different repetition. -Terence

In this case, I'm in the Inserts table, looking at a portal of GENES. Sorry if I didn't make that clear. Ultimately I want to be able to do this calculation from both sides (in a portal of GENES on an INSERTS layout, and in a portal of INSERTS on a GENES layout). Both calculations have the same problem, since its a many-to-many relationship (one Insert can be near several Genes, and One Gene can have lots of Inserts). For the first version (Inserts table, looking at a portal of GENES): There should be a calculation displayed for each row of the portal, and there is (INSERTS:location - GENES:start). But the calculation will always use the first INSERT that matches the relationship, even if its not the one currently being viewed. I understand why this happens, I'm just hoping there's some clever way to modify the relationship so that its one-to-many based on the record currently being viewed, without having to run a script. Does this make things a little clearer? I have a small test file at work, which I can post tomorrow if it'll help. -Terence

What a useful tidbit to know -- I hate having extra spaces/paragraphs sitting around, and that's the perfect way to get rid of them. Now I'll have to sit down and justify the math to myself. -Terence