Jump to content

Fenton

Members
  • Content Count

    4,998
  • Joined

  • Last visited

  • Days Won

    6

Fenton last won the day on May 28 2017

Fenton had the most liked content!

Community Reputation

15 Good

About Fenton

  • Rank
    member
  • Birthday 08/06/1950

Profile Information

  • Gender
    Not Telling

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. Fenton

    Relationships via Calculation match field 3 tables

    There is a difference between a field that is "unstored" (and cannot be indexed) and one that can be. A field which cannot be indexed (FileMaker will tell you so), can be used in a Relationship, but only as the "from" side; it cannot be on the "to" side of a relationship. Any field (including calculations) which can be indexed can be on either side.
  2. Comment's calcs were very near to success. The first one uses a special property of the Substitute () function, which is that it is "case sensitive"; which you really need. Hopefully people seldom used "HS", etc., for other things. If so, then you'd need to do more, to get the "other" out. His calc would change "HS" to "High School", which is usable text. The PatternCount allows you to see whether you got a match, which it can if you ran the above to get usable text. Case () can return only the value(s) you want. The List () function returns each of the above, and does not return duplicate values (at least it seems not to). Let ( key3_fix = Substitute ( KEY3 ; [ "HS" ; "High School" ] ; [ "MS" ; "Middle School" ] ; [ "ES" ; "Elementary School" ] ); List ( Case ( PatternCount ( KEY3_fix ; "high school" ); "High School" ); Case ( PatternCount ( KEY3_fix ; "Middle School" ); "Middle School"); Case ( PatternCount ( KEY3_fix ; "Elementary School" ); "Elementary School" ) ) ) Returns (if present): High School Middle School Elementary School ( It would return that above is they'd already typed one in correctly, i.e., "High School".) P.S. Notice that I made "high school" lower case; so you can see that PatternCount is not case-sensitive (as the above works). [ It seems to work. I cannot be sure. For one thing, I cannot be sure about many things.]
  3. Fenton

    Table occurrence naming

    Much of this sounds like it could use the David Graham separation method. Which is, as you say, a central table for the fields they share, and other tables for the ones they do not share. The main thing he added was a method to have the central table automatically create (via relationship, not script) a new record, with the critical ID, and also set that ID into the "child" table, upon record creation in the child table's layout. I posted a file with an example somewhere here (long ago) http://fmforums.com/forum/topic/65163-report-using-a-partial-join/#entry308948
  4. Fenton

    Keep users in found set?

    There are different possibilities of saving found sets, I suppose. But I have one simple one that I've used often. It is basically for saving the found set before trying some Find(s), which may or may not work well; so you can quickly get back to this saved one. It is fairly easy to build, fast to run, and fairly easy to use; basically 2 buttons, a nice ( < ) and ( > ) {but different from buttons going to next record, etc., though these two are mostly for List view layouts}, with a Tooltip saying what they are. Someone else, long ago, discovered that FileMaker can quickly move your Found Set to another instance of a table, which is NOT connected to the current one; in other words, another occurrence of the same table on the Relationship Graph, but not connected. If you use Go To Related Record, started with the current table occurrence's layout, and going TO an unconnected table occurrence's layout, FileMaker will (almost instantly) move the current found set to become the found set of the other layout. It will be "saved" (for that session anyway), as no own ever goes there otherwise. These is only one "saved found set" however; though you can create another for a 2nd (table occurrence, scripts) or more, if you need to (like for another special script). In the scripts below I'll call the first table "Table" and the other "Table FS" (short for Found Set). Save Found Set (FS) script: Freeze Window If [ Get ( WindowMode ) = 0 ] Commit Records/Requests [ No dialog ] # If [ Get ( FoundCount ) > 0 ] Go to Related Record [ From table: “Table”; Using layout: “Table FS” (Candidate FS) ] [ Show only related records ] Go to Layout [ original layout ] End If Else Beep // so they know nothing happened, if they're in Find mode End If Restore Found Set (FS) script: Freeze Window If [ Get ( WindowMode ) = 0 ] Commit Records/Requests [ No dialog ] # Set Variable [ $layout_name; Value:Get ( LayoutName ) ] Go to Layout [ “Table FS” (Table FS) ] # If [ Get ( FoundCount ) < 1 // cannot use GTRR; it would not even go back to the layout ] Go to Layout [ original layout ] Exit Script [ ] End If # If [ IsEmpty ( Get ( ScriptParameter )) ] Go to Related Record [ From table: “Table FS”; Using layout: $layout_name ] [ Show only related records ] # # Or, below have a ScriptParameter; called by other scripts; need to return to other than original layout (rare) Else If [ Get ( ScriptParameter ) = "List" ] Go to Related Record [ From table: “Table FS”; Using layout: “List” (Table) ] [ Show only related records ] End If Else Beep End If Notice that the "restore" is a little more complex than the "save", mostly because it has to know what layout to return to. The basic is to put the current layout into a Variable first, and go back to that. However, if you call this script from other scripts, and you want it to return to some other specified layout, you could add script steps to do instead (though I guess you could be smart, and send/get the ScriptParameter; but this is an old script)
  5. Fenton

    Folders in folders

    Thanks comment (Newbies will also, if he returns :-). That was what I was "almost thinking of, but couldn't remember all the details"; the "every folder of" was the piece I was missing. My version was a good test for an exact folder, in a known place, but did not handle "any level of nesting." He had hard coded the folder it was in, as a fixed folder on the Desktop,* so other folders should not happen. *However, the hard coded Desktop is only going to work for one person. If you logs in a again, I can give him some tricks on using a dynamic user name; also the "Macintosh_HD" vs. "Macintosh HD" (or whatever it is). That can be done in either FileMaker itself (and sent to the AppleScript), or in the AppleScript itself (likely cleaner, unless you want to use it on other things).
  6. Fenton

    Folders in folders

    I'd hate to leave this empty, so I'll give it a chance. I had some trouble with a few things. The first being that I had neither a FileMaker file nor the folders to work with. But the folders I could create. Then a few things in the AppleScript code. One is the "Users:Users:" in one, which just seemed too odd, so I made it just "Users:"; you can do whatever is correct for you. The one thing which I could not get to work was the: if the name of TargetFolder is in SourceFolder then It never said yes, and I didn't know what to change to make it happen (anyone?). So I changed to something else, using a name of what the folder would be, and just a test for it as an alias; which will return an error if it isn't there. I also moved some stuff around a little. I don't like to put a FileMaker routine inside a Finder area. Because it is better to keep FileMaker free if possible; so that you do not have to say "which" FileMaker you're talking about, so it will work for either "FileMaker Pro" or "FileMaker Pro Advanced". You do that by not "telling" FileMaker. It knows who it is; it is the app running the AppleScript, via a script step. You only need to tell FileMaker if you've got it stuck inside some other apps area. I also don't really understand the whole idea here, as it seemed like there were 3 folders involved: one which they chose, one which is on the Desktop, and one in the Shared folder (which ended up with the chosen folder). It is the one on the Desktop which seemed odd; it seems like of like a "value list" kind of thing :-? Anyway, this is my version; hopefully it does something useful. The commented out FileMaker lines will work in FileMaker, they must be made active to work on just AppleScript; this is a common method, so you can run in FileMaker or run in AppleScript (for testing): --tell application "FileMaker Pro Advanced" set fName to cell "tbl_Doc::UserFolderMN_DocuSet" of current record --end tell property ChooseFolder : "Choose Document Folder" property an_error : false try choose folder with prompt ChooseFolder set TargetFolder to the result as alias set SourceFolder to ("Macintosh_HD:Users:JohnG:Desktop:Upload:") on error -- they might have hit cancel return end try tell application "Finder" set folder_test to SourceFolder & (name of TargetFolder) try get folder_test as alias move folder (TargetFolder) to folder (alias "Macintosh_HD:Users:Shared:ModernExpertFolder:Document_folders:") set TargetFile to last file of TargetFolder as alias set the name of folder (TargetFolder) to (fName) as text set NewBocFolderPath to "Macintosh HD" & (POSIX path of (TargetFolder)) on error set an_error to true end try end tell --tell application "FileMaker Pro Advanced" activate if an_error is false then set cell "tbl_Doc::NewDocFolderPath" of current record to NewBocFolderPath set cell "tbl_Doc::DocPicture" of current record to TargetFile save else display dialog "Invalid Folder." buttons {"OK"} default button 1 end if --end tell
  7. I'm just using Control-Up Arrow and Down Arrow to move between records.
  8. A layout has access to all the records of its table (base table). You can use a Find or Relationship (script) so that you only have a found set of particular records. A portal on the other hand shows only records that are related to its Relationship (or those filtered). So, basically, they are not the same kind of thing. The method you used does not stop the other records of that table from being seen, just by the Relationship you've said the layout has. The relationship could however be added to the relationship if you added a portal to that layout. Which isn't enough to do what you want really though. So, to do what you want, you'd want to either view from "higher ground" (brian's method). Or by using a Find or Relationship script to filter down, when you went there. Basically, I've seldom every used anything except the basic table for a layout, unless it was a special layout for a separate relationship "group" on the Relationship Graph, or a special action (like saving a found set). Others may know more about this. And likely could say it better. I have trouble with words in general.
  9. Fenton

    Help Removing Duplicates

    I would be most suspicious of the "CountDuplicates.c" field. The ".c" is likely his way of saying that it is a calculation type field. I imagine it is testing for a few things. The result of matches to this calculation may be only a 1, or it may be a count (1, 2, etc.). Since there are multiple requests, the success of ANY of them will find a record. Two of the requests are just looking for "empty" in a field. The last one is looking for the word "vae" in the same field as the request above it. One thing to look it is the Import Records step itself, the "Specify import order," (button at the lower right). If any of the (many) fields are not lined up together correctly, then one (or more) of fields may be getting the wrong data imported. Look at the 3 fields which are used by those requests (glance at the others if one of these is incorrect). Be careful however, as messing up an Import would bust it. Another (safer) way to look at what's wrong would be to look at those 3 fields (only 3) data on a layout. One (or more) of those fields used by the requests likely does not have the correct data in them (of the Import with duplicates). It should be visible. However, if it's the "CountDuplicates.c" one, >0, you'll still have the problem of what the heck the calculation is looking at and/or doing.
  10. The Halt Script (script step), can run via a button on the layout (or be a visible script). It stops all scripts. (Which is why you may not want to use it real often in other scripts, as it will stop all that are running.)
  11. Some one may upload a file which has this, done via calculations and relationship, but I'll just give you some tips (and I don't have such a file, that I can easily find :-). Yes, it can be done via the above. There are a few rules via such. Some of these you likely know, as your skill level is expert; but I'll write some anyway. A. Calculation fields can have a result which can be "Indexed," or else is "Unstored." One which can be "Indexed" can be set as either. But if it cannot be "Indexed" it will be "Unstored." The "left" side (from) of a relationship can use fields which are either. However the "right" side (to, targeted), must use only Indexed fields. B. Anything which calculations uses such things as "Get (CurrentDate)" must be Unstored, as "current" requires that. So it can only be used on the "left" side of a relationship. Example for field "_cFinancialYear_curr", result number (beginning on September): Case ( Month ( Get ( CurrentDate )) > 8; Year ( Get ( CurrentDate )) + 1; Year ( Get ( CurrentDate )) ) For an "Indexed" field, in the table where the data (change the "date" field to one you're using) is, "_cFinancialYear", result number: Case ( Month ( DatePlaced ) > 8; Year ( DatePlaced ) + 1; Year ( DatePlaced ) ) Quarter: _cFinancialYear_Q_current Case ( Month ( Get ( CurrentDate ) ) > 8; Year ( Get ( CurrentDate ) ) + 1; Year ( Get ( CurrentDate ) ) ) & Case ( Get (CurrentDate) ≥ Date ( 9 ; 1 ; Year (Get (CurrentDate)) ) and Get (CurrentDate) < Date ( 12; 1 ; Year (Get (CurrentDate)) ); 1; Get (CurrentDate) ≥ Date ( 12 ; 1 ; Year (Get (CurrentDate)) ) or Get (CurrentDate) < Date ( 3; 1 ; Year (Get (CurrentDate)) ); 2; Get (CurrentDate) ≥ Date ( 3 ; 1 ; Year (Get (CurrentDate)) ) and Get (CurrentDate) < Date ( 6; 1 ; Year (Get (CurrentDate)) ); 3; Get (CurrentDate) ≥ Date ( 6 ; 1 ; Year (Get (CurrentDate)) ) and Get (CurrentDate) < Date ( 9; 1 ; Year (Get (CurrentDate)) ); 4 ) I did the Quarter calculation field, but using the above for Year, and adding a field for Quarter, which is: Field: _cQuarter Case ( DatePlaced ≥ Date ( 9 ; 1 ; Year (DatePlaced) ) and DatePlaced < Date ( 12; 1 ; Year (DatePlaced) ); 1; DatePlaced ≥ Date ( 12 ; 1 ; Year (DatePlaced) ) or DatePlaced < Date ( 3; 1 ; Year (DatePlaced) ); 2; DatePlaced ≥ Date ( 3 ; 1 ; Year (DatePlaced) ) and DatePlaced < Date ( 6; 1 ; Year (DatePlaced) ); 3; DatePlaced ≥ Date ( 6 ; 1 ; Year (DatePlaced) ) and DatePlaced < Date ( 9; 1 ; Year (DatePlaced) ); 4 ) Now, _cFinancialYear_Q is just: _cFinancialYear & _cQuarter C. Another trick which I use many times which need to see whether something is "true," about some calculation is that I can a "flag" result (also called "Boolean"); the result being 1 if so (nothing otherwise). Example field, "_cOpen_flag"; Case ( IsEmpty (OrderStatus, 1) Then, in many tables, I have a calculation field, "_c1" whose calculation is: 1 That is, there is a 1 in every record. It can be "Indexed" The "_c1" field can be used in the right side of any relationship which wants to target a "flag" field on the right; in this case "_cOpen_flag" D. I think you would have to do the different "date" (actually numbers) to view as different relationships. I cannot see how to do a change to see in the same portal. I would think different relationships, different portals, viewed within different Tabs. It would work because a "financial year" and "financial quarter" and "month" do not really argue with each other. You could flip between them. E. Product::Category="my category" (whatever) This would work in a relationship, because it is targeting a text field. One the left side of the relationship could be a global field (drop-down list, or whatever). F. Because the "right" side of the relationship are all Unstored fields, not attached to any Indexed field one a calculation, the "right" side could be from any table/layout you want it from. P.S. I am a little tired, I will send this as is :-|
  12. Fenton

    Value List

    Another option would be to "clean" your field's data. Similar to bcooney's Delete the unwanted data, but by learning how to use the "Replace Field Contents" script step (near the bottom of the "Records" menu). However, this is one of the more dangerous commands, unless you play by the rules. Which are: A. Save copy of (if database is open) or duplicate your database file (if not open). 1. This is so you will be able to go back, if you make a mistake. 2. Do this after each time you run the command (unless you never make mistakes). 3. An alternative (which is how I do it), is to create a duplicate of the field, with the existing data, and work on it instead. Then, when you've got it all fixed, you can do one last Replace Field Contents, and move all that "fixed" data to the existing field. a. Only the changed ones would need fixing, so do that above with only those records found. 4. I'd still do the backup fairly often; just to stop from having to start over. 5. This can be done even if running a Server database, since: a. You're mostly using a new field b. You can Export just that field, ready to Import it back, if mistake happens. B. Do a Find which finds all the records with that Field, which you want to change to ONE of the "new" values you want. 1. Change the field in the 1st record to the new value 2. Which the curser still in the field, "Replace Field Contents" (it will show the above value by default). 3. Alternatively, add the new value into a "Replace by calculation with result:" a. But I usually do #1 above 4. Repeat operation for all that need changing (or change manually, if only a very few). C. You can see why bcooney's method of a new table can be such a good idea. 1. Sometimes that table can be used for a few fields, if they have quite a bit in common (like one is related to the other). a. With a Value List for each field 2. However, how do you want to use this field? a. If extensively, complexly, then leaving funky data in the field will make it not as useful as cleaning the field's data in all records D. Do all of the above 1. That solves all existing data 2. Allow ONLY data from your new table to be used in the field. a. A simple option in the Field, Validation C. Know for the future that poor data is often way more time and trouble to fix than it is to take the time to set it up to be only good data.
  13. Fenton

    My Recipe database needs help...

    Well, good recipes (especially "tastes good") for healthy-gluten are likely needed. I am living with my daughter, who's skin is allergic to regular bread, etc., and her two boys, the youngest one has some problems also. She has some trouble with restaurants. But we are in Santa Cruz, which, for its size, has more "healthy" food than anywhere I've seen (and I've seen down to San Diego).
  14. Fenton

    My Recipe database needs help...

    If a Chef has multiple Recipes, but a Recipe has only 1 Chef (ever), then (I don't think) you need a ChefRecipe "join" table. You can just set the Chef ID (from the Chef table) into the Recipe that is his. You would see all his in a portal on a Chef form view, with the relationship on Chef ID to Recipes. Yes, you'd have quite a few portals on that layout, if you'd want to see everything about a Chef from there. The method eos used, of setting global ID fields would be how you'd show the details, starting from left to right. You'd likely want to clear those to the far right after a change to the left (or else they'd show irrelevant data). This may should complex, but really, they are much the same, so if you can do one, you can do others. The "clear the globals to the far right is also not that hard, globals can be cleared more easily (and safer) than regular fields.
×

Important Information

By using this site, you agree to our Terms of Use.