Having trouble with constrain find in script...

[JSRunnels]

Please bear with me. I have only had filemaker for 9 days and have no prior database/programming experience.

I am creating a database for my synagogue to use to deal with member and non-member data. There are two major tables - families and individuals. The individuals are related to each family by Iid and Fid. However, the data in the families table includes members, non members, prospective members, previous members and businesses (basically everything with an address.) That field is designated "Type of Entry"

I have two layouts for each major designation/Entry Type (members, non member -- this includes nonmembers, prospective members and previous members-- and businesses). One layout is a form for all the (pertinent) data for each record and one layout that is a list of all those records. The database is set up so that if you go to a particular layout (via buttons and scripts), you only see the records that pertain to that layout. With me so far?

So it seems to be working well except for Find, which, of course, searches the entire table. I am trying to write a script that will perform the find and then constrain the results to those which belong with the the particular layout from which the find originated.

I have a Find script that performs one of the following functions:

1) no find criteria entered - goes back to list/record - this works

2) no matching criteria - goes back to original list/record - this works if there are NO matching records in the table. If there are any (regardless of Type of Entry), one of the below happens.

3) one matching criteria - go to record form - this works if there is exactly one matching records in the table, but does not care if that record is of the right Entry Type.

4) multipe matching criteria - go to list form - this works but shows all records regardless of Entry Type.

I have tried putting the Contrain Find command everywhere in the script with out success. Please help. This is just about the last thing I have to fix before the database is ready (I think) for prime time.

I have attached a clone of the database if that helps. The scripts in question all start with find. I figure if I can get it to work for the nonmembers layouts and can modify it to work in the others as well...

P.S. Any other constructive criticism would be much appreicated...

SynagogueDatabaseClone.fp7.zip

[Fenton]

You need to tell us the master password or we can't even look.

[JSRunnels]

I left it completely wide open (full access) in Guest mode... Will that not work?

[Søren Dyhr]

not care if that record is of the right Entry Type.

Don't you miss yet another Get(LastError) if the Constrain doesn't come up with any match in the part where more records is found ...you need to remember that continue is the button pressed when errorcapture is on.

--sd

[Fenton]

Oh yeah, Guest Account. I'm not too sharp this morning.

If you find 1 record, but it's not the right type, there is no Constrain Find on that. If you put one then you end up with 0 records.

I'd probably just use Set Field [ Type, Current ] in the 1st Find request, and Perform Find [ Restore, Current ] instead of Constrain in the others.

By adding that criteria to the first step, you will be able to do a successful Find in one step instead of 2; finding your records the first time around. It's also easier. I only use Constrain when I have to, and I don't think you do in this situation. I guess I'm kind of "old school."

[JSRunnels]

I am not familiar with the Set Field command, so I am not exactly sure what that does. I have to go in just a minute, so I can't work on it right now.

One question that comes immediately to mind is: How will that work with the non-member layouts, which include records with "Type of Entry" field marked Non member, Prospective Member or Previous Member...? Can I "Set Field" to include all of these designations?

As for missing the Get(Last Error), I thought that was taken care of with the If [FoundCount = 0], which happens after the constrain find does...

Thanks with your Christmas Day help with this. It is driving me nuts.

[Fenton]

Set Field is "like' typing the entry yourself. So you go into Find mode (which you script ensures). The you set the Type field to "current", then Perform Find[]. That is the simplest way to always include "current" in the Find request.

But the broken part right now is that you have no Constrain if the Found Count is 1. But that 1 could be from the wrong group. So you need Constrain; or just check the Type field with an If. But as S

[Søren Dyhr]

I only use Constrain when I have to, and I don't think you do in this situation. I guess I'm kind of "old school."

An area where this altmodish'ness - have lost some ground is that constrain is perfect to follow a GTRR(SO) ...I'm wondering if there might be some idea here - in following this via parameter transfere over a subscript ...of if it just gets too bysantine???

--sd

[JSRunnels]

It's working! Thank you both for your help. I did need another GetLastError. I was using GetFoundCount instead. That didn't work. It works perfectly now (I think--I haven't tested it completely.)

So... Any other comments on the DB itself? Like I said I am new at this (11 days now) and would greatly appreciate any comments as to glaring errors, counterintutive interface issues, etc.

Happy Holidays!

[RalphL]

I didn't get envolved with the find part but I will offer some comments on the design.

Phone Number: Why 3 and what is the purpose of Phone type? I would suggest the the home phone number be the only phone number in this table. Work phone numbers should be in the individual table. Cell phone number should also be in the individual table. You have it this way now.

I was surprised to see doctor and dentist data in the family table. Again I think these should be in the individual table. Are familes assigned to different Rabbi?

Anniversary, I assume wedding, applies only to husband & wife. I would use wedding date and calculate number of years. Again I think these should be in the individual table.

Committees should be a separte table, use a portal. You might want to consider a many to many relationship between Committee and Individual.

You don't record gender of the person. Nor do you record the roll in the family, i.e., Father, Mother, Child, etc. You have only child and adult, are there other steps between these 2?

I do not know what Yharzeit is. Why does it need a join table? It looks like most of the fields in the join table are redunant. Do you really need all those auto-calculations?

I hope you find these comments helpful.

[JSRunnels]

Thank you for all your great comments.

Phone 3 and Phone Type is a remenant of the original data file (imported from Access via Excel tables). I hope to do away with them soon. I am using phone 2 for business numbers.

The Dr. data is only in the Family table for the purpose of addressing formal letters/envelopes(Dr., Dr. & Mrs., Drs., etc.) The actual profession data is on the individual tables.

Anniversary is in the family layout because I need to collate that data for letters/bulletin announcements and only want to have it appear once (One anniversary per family...) If someone is not married then the field is just blank...

I thought about doing a join table for the committees, but felt overwhelmed. I wanted it to be easily searchable and easy to make changes. The join table really intimidates me. When I was creating it for the Yahrzeits, I thought I was going to go insane...

I don't record the gender or family relationships because I just couldn't think of any function that would depend on that designation. I may regret this down the line...

Yahrzeit is the Jewish observance of the anniversary of the day of death of a loved one. It is very important. We print them in the bulletin as well as sending out cards to family members to remind them of the date. I used the join table because one Yahrzeit may go to many different families, both in and out of the congregation) and one family may have many different Yahrzeits. I thought that was probably the only way to do it...

As for the auto-calculations, I am still not quite sure when I shoud use a text/number field with an auto-calculation vs. using a calculation field. I started off not realizing that I could designate the field type as a calculation field and hesitate to go back and change them for fear of "breaking" something that seems to be working... I am still not sure when I should be using which.

Thank you so much for taking the time to really look at the database and make all your comments. I can't tell you how much I appreciate it. I know all you professionals out there must (occasionally) get a laugh out of what us beginners struggle with as well as what we are so proud of...

I have made several modifications/(?)improvements since I posted my original clone. Is there somewhere else on this site that I might post it for comments...

Sincerely,

Jessica

[JSRunnels]

I forgot to say I took all the fields out of the join table except YiD and FiD. I realized that I don't need them there at all. For the layout, I just have a field that shows the data from the related table.

One of the main reasons that I didn't add a join table (many to many) for the committees is that I could not for the life of me figure out how to make changes to a committee list (from the committee entry, without creating a completely new entry in the individual list without using the join table to do it.

In other words, if I want to change out all the members of I have to go to the join table to do it one record at a time. (That is what I did for the Join Yahrzeits.) The problem is that that join list seems a bit counter intuitive to me and I am not (ultimately) going to be the one to make the changes. I was afraid that someone else would not understand the relationship between the three tables and the need to go to the join table to make changes to the relationships between the committees and individuals.

Ideally, you would be able to go to the committee list and swap out/ add members from the list of available individuals. Of course, there is probably an easy way to do this. (Schools must do it all the time.) But I just couldn't figure it out.

Thanks again for all the help/comments.

Jessica

[Fenton]

I would agree with Ralph. What he meant about the doctor and dentist was that they should be another table, with their IDs entered into the Family table. So you're not repeating data about 1 doctor who 20 families use. What if he moves or changes his phone number? It would be best to only be in one place.

You would need a mechanism for choosing the doctor, etc.. I've got a couple example files in the Sample files section on a way to pop up a portal for choosing. Or just use a drop-down value with the ID and the name. How many could there be? not many.

For the anniversery you could have a separate ID for "spouse," with the assumption that a person has only 1 (at a time). So a husband/wife would have the SpouseID of the other person. A calculation field of both IDs would be able to access both, so that the Wedding Date would only have to be entered once. I've done an example of this; on the Ind. Data - Adult layout. It's a good example of using IDs in self-relationships; one simple, one "tricky" -)

Also, back to the original question; your Find Family Copy script was very close to what the Find script would look like with a Set Field step in the Find. It doesn't need a Constrain.

[P.S. The Wedding example assumes that the Family could be an extended familty, with possibly more than one wedded couple.]

SynagogueDB2.zip

[JSRunnels]

There is no relationship between doctors and families. Its just that some of the members are doctors/dentists/PhD's. That identifier is just there for addressing labels/envelopes, etc. Likewise, we only have one Rabbi in our congregation. I have a field for profession in the individual data view, really for data collection purposes only.

For the wedding date - you don't have to enter the wedding date twice, but don't you then have to enter the spouse id twice?

I got the Find Scripts working. (Thanks to your help.) I think the Set Field would work better than what I have except for the Find script for the nonmembers which needs to return records with any of the following designations:Non Member, Prospective Member and Previous Member. So I did it the cludgy way for all of them...

I am attaching a new clone of the database, if you want to see my improvements. (As if you have nothing better to do than help a complete stranger.. )

I am particularly proud of what happens if you try to delete a child entry. (This uses Applescript.)

I would love to see a sample database that has a child/class, individual/committee many to many relationship in it, if you know where there is one I might be able to look at...

Thanks again. and again.

Clone.fp7.zip

[Fenton]

Yes, you have to enter the spouse in each, as they're different. I suppose it could be scripted; do one, then hit a button to go do the other and come back; since it's obvious that's what needs to be done. Or use a join table, where choosing the ID of the spouse would auto-enter your own ID. But then the wedding date is in another table.

The Finds look good. I may borrow that technique. It does perform 2 Finds, but I imagine a Find immediately followed by a Constrain is pretty fast. As you say, it's less messy that entering lots of Set Fields (which would work BTW, by using New Request for each of those "or" non-members; or just use "current member" Omit. There's more that one way to do it.

[Deleted flawed attempt to create and delete records across a join table.]

[JSRunnels]

Is there an emoticon for blushing? Seriously, thank you for the compliment on the find script. (I was pretty proud of it...).

I think I will stick with Wedding Day/Anniversary in the family table. It is doing everything I need it to at this point.

The improvements to the Join table are much appreicated. I don't fully understand them yet. I haven't used global fields or containers at all so far; I am not sure what the _g and _cg prefix is for. I have no idea how to get them from your DB into mine and I am scared that I will actually delete data from one or the other of the family/yahrzeit tables. So lots of questions, but I will fool around with it until I understand it better.

The important thing is that when I delete a family (or a yahrzeit), I don't want it to delete the entry in the other table, just the entry in the join file... You might actually delete a yarhzeit (if the family moves away,) but often you keep it as some other family is observing it. They should be separate functions. I am assuming that is how it is set up, but it was NOT working that way when I set it up (12 days ago). But then again, I really have no idea what I am doing!

I had accepted the fact that I was going to have to have the users understand the join file to some degree and had made a little pop-up help file to try to explain it and guide them through. This looks so much more sophisticated from a programming standpoint and much easier to use/understand from a user perspective.

When I finally present this to the powers that be, am I going to the the cat's pajamas or what?

Jessica

[Søren Dyhr]

By and large does all the "Finds" do the same thing, which calls out for modulization - to optain a situation where a debugging of the script only should be done in one single place ...your find scripts only differs from each other in 5-6 lines towards the end of the script. This becomes very easy to spot when rewriting you script like this:

Set Error Capture [ On ] 

If [ Get(WindowMode)=0 ] 

        #We're in Browse Mode: Enter Find Mode 

        Enter Find Mode [  ] [ Pause ] 

Else #We're already in Find Mode. Let the Find button function the same as the Continue button. End If Perform Find [  ] 

Loop 

If [ Get(LastError)=400 ] 

       Show Custom Dialog [ Title: "Message"; Message: "No find criteria were entered."; Buttons: "OK" ]

       Exit Loop If [ 1 ] 

End If 

Constrain Found Set [ Specified Find Requests: Find Records; Criteria: Families::Type of Entry: "Current Member" ] [ Restore ] 

If [ Get(LastError)=401 ] 

        Show Custom Dialog [ Title: "Message"; Message: "No records match this request."; Buttons: "OK" ]

        Exit Loop If [ 1 ] 

End If 

If [ Get(FoundCount) <> 1 ] 

        Sort Records [ Specified Sort Order: Families::Family Name; ascending ] [ Restore; No dialog ] 

        Go to Record/Request/Page [ First ] 

End If 

Go to Layout [ "Family Data" (Families) ] 

Exit Script 

End Loop 

Show All Records 

Perform Find [ Specified Find Requests: Find Records; Criteria: Families::Type of Entry: "Current Member" ] [ Restore ] 

Sort Records [ Specified Sort Order: Families::Family Name; ascending ] [ Restore; No dialog ] 

Go to Record/Request/Page [ First ] 

Go to Layout [ original layout ] 

It is so that the point where the scripts differs is when Get(FoundCount) comes, which means that everything comming earlier is common ...and then the last 5 lines are common as well for all find-scripts. This makes two modules that can be shared between all find-scripts via a Perform Script!

--sd

[Fenton]

I guess I would also need that emoticon, in a darker shade. The file I uploaded earlier really did not work properly. Especially when you tried to get it to delete. It had to delete the Yahrzeit record, because that was where the portal relationship was pointed. So another approach is needed.

I redid the portal so that it points at the join table, which is more normal. I also wanted it to be able to choose an existing Yahrzeit, which it can do. What it cannot do is create an entirely new Y record. Which is OK, because with a new one there's other data that needs entry at that time anyway.

So it has a button to create a new Yahrzeit record; and it goes to that table. However, it can also, behind the scenes, create the join record in that table. It knows the Family ID, from where you started; and, after creating the Y record, it knows the Yid. So I'm setting both those IDs into globals in their own table, then going to the join table, creating the record, setting the IDs, then returning to the Yahrzeit table, for finishing date entry.

Obscure but helpful fact: you do not need the "name" of the Yahrzeit record to create the join record, only the ID. So it can be created immediately, before data entry. It's a good idea to Commit appropriately though.

Choosing IDs in the join table from drop-down lists is basically a hassle. A person shouldn't have to choose both IDs, if they've started from one of the other tables. I modified your "Help-New Join" script, so it sets the Yid.

I also moved the graphics from Yahrzeit to Join Yahrzeit, so they can still appear in the portal. The reason for them is that plain objects will appear on the blank line, which looks goofy, and does nothing.

But a calculation will only appear on a "real" row. That's why the calculation. A global field will also appear on the blank row (in version 7).

The "New" Yahrzeit script looks complicated. But it's really just one simple step at a time, moving between the tables. Pretty much how you'd do it manually.

Synagogue4.zip

[JSRunnels]

I can't seem to open the file. I get an error that says "The temporary file'FMTEMPFM41d072180eaa.tmp' could not be created on the system disk. Use a different name, make more room on the disk or unlock it."

Any ideas? I haven't had any trouble opening the previous files and am very eager to see how this one works...?

[JSRunnels]

Nevermind, I just restarted FMP 7 and it works now.

It is SO COOL!! Now I just have to figure out how to move it all to my database (which I have continued to tweek...)

That is so exactly what I wanted it to do. That way they check to see if the yahrzeit is already in the database, select it if it is or create a new one if it is not.

I am assuming that this is pretty much how you would do it for individuals/committees, too, so maybe I will tackle that after all. (I may not; I just need to be able to print labels, send emails, etc. and seem to be able to do that the way I have it set up now...)

I really cannot thank you enough for your generous help. I love the Internet. I love FMforums. It really inspires faith in the human race. (Mushy, but true.)

Jessica

[Fenton]

You shouldn't have a lot of trouble moving it. The complex part is the New Yahrzeit script, and you can import that. I also modified your Join Yahrzeit script substantially.* (You can create a named and sized window in one step.)

You also need to create those global fields for the IDs. They're at the top if you sort by field, "_gFid", "_gYid", etc.. The "_gFid" naming convention is just the way I name global fields. Yes, it puts them all at the top in 7; which might look ugly to users (who seldom if ever see them); but since these are the fields I use the most, I don't mind.

"_cgArrow" would tell me, "this is a calculation field referencing a global field." That keeps all such away from other calculation fields, which are just "_cName."

Another trick I used is to make the text of the ID fields white. So they're invisible to the user. They still drop down black. There's other ways, but this is simple and cheap. The button is just an object; no script. It's only there so people know to click.

The many-to-many structure and techniques with the Family, Yahrzeits and Join table would be about the same for any such tables.

Personally I try to avoid drop-down lists for any choice that could have a lot (thousands) of records in the list. I use a pop up "filtered" portal instead. There are a couple example files in the Sample Files forum I've done.

*I also changed that Join List layout. The Tab Order is important. I added a "_gDummy" global container field. It gives the tab somewhere to go to without causing one of the drop-downs to drop. It has to have somewhere else to go. A global container field can be made very small, 1x1 pixel, with no border. You probably can't even see it; to the right of the Family::Envelope name.

[JSRunnels]

So far it is going great. Thanks for the tip on importing the scripts. I was just getting ready to copy them in by hand.

I was wondering what that _gDummy was for. I think I will use that on the Anniversary/Birthday/Yahrzeit printout layouts as well.

The Yahrzeit list can be pretty long. As you may have guessed by where the Yid starts, there are over 600 of them right now. Fortunately, (considering what that data really is) you don't need to enter them very often... I can't really think what criteria I would use to filter the list when someone is checking to see if the Yahrzeit is already in the database, so I guess the drop down list is the best option...

So far the only problem I seem to be having is that on the Member Data fom, where the little trash cans and arrows ought to show up, I have a bunch of zeros...

So here are two other small things I would love the DB to do, but can't quite figure out:

1) When you create the new Yahrzeit (in the super slick new way), it would be great if it would add a note in the notes field to the effect of: "This Yahrzeit was created on "Creation Date" for the "Family Name" family. As I said, there are often times when you want to keep the Yahrzeit, but there are also times to cull them. (ie. Yahrzeits for member families that have moved away and observe the yahrzeit in their new synagogue.) A couple of years down the road, no one can remember who the yahrzeit for Ethel Blum was originally be observed by. You end up erring on the side of caution and keeping it. The Yahrzeits can stack up pretty fast that way. If I could add that information into the notes field, it would be easier to cull the list in the future..

2) For the scripts that print the lists/labels for Anniversaries and Birthdays and the script that prints all the necessary lists/labels for the Yahrzeit -- in both of those scripts you must choose the month (Julian or Hebrew) twice. (Once for anniversaries, then again for birthdays. Once for the Yahrzeit list, then again for the Join list.) It would be nice to set it up to select "January" once and then have it get the appropriate data from both tables... Then the user wouldn't even have to mess with entering data on the actual printout layouts at all.

Thanks again. This part has been really exciting for me. I have a huge new respect for computer programmers...

[JSRunnels]

BTW, on the copy you have the notes field in the Yahrzeit data layout incorrectly points to the related table "Individual" notes field. It should go to yahrzeit::notes.

[JSRunnels]

One more little problem. The drop down arrow in the family data layout is not appearing on the entries I imported except where there is already a yahrzeit. In other words, there seems to be no way to "add" a yahrzeit from the drop down list. (I suspect they are probably hiding in the same place as my trash cans and arrows..., but I can't figure out how to make them reveal themselves )

[JSRunnels]

Okay, I still haven't figured out how to get my trash cans/arrows/drop downs to appear, but I have moved on to another (related) issue.

I am thinking that there is really no good reason for the Join Yahrzeit script/ help file. After all, the way it is set up now (once I get the "new" - actually "select from list" drop downs to appear), you can add a family through the member data layout. Right?

And I can delete that join by just clicking on the trash can (without deleting the actual yahrzeit.) So my next question is why can't I delete a family from the yahrzeit layout (in the portal) without actually deleting the family. It seems like this ought to be able to work both ways, but when I add a trash can to the family portal in the yahrzeit data layout, it goes ahead and deletes the actual family entry. This seems wierd to me, because I checked the relationships and deletion of the family entry is unchecked...

Anyway, I will keep working on it, but I suspect this is all tied together in some way that I am just too inexperienced to understand...

[Fenton]

1) I see; if you delete a Family, their Join Yahrzeit records would also be deleted. But then you end up with "orphaned" Yahrzeit records. I see that it might be useful to have this note then, for those who once had join records, but now have none.

If the Yahrzeit record is created via that button, then you could add that to the script. When you create the Yahrzeit record, you already know the Family; it is sitting in the Family:_gFid field, which you can access from anywhere. You also know the current date (duh). So it can be added to the Yahrzeit note.

But you would need to get the Family data somehow. You could create and set a _gFid global field in Yahrzeit instead (it doesn't much matter to the Join table where it gets that data, just modify the script; or set it in both). Then use that, via a relationship back to Family, to get the family name; assuming you want the name also. Or just put the Fid (_gFid), which requires no relationship. Why not? This is not really a note for people to "browse." Or create an "original Fid" field in the Yahrzeit, which is relationally correct as there's only one.

But what if the original family moves away, but a later family had also "adopted" this Yahrzeit? The note is then useless; unless you add each new family to the note. In which case you're duplicating the join table in a note field, but it's more convoluted. Personally I prefer to stay relationally correct, and use the following method.

An alternative "lean & mean" approach would be to consider a Yahrzeit that does not have a join record is basically obsolete. This is assuming that you always create the FamilyID join record at the same time you create the Yahrzeit record (or see < date below).

It would be an unstored Find, hence not real fast. But with 1000 records not a problem. (Could alternatively be a Loop.) Do it as a maintenance routine; once a month should be sufficient.

Just put the Yid from the join table on the layout, and do a search for ">" & 0, Omit (it's a number field). You could add a "<" & DateCreated request first, to only find old orphans.

2) Another obscure but useful fact: Even though you cannot enter a global field in Find mode, you can use Set Field to set one. That is how you can store Find criteria. So, after they've entered the month, before the Perform Find, set the Jewish Month into a global (text). Then you don't have to Pause the next time. Enter Find mode, set the Jewish Month to the global month, and Perform Find.

You might want to upload a clone of your file again, after consideration. Something is wrong with those global containers.

[JSRunnels]

Here is a new clone. Please let me know what I did wrong. I really do want to learn (even though I will probably NEVER use these skills again -- this is volunteer work; I am an M.D./mommy in my real life...)

A note on the yahrzeit, some time you want to purge them, sometimes you don't. If it is an actual congretational member who dies, we will probably keep it forever. We also have some Holocaust victims that are not "attached" to any family, etc. On the other hand, whenever a new family joins the congregation, they bring their own family's yahrzeits with them. If they move (which happens alot around here..), there is no need to continue to observe those yahrzeits as a congregation. It's complex, but the more data we have, the more likely we are to know what the right thing to do is.

SynagogueDatabaseClone.fp7.zip

[Fenton]

It's deleting the Family because the portal relationship is to Family. It should be pointing at the join table. The Envelope Name field can still point to Family; because the join file is related to it, in a direct line from that portal row.

I tend to agree about dumping the "Add Family" to Yahrzeit. It's clunky and really little help, since there's bound to be more Families than Ys, so the drop-down list is huge, and you would never add a Y with no Family, right? I would say "create all Y's from Family."

Or use my "filtered portal" technique to choose from a pop up portal of Families. But we better solve the other problems before we add more -|

[Fenton]

Things "not quite right":

1. The "New" Yahrzeit button is not attached to its script (at the bottom of the scripts).

2. You need a little more room in that portal.

3. In the calculation for the global trash and arrow, the result (drop-down, at the bottom of the dialog) must be Container, not Number (which is the default unless you change it).

4. You have to go to the Join Graphics layout, then copy/paste the actual objects (which are there) into the global fields.

Copy in Layout mode, Paste in Browse mode. (I couldn't tell if you'd done this, because Save As Clone wipes out all data including global fields.)

You could/should script the deletion of a Family. It could test various things about any related Yahrzeit record(s).

Count( relationship::field with data) will count related records.

1. Count ( Join Yahrzeit::Fid) says if there is any join records at all

2. If so, go to them, in Yahrzeit, via relationship

3. Loop through them, processing each (a few at most)

4. Count (Join Yahrzeit::Yid) (to Joins, from Y) tells you how many joins this Y has. If it's > 1, don't delete, other families claim it.

5. If = 1, check whatever other field (Holocaust, etc.)

6. If blank, does not meet conditions to keep, delete

7. ELSE Go to Record [next, exit after last]

8. Exit Loop if Record Number = Found Count (it's the last record; this could happen unexpectedly, if the last is deleted. Sometimes it's tricky to stop a loop.)

ELSE is so you don't skip records; delete automatically places you on the next record.

9. Return to Family record. Delete it.

[JSRunnels]

I am working on the script to place text in the Yahrzeit note field.

I put a set field right between the new record and commit record lines.

I though that: Set Field to calculation: "This yahrzeit was originally obsevered by the " + "Families::Family Name" + "Family. This entry was created on " + "Yahrzeit::Creation Date" + "." should work, but, of course it doesn't. That just returns the date. Similarly, if I take out all the calculation stuff, it will return, "This yahrzeit was originally obsevered by the ". Anything in between, returns a zero. I assume I am doing something wrong with the language here. Is there a special way to convert the data from the field into text to add to my text string?

The idea is that "cleaning out" the yahrzeit list won't happen very often, but it would be nice to start off with some historical data to use as a guide.

[Fenton]

At first glance it appears that you are using a programming language type syntax inside the FileMaker calculation dialog. That's not necessary, not how FileMaker does it. If you surround FileMaker field names with quotes, you basically end up with the field name as text; it does not evaluate the fields, such as would happen in JavaScript, etc.. It may not be an error, but it's just text.

In FileMaker only text is within quotes, field names are not,* and & (ampersand) is used to glue things together. The plus sign "+" is reserved for actual arithmetic.

"This yahrzeit was originally observed by the " & Families::Family Name & " Family. This entry was created on " & Yahrzeit::Creation Date & "."

It cannot go right after the Yahrzeit record is created and committed. It has to go as the next-to-last step in that script, right near the end. Because, it is not until the Join table record is created and completed that the Family: path is connected all the way back. Just set it, then go to the First Name field. No real need to commit yet.

*I guess they sometimes can be inside quotes now, in an Evaluate function. But that's not needed here.

[JSRunnels]

2) Another obscure but useful fact: Even though you cannot enter a global field in Find mode, you can use Set Field to set one. That is how you can store Find criteria. So, after they've entered the month, before the Perform Find, set the Jewish Month into a global (text). Then you don't have to Pause the next time. Enter Find mode, set the Jewish Month to the global month, and Perform Find.

I can't get this to work. Please see my clone and look at the Monthly lists Script. I am messing up the Set Field in there somewhere. It performs fine until it hits the second set field command and then I get an error message that says: "This action cannot be performed because this field is not modifiable."

I am sure it is something basic, but I would like to get this to work, then I can duplicate it for the Yahrzeit script.

Additionally, I may add the Anni/BD/YZ scripts all into the monthly script. I would (ultimately) like to have it do the export/print lists (for both Anni & BD) first and then the labels. That way I can eventually add an option: "Print labels or envelopes?" to the whole shebang...

The script would then print the lists and export the files for Anni & BD info., then pause and ask about labels vs. envelopes, then print those all at one time. (That way you only have to mess with the printer once.

Clone.fp7.zip

[JSRunnels]

I got the little down arrow drop downs to appear. I just deleted everything that I had for that portal and copied in everything that you had. It bugs me that I can't figure out where my error was... (how am I going to recreate this/fix it in the future if I don't know what the problem was,) but at least it is working now...

[Fenton]

What you're trying to do is to set the Find criteria into a global field. So it should happen while you're in Find mode, before you Perform Find. But, it doesn't really matter, because it's going to be the same thing after Perform Find. So that's not the problem.

But then you DO have to set the global into the field you want to find in while you IN FIND mode. You're doing in Browse mode. The reason it's causing an error is because it's a calculation field. That's lucky, 'cause otherwise you'd be overwriting data.

Basically you need to learn to be more flexible with the way you do Finds. As I said before, you can use Set Field to enter criteria during Find mode. These "preset" Perform Find and Constrain Find single steps are good for straight data finds; but they are lousy for complex finds when you need to set fields from (or to/from) global fields. I don't know how to enter data from fields in those things. As I said, I'm old school. I use them when appropriate, but otherwise not.

Synagogue5.zip

[JSRunnels]

It makes so much sense when I look at it now in the light of day. I am going to chalk my obtuse-ness up to lack of sleep and the late hour last night...

I should have re-examined your previous notes on the set function and probably would have gotten it straight away. Thanks for taking the time to re-explain it. I am going to clean up those scripts (and possible some of the others) to make them utilize the set field function properly. (I have constrains all over the place, where they are probably unnecessary.)

Of course, a week ago I was pleased with myself for figuring out how to make a custom dialog box! So, I think I have come a pretty long way. -- Thanks to your help.

I am going to try to create that delete family script next. I haven't used a loop before, but I think I followed the logic of your suggestion. I will let you know if (optimism at its best) I run into any trouble.

One other thing that is bugging me with the database at this point is the serial numbers. I want to reserialize all the data (to compress the numbers down and add zeros to the beginning so they look nice the the various drop downs.) It would also be nice to import it in a presorted order, so there is a little less "work" do do each time the program sorts, etc. - I know this will get out of whack soon, but it seems like a good idea. At the very least, we won't hit the 1000+ numbers for a while and the data will line up nicely in drop down lists...

I am thinking that I can export to a excel file, add a new serial number, reimport to clone with new serial number going into a new iD field, use that data to populate the join fields and then use a calculation to copy that data into the Fid & Yid fields. Does that make any sense?