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