We have reset all users FileMaker related profile fields. Please take the opportunity to update your information,  this will provide background to members whom read your posts. Click here.

Jump to content

Auraboros

Members
  • Content count

    31
  • Joined

  • Last visited

Everything posted by Auraboros

  1. Hi...I have looked high and low for this solution...maybe it is too simple for anyone to comment on! I have a number field...say I enter 50. In one year from the date I entered 50 in that field, I would like it to automatically increase by 1, to 51. I do not want to have to re-enter the number, and if I don't visit that record with the field in it for 3 years, then I want it to increase by 3. Increase by 1 each year. OK, let me tell you what it is. I enter the ages of clients' children, next to their names, in a database. I do NOT want to have to enter their birthdays, nor even their birth year, I want to just enter the age, in a number, like "12". In a year from now I want that number to change to 13. I don't really care if it does this exactly on the date I entered it. It can change on January 1 each year, I don't care. It isn't meant to be accurate, it is just a basic idea of the kid's age, could be off by a whole year even. I don't care. I want this number to change each year...based on when I entered it if possible (it would then be "sort of" accurate)...so if 5 years go by before I look at that record again, I want the age to increase by 5 years. Seems simple...but every solution I've found requires that I enter the birthday, which I don't want to have to do...or even enter the year of birth...same problem. I want something very simple. (People get suspicious when you ask for their kids' birthdays...) Solution?? THANKS!!!!
  2. Thanks guys...I hope one day I have a handle on all this like all of you do. I have been taking courses but most of them do not get advanced enough. I love trying to figure this stuff out but I think there is some logic chip that is missing in my brain. I also make things too complicated. I got really close with this but I couldn't get my "script trigger on modify" to work correctly...it would snag and just sit there until I hit command-period. I think it is probably best just to have that yearly scheduled script run through all the records with the age field. Thank you both for the solution and suggestions! Todd
  3. I set up a script trigger on the age field so when modified it inserts the current day in another off layout field. But now I don't know what to do with that date. How do I get the contents of the "Age" field to add a year when 365 days have passed from the modify date? I think I am going about this the wrong way... Thanks Comment! Todd
  4. Go to Related Record in Portal broken

    Hi...I have created a database using a "modified" anchor buoy method and so far everything works great...but...I have a portal in a "join table" that works fine...however, my button that is supposed to open the record in the portal row does not function...it returns nothing. The records are indeed related (see RG) as the fields show up from the related table without relational conflicts, but this button that is supposed to open the "library" table with the related record pulled up simply does nothing. I have attached the DB. Also a screen shot of the table where the portal is (it is actually a join table layout, this may be the problem). I don't understand how the button (with the little arrow) does not go to the table that the actual portal record, which IS related because I see it show up on the portal row...what is not right here? Thanks! Todd **MusicalAmericaPRESUB copy.fmp12
  5. Go to Related Record in Portal broken

    I'm not sure why you say the T03aJT_JMC_musiclibrary cannot be the "one" and T03a_JMC_join_jmc|musiclibrary be the "many"...?? (I need to learn this stuff, I am just confused). T03a_JMC is a join table...joining another join table (T03_Join Music|Contacts (truncated to JMC). I am probably hopelessly confused... The way you set this up the first time does work. (see screen shot). I think I was confused that these tables needed foreign and parent keys...the music library table doesn't need a foreign key because it isn't a child to any parent table...the join table is always foreign keys because it simply links to parent keys...I had the relationship backwards, like you said, and tried to create a parent key in a join table, which made no sense. Is that what I did? Thank you! I think this solves it...NOW...I need to also relate the music library to invoices! But that comes later! I'll be back!!!! Todd
  6. Go to Related Record in Portal broken

    Yes...the arrow in the portal now goes to the correct table and layout. The way you contend is correct still has the arrow going to the join table layout, which is not where I want it to go. I am not familiar with all of this enough to see the logic error. I AM trying to do something that may either be impossible to do, or the way I have attempted to do it faulty. Contact > Market Event | Contact Join Table > Market Event (many events to many contacts) I also want the Market Event Join Table to have this relationship: Market Event Join Table > Market Event | Music List Join Table > Music List (many music pieces to many event join table records) Weird...but makes sense... Contacts are linked to events, which are email mailings that have links on them to pieces of music. I am trying to track each piece of music a contact clicks on in an email mailing (market event). The Market Event | Contact Join Table links Contacts to Marketing Events. Then, with each of these individual links, I want to see what pieces of music that contact clicked on in the email. I thought the only way to track this would be to have a portal in the Market Events | Contacts Join table...so far, all of this seemed to work fine in the RG. But then I want the usual "go to related record" in that portal in the Join Table that takes the user directly to the music piece (Music Library layout and records)...here other information about the piece of music will be kept...does this work??
  7. Go to Related Record in Portal broken

    Wow...first of all, for some odd reason I sent a file that had the wrong relationship set up...which obviously didn't work...but the "right one" didn't work either... I have to ask...the relationship I tried to set up for the "Go to Related Record" was to get to the music library table (the TO) NOT to go to the Join table with a field put on the table from the music library table. Why is your way the only way to make it work, and in all of my other "Go to Related Record" arrows in other portals I go to the table itself that has the record I want...I know this doesn't make sense... Look at the attached screen shot...why can't I go from the T03a_JMC_join_jmc|musiclibrary portal directly to T03aJT_JMC_musiclibrary with its appropriate layout (Music Library)...?? I would like to know the logic I am missing here. From a practical point of view...I need the arrow to go to the related record in the music library table with the music library layout. I think you will say "well, that isn't the table that the portal is related to"...but isn't the T03aJT JMC musiclibrary table related to the T03a JMC join jmc|musiclibrary table (which is the portal)? Sorry...I am relatively new to this... Also, it seems in other tables where I have portals I can do this sort of relationship. I need that arrow in the portal to go to the table that the music library record is actually in, not the join table that links it to the contact table... Hope this makes sense...and thank you!! Todd
  8. Go to Related Record in Portal broken

    Yes, I did some work on it, still not solving my issue, I tried to delete files and it deleted everything...I am reuploading... **MusicalAmericaPRESUB copy.fmp12
  9. mass linked records to one event

    Hey... Here is the scenario... Contact table joined to Contact|Event table joined to Event table Portal in Contact table of Event table to see all Events linked to a particular Contact Needing to link 100 (or any number) of Contacts to 1 Event in Event table Do not want to do this manually. I have all the tables and relationships set up and everything works. All I need is a way to create multiple records (joint records in Contact|Event table) in multiple Contact records so I don't have to enter them in one by one...which of course I can do. I figure this would be a script...but I don't even know where to call the script...in the Contact record? In the portal row? in the Event record? Where does the script button go? And what is the script? Once I create a found set in Contacts, how do I assign one Event to many contacts (in the found set) without having to enter the link manually. THANKS!!!!! Todd
  10. mass linked records to one event

    Thanks...Sorry about the RG...I have been using different names (such as Contacts B for clarity in describing my situation, the RG is from the actual database.) Yes, this whole structure may be the cause for these difficulties...I originally wanted staff to be part of the orchestra table, but they are independent, move around, have all different information and often times get marketed to differently. It probably would not have been a problem to have staff part of the orchestra table...but conductors definitely need their own table. They are very independent, some work for several orchestras, orchestras have many different free lance conductors. However, marketing often goes to orchestras and conductors...the SAME marketing event...such as a mailed postal card or email campaign...will be sent to the general address of the orchestra, as well as to the address (usually home) of a conductor...sometimes a conductor that has no orchestra affiliation. The events are the same..the same card or email, so I want just one Marketing table. An event (a single record) I want linked to many orchestras and/or many conductors. I have a join table between orchestras and marketing, and a different join table between conductors and the SAME marketing table. For reasons beyond my FM acumen, the orchestra/join/marketing system works (using the script Comment so kindly wrote for me). The same modified script does not work with conductors/join/marketing. FM required that I make a TO for Conductors...to relate to the join table. The script DOES work actually...all the join table records are properly created, with all the proper IDs in the proper fields. What doesn't work is the portal in Conductors...it shows ALL the join records, not just the one that is assigned to that conductor's record ID. Obviously I don't properly understand TO's. Thanks! Todd
  11. mass linked records to one event

    We track a variet of contacts...orchestras, staff and conductors. They all relate to one another (an orchestra can have many conductors, and a conductor can work for many orchestras.) However, we send the same marketing material to all contacts (or selected ones within the groups). Contract A in the example I gave is the orchestra table, Contact B are conductors. I have one event table...1 orchestra table and one conductor table. I want to link 1 event to records in the orchestra table and records in the conductor table. I know I have to do this in two separate actions with two scripts and two join tables. The relationship between the orchestras, join table, and event works fine, but when I try to do the same thing with conductors, a new join table, and the same event table, it doesn't work. It made me create another occurrence of the conductor table before it would let me relate the join table to conductors. The weird thing (to me) is that it all seems to work perfectly fine...the join records are created properly...but the portal in Contacts B shows ALL the join records...not just the ones related to that contact ID...why is that? I know you can't tell much from a screen shot...but there it is. I can send the file too but I don't know if anyone wants to dig into that!
  12. mass linked records to one event

    It works!! Yes, it would be nice to find duplicates and such...I'll have to work on that... NOW...I do have a problem. Of course my situation is more complicated than what I originally presented. Not only do I have a table of contacts to join to a table of events...but I have two other tables I want to join to the same event... Let me simplify...as I stated above... Contact A table mass link to an event in Event table. Using Join Contact A | Event....Your solution worked beautifully! Now, add Contact B table mass link to the same event in Event table. I set up the same script, with a button that says "Link this Event to Found Set in Contact B" I changed all the parameters in the script to now make the records in a new Join Table ContactB|Event. I made a new Join Table. Related it to Event, just as before, but when I tried to relate it to Contact B it said I couldn't do that and forced me to make Contact B 2...a table copy. The script works...EXCEPT...the portal in Contact B that is related to the Join Contact B | Event shows ALL of the records...NOT the related record only. Everything looks exactly the same as the Contact A script, portal, etc. which works great. The Join table shows all the correct IDs put in all the correct fields. But Contact B portal shows ALL of the records in the Join Table (for Contact B and Event) not just the one associated with the Contact B ID.So if I do a found set in Contact B of 20 people, and run the script, all 20 records (from the Join table) are included in each contacts portal. I think the problem is with the copy of the Contact B table. I changed things to reflect the #2 table wherever I could, but that didn't work either. Is there a solution to this? Thanks!! Todd
  13. mass linked records to one event

    You guys blow me away...do you all have IQs of 200+? Amazing. I have been messing around with FM for a while now and although I can come up with some pretty cool stuff, I get totally stumped with the logic in these sorts of scripts. Thank you so much Comment...I think I follow this. I am assuming that you first create the found set in Contacts in this scenario? The script is run from a button in the Event record I want to link to my Contacts found set, right? Like in my second scenario? If I don't first have a found set, then it will try to attach the event to my entire contacts database (if "SHOW ALL" is clicked)...the "Show Custom Dialogue" is where I say "You are about to link 900 Contacts with this event, proceed?" Correct? Thanks again...I'll give it a whirl...I'll be back if I flub it up!!! Todd
  14. mass linked records to one event

    Thanks! I am trying to avoid checking 900 or more names to add the event so I don't think a "picker window" would work...but it is a cool idea! The work flow isn't crucial. Off the top of my head this is how I would intuitively do it: 1. Go to Contact table and create found set through a "Find" based on criteria (selecting the contacts that the event--in this case a mass emailing--went to) 2. Go to Portal on the first record of found set and add the event. 3. Click a button somewhere (in the portal row?) and assign that same event to the rest of the found set. This might not be the best way to do this. I see there being confusion as to which event to "assign"...I know that the "Assignment" is actually creating records in the JOIN table between Contacts and Events. I don't know FM well enough to think this through, but is it possible to have a script that uses the found set of another table? It might make more sense to do this... 1. Go to Contact table and create found set. 2. Go to Event table and find record of event you want to assign and press a button that says "Assign Event to Contact Found Set" 3. Have a dialogue box that comes up saying "You are about to assign this event to 900 Contacts" (or whatever the found set in Contacts is) Then click ok and the script creates JOIN records for 900 contacts. This would be the preferred way to do this...but I don't know if a script can utilize a found set in another table...can it? THANKS Todd
  15. User Defined Date Portal Filter

    Hi... I have a portal with records that have a date field. I would like my users to be able to define a date that the portal will filter and show only records with the date the user defines. This seems simple, but I cannot figure it out. If I use a filter that uses a calculation with Get(CurrentDate) it will work, but only for the current date! So I know my relationships etc. are working...but I would like to be able to choose the date in the user interface...and have the portal search and display that particular date (NOT a date range, just one date). I have tried to perform a find, but that doesn't work (I am new to FM!) Any help? Thanks!!! Todd
  16. User Defined Date Portal Filter

    Duh...I figured it out...I think. Thanks Ryan....How about the match relationship? I had to relate the date field I wanted found in the portal set (child) to the global date field in the parent. Is that right? It would not work otherwise. My portal refreshed without any other script setting. In the portal filter I had the child date field = the parent global field. It seems to work, did I do it right?
  17. User Defined Date Portal Filter

    Thanks Webko...but where does the user enter the date he/she wants to find?
  18. Psychotherapy DB

    Hi...new to the forum...thanks! I am rather new to FM but have created solutions before...I am embarking the creation of the Psychotherapist's patient database and right off the bat I am stumped. To keep it simple, let's say I have a Patient Table, an Invoice Table, and a Sessions Table...however, my patient table will include both INDIVIDUALS and COUPLES...a couple consists of two people, with unique information for each person, name, phone, email, siblings, parents, etc. However, they will still function within the database as ONE unit...meaning they couple will receive many invoices (addressed to the joint address, or a specified email address, or both email addresses) the couple will have many sessions...but a session and invoice will be linked to only one couple, not two individuals. Should I approach this with hidden fields and just use the same table for both couples and individuals but have the unique fields for couples show up if a radio button is pressed? Or should I somehow join two individual records into one (??) or what? How would this be solved? THANKS!!!!
  19. Psychotherapy DB

    Wow LaRetta...you are absolutely right, I WAS using the pop-up when it "worked"...I thought they were the same, pop up and drop down, guess not. Jeesh. Thank you so much for the comment. I have gone through a bunch of the trainings online for FM and even the advanced training only covers about 2% of what this program can do (I am sure what I have studied has told me the difference between drop down and pop up, I just don't remember.) Thanks for your help...and everyone here. I am sure I will have many more questions, and hopefully one day I'll be able to contribute!
  20. Psychotherapy DB

    Thanks again Fitch. I will keep learning. One thing I DON'T like about this solution is the field where I have the pull down (using Name_Full as the secondary field in the value list) puts the ClientID in the field instead of the person's full name. Several of the other "versions" I had come up with, that didn't actually "work", did not do this...and I am confused as to why it doesn't put the Name_Full field contents in the field when I choose it from the pull down value list...instead it puts the first field choice (ClientID) in the field...that's why I had to create the partner field so we could see the person's name rather than just an ID number...I'm sure I am doing something wrong here, but of course have no idea what it is... Thanks!
  21. Psychotherapy DB

    Thanks Fitch...I should have cleaned up the table titles, didn't mean to leave it Customers 2...in fact the original table needs to be renamed to client and the TO renamed client_PARTNER or something like that. I'm not quite sure if I understand the "TO the layout is based on". I do think I understand context...but that's what I thought was strange about this layout. Not all fields are from the original client table (Customer). And there is no portal for fields from the TO. Still...this thing works the way I want...you say it would work either way...well, when I tried to switch around the relationship it did not work at all. So I am still confused. Like which table is now the parent and which is the child? Or is that designation irrelevant? Should I change the field names? Thanks again for taking the time...I really do appreciate it...I've included an updated file if anyone wants to look at it. spouse1.fmp12
  22. Psychotherapy DB

    You guys are geniuses...I feel like a caveman with most of this stuff... I have another one, which is more on topic... I have been trying to do this self-join to select a partner (spouse) from my contacts table. I have followed all the rules for setting this up but it simply would not work. I tried this setup so many ways I lost count. I finally got it to work. In fact, it works exactly as I would like it to work...however, my relationship to the TO is screwy. In fact, according to all that I've read, it is backwards... I have attached the little database..."Partner" fields (to choose spouse) are hidden unless "couple" is selected in the "type" field. Even though this works I am concerned because I am afraid when I start building other relationships to the original table it is going to get all screwed up...is it? Everything I've read says to relate the original table clientIDpk to the partnerIDfk in the TO...but that didn't work at all. When I reversed it, relating the original table partnerIDfk with the TO clientIDpk it worked...why? Thank you so much!!!! spouse1.fmp12
  23. Psychotherapy DB

    Me again...not sure where to post this, but a general question about populating fields. I am planning on using an iCal plugin that will pull in iCal events. These events are appointments with clients, who are in the solution "clients" table. My question...how can I link an event title, such as "Todd Hayen - individual therapy" to my "Todd Hayen" record already in my client table? I want information stored in that record to populate certain fields, but since the actual event title is not an exact match to the name in the "name field" in the client table I don't know how to do this. All of the events pulled in from iCal have the full name (which could be matched to a record in the client table) with a dash ( - ) following the name... Thanks! Todd
  24. Psychotherapy DB

    In Ontario the client pays us for our services and then gets reimbursed when they receive our receipt...so the "invoice" we send them they submit for the reimbursement. So no, the database does not handle "insurance billing"...just that the invoices have to be made out a certain way and have specific information on it.
  25. Psychotherapy DB

    Yes, it IS unusual to bill two recipients...and drives us crazy. It is due to insurance...sometimes both spouses in a couple have different insurance coverage as they each work for a different employer...sometimes they need invoices to be only in one name, sometimes the insurance carrier requires both names, sometimes it goes back and forth from session to session. It is crazy. I need to be able to send them to both email addresses too...or one or the other. Drives us nuts. These are all great suggestions and when I know more about constructing the DB I'll be able to implement them. If anyone has anything else to suggest, please do. I would rather create an ERD that works than experiment in the middle of construction to see what works...but I may have to do that. This couples problem makes the whole thing crazy...there isn't a CRM out there that I have found (other than custom made DB's for therapists...but even those are rare) that intelligently deal with this issue. I would like to find a solution that works elegantly. Thanks again to everyone!!
×