gadfly

That was exactly what I needed. I was trying more complex calculations and your solution was so simple I couldn't see it. Thanks very much.

The date field doesn't actually hold dates, just text like this: jan1, jan2, jan3, etc. Therefore, I thought it would be best to simply wipe out the existing data and somehow plug in real dates in the proper sequence. I was thinking that since it's possible to insert a sequence of serial numbers there ought to be a way to insert a sequence of dates, which are really just numbers, aren't they? I just don't have the experience working with date calculations to figure it out. I hope that makes it more clear. Thanks.

I'm importing some data from a spreadsheet that includes a column of dates that were not formated as dates. It's a simple table containing some daily class attendance numbers for a single year. I've got a total of 365 records and they are sorted correctly so all I need to do is insert the dates into the date field. Is there some kind of simple calculation I can use to replace the contents of the date field with a sequence of dates starting January 1, 2005 and ending Decmber 31, 2005? TIA

O'Reilly's Missing Manual for FM 8 is out... anybody got any opinions on it?

Thanks for the comment. I went ahead and recreated the database with multiple tables. The more I learn about 7, the more I like it. What a huge change. Thanks.

Thanks for the suggestion. I meant to ask if there was any method to combine tables within FMPro 7. My tables are not that complex and it will probably take me less than two hours to recreate them in a single database. It's not a big enough issue to warrant having to spend money on another product. I guess I'm really more interested in the pros and cons of having tables combined. Thanks.

I've got an old project that I'm bringing back from the dead. It was originally created in FM Pro 5 and consists of 5 separate databases each with one table. Now that I'm using version 7 I'm considering starting over and recreating all 5 tables in one database. None of them need to stand alone. I like the idea of combining them so that I only have one project file but I'm not sure if it's worth the trouble to recreate all of them. The questions: Is there a way to import or combine tables? Are there advantages or disadvantages to having my tables combined or separated?

I am trying to decide between these two books, one of which was recommended above: Learn Filemaker Pro 7 (has 9 customer reviews with an average of 4.5 stars) or Using Filemaker Pro 7 (has two 5 star customer reviews) Is anybody familiar enough with both of these to comment on the differences? I've been using FM since version 3 so I know the program fairly well but I'm a novice when it comes to scripting and complex calculations. Of course, if you are only familiar with one of them, I'd still love to hear what you think of it.

I'm ecstatic! The relationship worked but it needed to be between Attendance and MembershipCards: Attendance::Name = MembershipCard::Name AND Attendance::gStatusActive = MembershipCard::Status does exactly what I want! Thank you so much. I know that I have much to learn and the help I received here has gotten me started. My next thread will have a much easier and more general question...

I've made some progress using your suggestions. Things don't work for me exactly as you suggested but I managed to get close on some of it. For example, you suggested a syntax for scripting with Set Field that I am unable to replicate: Set Field [ MembershipCard::Status ; "Active" ] and... Set Field [ MembershipCardActive::Status ; "Inactive" ] but I don't see how to create such a script. Scripting for me is limited to selecting items from a list of commands and none of allow the option shown above. Instead I had to specify a calculation: Set Field [clientcards:::status; If (clientcards::ends < Get(CurrentDate) ; "expired" ; ( If (clientcards::cardcount ? clientcards::classes ; "expired" ; "active"))) I think mine works as you intended (though I still have a lot to learn about when and how I can trigger a script) but I'm a little confused that I couldn't format exactly as you suggested. The only way I can use Set Field is by specifying a calculation. Is there a way to write free form scripts? I seem to be limited to choosing options from the scripting dialog window. Unfortunately, I haven't been able to get this next suggestion to work: The MembershipCardActive relationship above is defined as Member::MemberID = MembershipCard::MemberID AND Member::gStatusActive = MembershipCard::Status I'm a little confused about all the different relationships between the various tables. If I set up a relationship in one table, is that same relationship included in the relationships diagram for both databases (I've got each table set up as it's own database for no other reason than that's the way I originally set this up when I was working in FMPro 5)? The global filed is set up and seems to work fine and the relationship as described above exists but I still see the Attendance join file pulling up the first record in the table, regardless of status contents. I'll keep playing around with this last part and if I continue to have no luck I'll try to post a more clear description of how I've got things configured. Thanks.

All right, I'll try that. I got sidetracked and won't be able to work on this for a day or two. I'll give you an update when I get a chance. I much appreciate your efforts and I will do my best to learn what I can from them. Thanks.

Wow. That was quite an elaboration. I'm going to spend the next few hours trying to work through it all. I do see a continuing problem but I'm going to attempt to implement your suggestion if for no other reason than as a learning exercise. Thank you so much for taking the time to explain that to me. It is much appreciated. The continuing problem is that I also need to check card expiration dates in addition to number of classes allowed. For example, a Monthly Card allows someone to attend as many classes as they want in a 30 day period (I also have 3 Month, 6 Month and 1 year Cards). That's why I have been using a calculation to determine the active/expired status. The calculation not only compares the number of classes attended versus number allowed on the card, but it also needs to compare the class date to the card's expiration date. The numbered class cards also have expiration dates. A 5 Class Card must be used within 30 days -- it will expire once 5 classes are attended OR if 30 days have passed since it was purchased, regardless of how many classes were attended. Many thanks.

Transpower & Ender (and anyone else who might be reading) I wonder if you could elaborate on your previous comments/suggestions. I think I understand the problem with the unstored calculation but suppose the Status field were not a calculation... Are you suggesting that it's possible to do some kind of lookup that references the Name field AND the Status field? Also, I don't understand the suggestion about the Count of visits number field. I've got the Count of visits field in place as a way to determine when a card has been used up but I don't see how that field could have anything to do with establishing a relationship between classes and cards. I hope I'm not coming across as so dense that I'm looking for someone to do my work for me. I'm trying to learn from your suggestions but I'm pretty green, I guess. This is probably the most important aspect of my database. I can't imagine how to continue until I get this figured out. I suppose I may have to find a consultant again but I'm hesitant because the last time I hired someone I spent a lot of money and ended up exactly where I started. Thanks again. yogatables.zip

I tried using the new lookup functions to no avail. I don't quite follow your other suggestions. I really am a novice. Although I've been using FMPro since version 3, I have never really done much with scripting, relationships (beyond simple lookups) or complex calculations. I've attached an archive of the files (stripped down) in case anyone wants to take a look and see what I'm trying to do. The question still to be answered is simple - alas, a solution may not be. You will see that mister jones has three class cards, two of them expired and one active. When entering mister jones into the Classes database, how would it be possible to have the CardID of the "active" record looked up and not any of the others? It's a stumper... yogatables.zip

The status field *is* a calculated field. I've also already got a concatenated field combining CardID and status. The problem is that I want to do a lookup using Name as the key. As long as I have multiple records containing the same name I don't see how it's possible to link to only one of them. I think this is possible because I use a proprietary windows program that does exactly this. Unfortunately, it's a discontinued program that is no longer supported and I don't have access to the source code to see how it was done. I'm trying to create my own version of this software using FMPro. I actually spent a few weeks trying to solve this last year -- even paid someone a hefty consultant fee to help me figure it out -- but ended up giving up in frustration. It seems like it ought to be possible but the solution escapes me. Given the difficulties, does my idea about splitting up the class cards into two tables sound more feasible? If I have a table that only contains one Card record per name then looking up the current data is simple. The problem then becomes one of how to keep data from and maintain access to past cards once they are no longer current. Hence my question about whether or not it's possible to create a script that... exports a single record to another database; resets all fields in that record to their default values (or blank); auto-generates a new, unique Card ID in the appropriate field. I also wonder if the difficulty lies more in my inability to accurately describe what it is I want to do... Thanks.

Greetings fellow yogini... I'm still stuck... I've done what you suggested but I still have a problem. I think this would work if I was using the ClientCard ID to add names to Classes. Unfortunately, the "cards" are virtual - there is no physical card and I don't want to have to pay attention to the CardID. I want to use the Name field as my primary key. Each client (Name) will have multiple records in ClientCards. Ideally, only one of them would be allowed to contain the status, "active" and the other records would all be "expired." I want to be able to enter names into Classes and have only the most recent card information looked up - whether it is active or expired. I've attached another diagram to illustrate - this one is of the actual tables in FMPro. Thanks again.

Ah, I see. No, that's not correct. I should have given a more detailed description. This is for a yoga studio and the classes work on a drop-in basis so each class is a one time event. I actually use class date and time to create a unique ID for each class. I've actually made some progress with the task I was attempting to describe and figuring it out was not that difficult (especially with some of the help I've gotten here). I've kept the current and expired cards in a single table and my portal in the ClientList table only displays one record from the ClientCards table - sorting by date allows me to only display the most recent card. That turned out to be a pretty simple solution but I now realize my problem is bigger than that and will be more of a challenge... Here are the tables: ClientList ClientCards Classes Attendance (I've attached a diagram to illustrate the relationships.) The problem occurs when a client attends a class. The Name field in the Attendance portal is a Value List based on the Name field in ClientList. When I enter a name here, Attendance looks up ClientID from ClientList and CardID from ClientCards. I am then able to count how many times a particular Card has been used (from Attendance) so that when the 5 Class Card has been used 5 times the card status changes to "expired." All well and good. I can also count how many times a Client's Name appears in Attendance, allowing me to track the total number of times they have attended a class. When a Card is expired, it needs to be replaced with a new Card. Now the challenge is how to have the Attendance portal in Classes pull up the new CardID instead of the expired one? I hope the attached diagram makes this clear. This is why I was originally thinking I might need to create a script to export expired cards to another table, reset the fields to allow me to then modify the existing record with new values. As long as each Client only has one class card with a unique ID, the counting works. If I keep the current and expired cards (records) in ClientCards, how does the Attendance portal lookup the correct card ID?

Transpower, I'm not sure I follow you. I don't understand why you suggest separate tables for class and class date... Right now I've got a unique ID for each student, each card and each class (using date plus time to allow multiple classes on the same day). What I'm trying to figure out is how to deal with expiring and new cards. My system works great as long as the card ID remains the same. When I look at my layout in ClientList I want to see the most recently expired card or the active card data displayed in a portal. I can do that fine with my present configuration. What I can't figure out is how to allow each client to be linked to multiple class cards (records in ClientCard) but only display the most recent card in the portal. Make sense? Thanks.

I'm working on a membership database. I have three primary tables: ClientList, ClientCards and ClassAttendance. Every time a new class begins, I create a record for that class, lookup clients from ClientList, enter them into the attendance roster for that class and at the same time, lookup information about the current membership status from ClientCards for display. This allows me to see current membership status for each client as they check in for the class. When a card is expired and they buy a new class card, I don't want that card's information to disappear so we can keep track of the history. I'm not sure the best way to handle having multiple card records while only displaying either the active card info or the most recent expired card info. I can think of two possible approaches: 1) Create two separate tables: CurrentClientCards & ArchivedClientCards. When clients check in for class, have the lookup draw from CurrentClientCards. If they are expired, I would create a button with script attached to "Buy a New Card." This script would then export the current client card record to ArchivedClientCards, reset all fields in the current card record to blank or their default values, and then allow me to enter in the new card information. I have no idea is this is possible, or how complicated it would be. 2) Keep all card information (active and expired) in a single table (ClientCards), but only display the information from the most recent card record for each client. Not only do I not know how to do that, I also wonder if it might create other challenges such as, How to deal with multiple records for each client. Would it be possible to allow a client to have multiple records but only allow one of them to have the Status field contain the data, "active"? I hope this adequately explains what I'm trying to do... Any ideas, comments, suggestions? I'm open to other solutions besides these two. TIA

Thank you very much. My next question will be a little more challenging, I think...

How cool! My help comes from MoonShadow just minutes after I left the TV after watching Yusef Islam (Cat Stevens) on Larry King Live. He even made a reference to the song, "Moon Shadow." Nice coincidence, since that's the first song I ever tried to learn on guitar. I though I had tried Get(CurrentDate) and it didn't work but maybe I had some other error getting in the way. Anyway, thanks for that. Now my next question: I have a ClientList table that includes a portal to display the current card Status from the ClientCard table (where the calculation field is). As it works now, the portal displays the results from the last time the calculation was performed which may have been before today. Is there a way to force the ClientCard table to recalculate the Status field so that references to it are always up to date?

I'm trying to create a simple If statement for a membership database. I want to use a simple calculation like this: If ( ExpirationDate < TODAY ; "expired" ; "active") but I can't figure out how to substitute the current date for TODAY. What's the simplest solution? (I've been using FMPro for years (since version 3) but I've never really done anything with date calculations.) TIA