Lougee

I knew I could rewrite the calculation to account for the hyphen and prevent future problems. I did not realize that would 'reverse engineer' the incorrectly formatted records. So simple. Thank you.

SSN field formatted to automatically insert hyphens in the correct places -- xxx-xx-xxxx -- so I don't have to stop twice in every entry to find that darned hyphen key. Works flawlessly. However, when someone else entered ID data, she typed in the hyphens so I have some records that, when formatted, look like xxx--x-xxxx. (The calculation is: Left (SSN; 3) & "-" & middle (SSN; 4; 2) & "-" & right (SSN; 4).) Is there someway I can see the original -- unformatted, just numbers -- SSN that I typed in?

Am I wrong? OK, I understand now. The options dialogue when performing an import -- apply serial number, etc, gives me this option. I've NOT been doing this because I didn't want to break any calculations, serial numbers, relationships, etc. The calculated participation/person field doesn't have that problem. Which is the reasoning for using the calc. Thanks for bringing my misunderstanding to my attention. I'll go back and try it.

I would if these records were always going to be created new in this table. But they might also be imported from several other files and auto-enter calculations don't work on imported records. Looks like I need to rethink this.

Yes, and yes. If it's not, unique, the inputs aren't accepted. Some wort of dialog on the order of: your data doesn't generate a unique result. Please check your data and reenter. Or words to that effect. The point here is to be able to join a contact ID (one input) and a participation year (the other input) and generate a unique identifying 'participation year/person'.

Is it possible to restrict the result of a standard (not auto-enter) calculation field to unique values only?

I was exporting because the calculation that add up the total years of participation lives in the contactInformation table and counts ALL the years so I couldn't get totals up to a certain year and not after. But I could export only the relevant activeYear information. It feels like duplication of data, but now I also have a calc totaling active years in the activeYears table so a simple find should work for me. Thanks for the referral to the 'fast summary' example. It's way beyond what I would have been able to develop for myself; I never even thought of using variables. But it looks like it does exactly what I need. I'm having a great time playing with it and poking around under the hood.

Exactly. The most recent year can be anywhere from 1988 to the present, and the parameters for selecting participants can vary with every search (i.e., all the females in years 2001-2003, or all the students in years 1999 and 2001 by gender). We are a grant-funded organization and must report to our funders on a quarterly basis. They let us know what they want reported and each grantor might ask for different demographic/participation information. This quarter, one of our funders is asking about total high school participants, by gender, by ethnicity, by longevity of participation. The find process is easy enough, but the summarization calcs are what I haven't been able to figure out. Actually, I have figured out half of the problem -- I can calculate the number of occurances for each ID. But I still can't summarize the number of unique IDs for each year.

I need to extract information about our program participants from years gone by, without including any more recent information. I can select a program year -- say 2004/05 (school year) and find all the partipants that year and earlier (each year for each participant is a separate record in the AY_activeYears table). By exporting the contact_ID and activeYear of the found records of active years that fall into my range to a temporary FM file, I can eliminate any more recent information. Now I want to count how many of each contact_ID I have in my temporary file. That number equals the number of years each person has participated (longevity). For instance, I might have 7 records for contact_ID #1 which equals 7 years of participation or 2 records (or 2 years) for #21, etc. I want a calc/summary to give me the total years/records for each contact_ID. Then I need to summarize how many contact_IDs there are with each X number of years/records. It should be simple but I can't make it work. Please help. If I'm not clear (not unusual) I apologize.

Thanks, Jerry. I'll give it a try.

I want to create a 'dashboard' which will show the several most recent additions/changes across multiple tables. For example, I want to see a new contact added on 1/1 (contacts table), an agenda modified on 1/3 (event table), a participant who cancelled on 1/4 (registrations table) and a different contact whose address was changed on 1/7, assuming these are the 4 most recent additions/changes to the db. Can this be done and how might I approach it? As always, I very much appreciate your expert help.

Basically the title says it all. I'm wondering if it's possible to create a value list whose values are the result of a calculation. For example, an event that begins on 1/1/2010 and ends on 1/5/2010... Is it possible to create a calculated field that would return a list of the dates (1/1, 1/2, 1/3, 1/4, 1/5) and could that list then be used as a value list for a drop down field? TIA

Comment, Thanks for the suggestion. Guess I'm getting a little better at understanding how to approach these problems. I had just started to play around with the pop-up window for entering or editing attendance details, with just the results showing in the portal. I will post my end result. Or be back for more help, whichever comes first.

Thanks much, comment. Working on it. I'll shout it to the world when I get it working! IS there any way to show multiple related records other than a portal? Something new or tricky or more knowledgeable than I am (I've so much still to learn..)

Well, I've had a lot of fun, and learned a lot too, but I think I still need some more help. Inky Phil, your solution works -- I can enter data into either the top or the bottom field depending on the state of the chooser field. But even if I enter text into the bottom field, when I tab out, the top field remains on top. I need to figure out how to keep the field that corresponds to what was selected in the chooser field on top and visible. Any more suggestions, anybody? Abbe

Vaughan, I wish I did have FM 10, but it's not going to happen for a while. Inky Phil (), thanks for the sample file. Sounds like a great option. Off to play with it right now and report back soon. Abbe

Basically, I'm trying to get FileMaker to wash the dishes and walk the dog. Probably not possible, but I thought that the combined wisdom of this forum might have some tips and tricks. I'm registering teachers for a professional development seminar. Right now, I can register the teacher and all registrants show up as expected in a portal that shows name, school, attending status (yes, no, no response). Now I want to add 2 pieces of information to the portal. If the teacher is coming -- a definite YES -- I want to show a field called meals_lodging. This has checkboxes and a value list with values such as Breakfast_monday or Lodging_monday, etc for all the seminar meals and lodging. The user can select those meals and nights' lodging appropriate for each teacher. Works fine. If the teacher is a definite NO, I need to document their reason for not attending. That's a field called attending_NO_reason. The 2 fields have conditional formatting such that meals_lodging isn't visible unless attending_status = yes and attending_NO_reason only shows up when the NO is selected as the status. Now for the walking the dog part. Is there any combination of functions, custom functions, tricks or magic that makes it possible to show meals_lodging OR attending_NO_reason depending on the teachers attending status IN THE SAME SPACE. A calculated field that shows the field meals_lodging or attending_NO_reason. Actually, I can do this using conditional formatting and stacked fields. But I can't select and enter data into the fields. Into the top of the stacked fields, whichever field I have on top, but not into the bottom. I need magic to show the appropriate field, or bring the appropriate field to the top so the user can enter data. I hope I'm explaining this clearly. Happy to give more information if requested. And TIA. I find the information from this forum invaluable.

I would have expected that the large text would cause the field to extend outside the portal and break how each participant displays but I tried it and it works!! Exactly what I was hoping for. OK, not exactly since I still have to make it unclickable when invisible, but 99% perfect. There are sooo many ways to think outside the box... But I'm slowly learning. Thanks a million, Tom. ---- The light came on last evening -- it's a FileMaker natural law that text size doesn't change the field size. I know this well so my earlier comment about about breaking the portal display was kind of a 'duh.' But I still wouldn't have thought of this myself.

Thanks, Lee. I'm sorry I was confusing. My search was exactly what you said (I just translated it when I wrote my post). There were 7 results and none of them addressed my particular question.

I've searched the entire site looking for conditional AND format AND checkbox but didn't find anything that addresses my particular question. In an event tracking db, I have a portal in a layout (based on the events table) that shows all the expected participants (from a join table - registrations - between participants and events) for a specific event. In the portal I have the participant's name, attendingStatus (yes, no, no response), and a field called activities_mealsLodging. This field is linked to a value list -- a hard-coded list of possible meals and nights' lodging (basically the same for all events) and looks something like breakfast_1, breakfast_2, lunch_1, lodging_1, lodging_2, etc and is formatted as a checkbox set. The user can select which meals the participant will be eating and which nights he/she will be staying from right in the portal. Very convenient. Now I want the meals/lodging choices to show only once a participant has officially registered (attendingStatus = yes) and be invisible until then. Easy enough to do with conditional formatting. I set the text and fill to be the background color unless the attendingStatus field = yes. Then the text is black and the checkboxes are available for selection. Like I said, this works very well. Except... Setting the text and fill to the background color (or transparent for the bg) doesn't change the color of the checkboxes themselves. I have an apparently empty space but with checkboxes showing and I can't find or figure out a way to make the boxes themselves invisible too. Since it's already in a portal, the invisibility trick won't work. And I don't need to drill down so far that I need to create separate records for each meal and night's lodging. This way give enough detail without being excessive. So my question is -- is there any way to make the checkbox boxes invisible using conditional formatting? I don't think so but I really, really hope that I'm wrong.

I've fixed it. Well, actually I realized there's a very simple work around that is not going to create a bigger problem later on. I added a space before and after the ethnicCode. Used find/replace to change current codes to the revised codes and changed the ethnicCode definitions in all affected calculations. Slick and quick. I only had to manually change about 50 out of 10,000 records. Works like a charm and gives me exactly what I want on the printed applications. Thanks to all for your help and I have no doubt I'll be back -- sooner rather than later, I'm afraid -- for more expert advise.

Thanks, Lee. Definitely worth the time to create a clone of the problem if it will help someone help me. I'm going to play around with IdealData's additional 'if condition' first. If (no pun intended) I can't make that work, I'll create a small clone of my problem and upload it.

Oops...I always put each condition on a separate line when constructing my calculations but I rewrote, didn't copy/paste, my calc into my post. Thanks for the reminder. I'm off to play with my calculation again using your additional 'if statement' suggestion. I'll be back with the results. Abbe

Thank you for the suggestion, but as it is, it's still not enough. I thought I'd included all the pertinent information, but I see I did leave out important elements. Using checkboxes for the standard ethnic codes would work most of the time, but I'm trying to capture very diverse information. The EthnicCodeDetails_SHOW field is preprinted on the application of returning students. This so the student and/or parent can verify that we have included all the ethnic background information that THEY consider important. It often includes information such as "Canadian indian" or "Registered tribe member" or "Turkish." For example, one student's ethnicCode is "O" for 'other' and the details field says "Brazilian, Russian." My current calc evaluates the initial B in Brazilian as the ethnic code "B" and returns "African American/Blackrazilian, Russian." You get the idea. The calc does what I want but does it TOO well. I need to have it not calculate the first letter of a word in the details field that also is an ethnic code itself, e.g., the "B" in Brazilian. I hope this makes things clearer. Abbe 8-)

I'm close. I've almost got it. But there's still one glitch I haven't figured out. I'd be forever grateful to someone for clueing me in on what I'm doing wrong. I'm tracking students' ethnicity. There are 10 codes — AI/AN (American Indian/Alaskan Native), AS (Asian American), B (African American American/Black), HA (Hispanic), W (White) and several more but these are enough to illustrate my problem. There are many students with mixed ethnicity, i.e. Hispanic and Black. I track them as M (Multiple) and have a field called EthnicCodeDetails where I list the codes for their specific backgrounds. This student would be listed as ethnicity M and the EthnicCodeDetails field would show HA, B. Returning students' ethnic code is pre-printed on the program application using the EthnicCodeDetails field. I want to decode this to plain English so I've created a calc field called EthnicCodeDetails_SHOW. The calculation is: If ( not isEmpty ( ethnicCodeDetails ); Substitute ( ethnicCodeDetails ; [ "I" ; "Asian Indian"] ; ["AI/AN" ; "American Indian/Alaskan Native" ] ; [ "B" ; "African American/Black"] ; [ "AS" ; "Asian American/Asian"] ; ["HA" ; "Hispanic"] ; [ "PI" ; "Pacific Islander" ] ; [ "W"; "White"]; ["O" ; "Other"])) and it almost works. It breaks down when there's another word in the details field that begins with any of the specific codes. Take for example, a student whose ethnic details are AI/AN, W for American Indian/Alaskan Native, White. The 'show' calculation returns AAsian Indian/AN, White. The I in AI/AN is treated like another, separate code. Changing the order of the codes only changes which letters cause the glitch. I guess I'm looking for a way to stop the codes themselves from causing the calculation to reevaluate midstream. I hope I've explained myself clearly enough. If not, let me know. I'm using FMP Advanced 9.0v3 on a Mac running OS 10.5.7. Abbe Lougee