Relationships

Thanks to Reed's excellent many-to-many example (see attached), I was able to create a project that allows a user to relate a number of tables to my master table using the standard: TableA-=<TableAB>=-TableB approach. I now need to enable the user, while sitting in TableA's layout, to delete the relation in TableAB without deleting my perfectly good value in TableB (which is likely related to a bunch of records in TableA) Going to the related record in TableAB doesn't work because it seems to match only on the TableA_id and not both the TableA_id and TableB_id. Any thoughts on how to do this? I smell a script, but I am not certain how to approach it.…

I have 2 tables in my file. Table1 has fields like "Title", "Creator", "Type" and "Description". Table2 has 2 fields, "fieldName" and "fieldDescription". The values in "fieldName" match the Field Names in Table1. What I want to happen is when the cursor moves into a field in Table1, the description that goes along with that field is displayed in "Description". So, I want to look for the record in Table2 that has the name of the active field in Table1 and put the value in "fieldDescription" in Table1's "Description" field. Why don't I just use the FieldComment() function? Because there is more than one line of text in the descriptions. Sorry if this is a…

Let me admit up front - I am new. Please be kind. I have been hunting the forums and haven't found a solid description on dealing with a basic many-to-many relationship in version 7, so I fall on you, the expert to PLEASE lend a hand. TableA has a serial as the PK and a text field. TableB has a serial as the PK and a text field. TableAB has two number fields, the PKs from TableA and TableB So we have A -=< AB >=- B in the relationships graph. I want to add a record in TableA, type something in the textfield and select one or more records in TableB that relate. When adding to TableA, I need to see the textfield from all records in TableB. …

Hoping to gather some insight on how to improve my relationship... I have 2 files (1 - Timesheet , 2 - Cost Codes). *Please see attached documents* I am experiencing an extreme slow down when I run my script "Calculate Totals and Go To Summary"... can anyone provide me with a more efficient way to do what I am trying to do? As well, adding codes is a little bit painful. Thanks much in advance! Cost Codes.zip

Forgive my ignorance, I know this must be a basic question -- I've mucked around the relationship forum and haven't found an answer. 1. I'm making a bilingual dictionary database (English and Ojibwe, an Amerindian language) 2. Each word can have one or more senses. I accomplish this with two tables, a word table and a sense table -- the sense table shares an identifier field with the word table. 3. Each sense can have multiple keywords associated with it. For example, the Ojibwe word minogaade means 's/he has cute legs' -- key words associated with the sense 's/he has cute legs' might be 'cute' and 'leg.' A sense can have any number of key words. I accomplish this w…

Ok, folks, this is a real newbie question, but I need to ask it. I'm working on a solution that will help the tech support guy (me) track our software licenses, where they're installed, etc. I want to keep track of what software I have on hand, how many seats have been ordered for each, how many machines have a particular app installed, etc. I created one db called "software." It stores info like app name, manufacturer, license, # seats total, etc. A record is a single instance of an app (photoshop or office or acrobat, etc). I also created a db "employees" that lists employees, and what apps they have installed. A single record is an employee (Joe, Jane,…

Can anyone tell me how I can take an existing report (Letter) and make it open and paste into an email so I can send it. Thank you

Struggling with new learning curve with version 7. I have a complex file with tons of fields and scripts. I would like to populate a related file with data from just 2 of the fields. When a new record is created in CmplxFile1, i want a new record created in simplefile2, automatically with the exact data from the two fields populating the records in simplefile2. The new fm7 table is confusing me and my simple file relationships aren't working! Suggestions?

I'm creating a training diary solution. I have a layout showing statistics for the training. It contains maybe 30 fields looking like this: sum(Relation1::sport1) next field sum(Relation1::sport2) and so on ..... The relation looks like this: field 1 (activityuser & " " & year(activitydate) field 2 (globloginuser & " " & globyear) On the layout the user can choose year number. But I want the user to be able to choose if he shall get stats from a year, month, week or between two dates just checking a checkbox and filling a globalfield. All on the same layout. Can I get the relation to change depending on what the user chooses?…

Please forgive me if this has been posted already. I did a search and was unable to find the information. If anyone could direct me to a previous post or an example I would be most appreciative. Also, if this is in the wrong forum, I also apologize. The situation is as follows. I am trying to build a quoting system for my office. I have a list of shelves and a list of options that go along with those shelves. I want to assign a list of available options for a given shelf from the master list of options. The way I have it set up now is with the shelves in a dropdown and a list of all options in a portal. I want to be able to click on items in the option list a…

Something has gone south in my relationships and I'm at a loss as to where I should go to fix this. Two files use basically the same relationships and when I open either, I might as well go jog around the building for as long as it takes my system to open everything up. Once open, performance is fine until I go to define relationships.. then it takes forever for that to finish opening. But the real kicker is, it's only on my machine. The rest of the users have normal functioning still. I've cleared temporary files and still have had no luck. Anyone out there seen this before and know what to look for?

My question is how to create the relationships for Category? In my Inventory have many Product ID but diffent Category. So I use define values list(use values from field) relationships to Inventory Product ID. So every time I select the field will show all Product ID, now I try to use Category to control the Product ID. Look like when I select the select Category only show the category Product ID at the field. Any idea to do it??? Pls. Advice.....

Dear friends, I usually cope well with my relational structures but I have come to a complete halt over this one. I have A,B and C tables, each with a many-to-many relationship to the other 2. The report output requires reporting from a combination of all 3 sets of many-to-many tables ie AB, AC and BC. To make matters worse, A is itself a many-to-many result of files X and Y. However, I look at this, it looks clunky. Data normalisation seems to require that I set up AB,AC and BC tables in order to maintain data integrity but to get my reports, I first need data from all 3 tables to be consolidated in one table, say table Z. Am I on the right track? Do I cr…

Hi, I need some help, any would be appreciated, I wish to display a phone number based on what someone selects in a drop down list. The complication is...this. (It can be any one of a series of numbers stored in 5 different fields. times the number of records.) I start with a company in a drop down list once that is selected you move to the second drop down list and select the staff member one of 5 possible for that company. this is the list I need to use to pick the mobile phone number. How I do the above check out the pics attached. it may give you an idea (I also use a calculation fields to combine the 5 staff members together for the second drop…

I am but a novice pushing my envelope of expertise, any help will be gratefully accepted. I am using a series of Global fields to control access to a separate data file containing images. I also have a separate customer database containing valid user IDs. I was hoping to provide access to the images via another "Viewer" database that contains no actual data but uses information extracted from the function Get(AccountName) assigned to a global field that is linked to the customer database, this relationship allows the Customer's Account Number to be assigned to another global field that in turn provides a link to the image database. Using a series of single instance …

Very Simply, Two databases. One for movies one for theaters. A movie is playing at multiple theaters (a theater hosts multiple movies, for that matter, but I'll keep my question "bite-sized"). I want a field to list these theaters (theater 1, theater 2, etc). I have a list of the theaters in a portal, but I need to format an export that is a comma delimited list. Anyway, I stripped down my examples to the bare minimum, I hope that someone can help, thanks in advance. FileMaker Version: 5 Platform: Mac OS 9

I hope this the right forum for this question, if it isn't I am sorry in advance, so here it goes. I have DB called invoice. In this DB i have separate fields for Caller name, company, address, city, state,zip. I created a second DB called contact. I then created a relation ship with both these DB using a field called key. In both those fields I put the number 1 in the field. So far I think I am creating a relationship correctly. What I want to do in the invoice DB is when I put in the caller name I want the DB to go to the contact DB lookup the company, address, city, state, & zip and paste it the invoice DB. So each time when I put the called name it the address w…

I'm starting on a solution to schedule the use of resources in our lab. Ive attached a file which attempts to use relationships and a calculation field to detect scheduling conflicts. This appears to work, but I'm looking for input on whether this is the best approach to use. Also, I'm looking for a way to use field validation to disallow commiting a record if it conflicts with an existing record. Any ideas on how to validate this w/o the use of a script? FileMaker Version: Dev 7 Platform: Mac OS X Panther schedule.zip

I'm starting to build a new Enrollment system in one FM7 file (as an exercise at this point), and I love how easy it is to view related data from several entities away. But now that I can do this, I seem to be stuck on how to create filtered portals to thin this data down. The problem is that there is no direct relationship between the file I have the portal in and the data I'm viewing through the portal, it's joined through a series of relationships. Specifically, what I'm trying to do is find a way to filter the related child Enrollments by School Year (from the Staff layout). This way for any given staff member, I can just look at the children enrolled in a…

(maybe you read the 2nd post, my problem is described in a better way there...i guess) Hello, sorry to come up with a question like this...i'm new to database and the only thing i knew quite well was Claris Works 3.0 i'm working on a database for a TRAVEL AGENCY office. starting with the CUSTOMERS: i'll have a database with eacht CUSTOMER having a record. now: let's say i have a FAMILY. i suppose i can store POSTAL ADDRESSES in a different list and relate ADDRESSES<==>PEOPLE with an serial number. i managed to do this. now: [color:"red"] how can I SELECT say 3 PEOPLE records (having all the same address because they are a family) and then work…

Hey there, I'm trying to build a new FM7 Costume Breakdown from and old FM5 one. In the old I had five differente "Files" with all the relationships working pretty much as I liked. Now I have one "File" with five "Tables" and I cant get the structure like before. Here are my "Tables" Chrono = This is where I input the data for the Scenes and add the Roles to each Scene. Roles = A list of all the Roles and the scenes that they are in. This is also where I assign the Costume #. Contunity = This is a printable list of all the Roles and Costume changes by Scene.One costume per page Line Up = A chart showing what scenes are shooting on what day. I would…

I need to know if there is some way to use a realtionship field in a report, without using a cell. I only want to use <<>> so the rest of the report letter conforms to it. Example: Company Name (Pull Down Box) when selected it looks up :address, ::city etc.. I want to use ::address in a report like <<address>> but it will not look it up is there a way?? FileMaker Version: 6 Platform: Windows 2000

I created an articel and a position file. Now in the bill database I created a portal using fields from the position file. It contains the article number and the name of the article. (beside others) When typing in the article number the article name is shown automatically. But when typing in the name of article the article number does not show up. How to make this portal work that the person can also type in the name of the article? FileMaker Version: 4 Platform: Mac OS 9

I have a file with 2 tables and have managed to select all the data from all the "Sample No." field in Table 1 and list them as a lookup in the "Sample No. " fields in Table 2 via a self lookup and concatenate function as suggested by CyborgSam which worked very well. I have 2 tables: Table 1 "Sample No. 1" "Sample 1 Details" "Sample No. 2" "Sample 2 Details" etc to 10 sets Table 2 "Sample No. 1" - value list based on the concatenation of the values in the Sample No. fields in table 1. "Sample 1 Details" "Sample No. 2" "Sample 2 Details" etc to 4 sets Say the value in "Sample No.3" in Table 2 is from the "Sample No.1" field of Tabl…

Hi, this might be a very stupid question but I'm not a FMP wizard. Thanks for your understanding. I want to do a pop-up that "shows" certain values but store others. For example, the user choose between A, B and C but the value stored is 1, 2 or 3. Pretty much like it does in html: <option value=1>A</option> and so on. The reason I want to do that is because I have a CDs database and a Bands database. Each band has a unique ID and a name. In the CD database I want a pop-up to choose the band by name, but it's the ID I want to store, so that if I made a spelling mistake in the Bands database and correct it, the relevant CDs now shows the corrected name. …

hello. i have been assigned the task of creating a simple relational database that shows details of employees of a building company, with their wages, job site details, and client details also. My transaction file (named employees/jobs) is for each emlployee working on one day, and how many hours they did that day will be recorded. The problem is that i cant make the "hourly wage" link through to the transaction file. The separate "wage" file contains the hourly pay for each job type (eg plumber makes 19.00 an hour) and it links into the employee file just fine, but then i cant get the wage to come up automatically into the transaction file. help?!?!?! sorry my…

< traditional newbie disclaimer > i've had a hell of a time finding a solution to my problem relating two databases: one database (related file) is a list (~250 names) of realtor info (first name, last name, and branch office). the other db (master file) is layed out for me to type in work orders sent in by the realtors' assistants (realtor first, realtor last, assistant name, office, etc.) with requests for different photography requests . however, some branches have realtors with the same last name. furthermore, the request forms are rarely filled out completely. usual info: assistant first name, realtor last name. so, how do i setup my relationship(…

I would like to place all the layouts in a separate file, apart from the data in the tables. The reason I want this is that it makes life a lot easier when I want to modify the man machine interface (layouts) in a new file and just swap that modified file with the one currently in use (without having to import all the data). Then I can modify while the FM application is being used. I hope sombody knows how to do this; I can't find anything like this in the user guide or the examples... Thanks, Wimek FileMaker Version: 7 Platform: Mac OS X Panther

I am in the throws of setting up a new database with the current tables Table 1 Engineer Sample Number Table 2 Engineer Contract Number Sample Number 10 no. repetitions Sample Details 10 no. repetitions Results 10 no. repetitions Table 3 Contract Number Sample Number 4 no. repetitions Results 4 no. repetitions I have managed to use the value lists and relationships to link all the data together so that only the sample numbers allocated to the engineer in Table 1 can be inserted into the Sample Number field in table 2. Also only the sample number allocated to a certain contract in table 2 can be inserted into the sample number field…

i constantly hear the comment filemaker is flexible, it allows you to correct mistakes and reconfigure tables and relationships as your needs evolve...okay, just how flexible IS it? because i also hear be careful, get it right the first time when you're planning out your database structure because if you don't get the relationships right you'll have a mess you can never fully fix. so, where does this leave me, really? CAN relationships be fixed, if you put together an ineffective or inefficient logical flow and disover later you simply cannot operate the searches you need with the structure you have? how much CAN you change once you've set up fields and tables? how hard i…

ok this is my first attempt at a real database, and all my books are about five and six so its sort of frustrating to try to figure out 7...any help would be appeciated. here's the prob: we have clients, they serve families of subclients themselves, we need to track each kid in each family for times being served, times vary depending on day of week and days may vary by kid even within families, eligibilty starts and may end at differetn dates for each kid, we need to track kids times and days of eligibility and start and end dates, and link it to the original clients info to produce a monthly report updating each kid's status, dynamic things like the kid aging out of the …

Hi All, I'm tyring to find an elegant way to add line items via an X Y type grid. Style No: 101 . Sm Med Lg Blue 10 20 30 Red 45 23 18 Green 23 56 20 _______________________ Qty 78 99 68 This should produce line items; 101 blue sm 10 101 red sm 45 101 green sm 23 101 blue med 20 ...........etc Please find attached one method which works. The final version needs to have up to 8 sizes and 6 colours, maybe more. Sometimes there are only say, two colours / one size. Colours, sizes and quantities need to be dynamic or appear so. Any thoughts? FileMa…

I have two databases. One is a database of software types and serial numbers we own. The other is a database of computers we own. The computer database creates a value list from the serial numbers in the software database, to assign serial numbers to computers. How can I make the software database click a checkbox to show the serial number was used in the computer database? This would let me quickly scan for available serial numbers. Thanks! FileMaker Version: 5 Platform: Mac OS 9

Hi, if you have a one to many relationsship and you want to display the related fields with a lookup, it will only show you the first related field although there might be more. If you use a portal you can see them all. But is there a way to display a specific related field out of many without displaying all others? E.g. something like a filtering in a portal?!?! Joern FileMaker Version: Dev 7 Platform: Mac OS X Panther

I have a database with 3 fields on Image Page and Status. There will be lots of pages that are the same for example 2/3, so I need all the 2/3 pages to have the same status. So if someone selects that pages 2/3 are finished, it will show the status of all pages 2/3 as finished The status field is a pop up list. I have attached a samll file of the database to show you what I mean. Thanks for any help. Chris. Cat_test.zip

Let me explain my problem the best I can. I have a product database. This database has two fields SKU and Description. Some of the SKUs have components and in some cases those components are actually already in the same database. e.g. PT001 consists of FD01 & FD02. FD01 is in the same database but FD02 is not in any and it doesnt have to be. What I want to achieve... On the screen of PT001 i want to have list of components which I would be able to chosse from the drop down list (i know how to make this). Once I select a component SKU from a list (FD01) I would like to have it's description automatically entered in the next field. HOW to make th…

I'm new to databases. I've modified the given "contacts" file just a little. Now in another db I'd like to be able to view (not lookup) the info from contact by entering one of the fields (most likely last name or phone #) and having all of the info brought into view in the new records. I began trying to set-up a relationship but it seemed to not be correct. No doubt this is not a new question but I was unable to find an overview or sample step-by-step as I browsed. Thanks for any help Kirsh FileMaker Version: 6 Platform: Mac OS X Panther

The one thing I've found about this forum is "never say never" ... somebody somewhere seems to have a suggestion. I'm wondering if anybody has such a suggestion for this instance. Simply ... is there ANY way of forming a relationship to a calculation field? In this instance, a calculation which forms a date (i.e. some records in my project manager are fixed ... some are set to "today" so that they are returned until the project is finished, where upon the date is set to the finished date) I'd appreciate any suggestions Warmest Giles FileMaker Version: Dev 6 Platform: Windows XP

I'm having trouble wrapping my head around the TOGs in FMP7 and I think once I have a answer to this question I will understand it much better... In the FileMaker Business Tracker it is easy to see in the relationship graph a basic path from contacts to invoices to line items to products. But what do you do if you have, say, a quotes database that is basically the same as the invoices database. Intuitively I'd split the path before the invoices/quotes and bring it back together afterwards, but that won't work cause then you'd have a loop. So I guess there are a couple of options (ignore the dots)... . . . . . . . / Invoices - Line Items - Products C…

I am trying to find the best way to organize my data structure. Any help would be greatly appreciated. Here are the basics.I have a typical product database with basic products that I tie into inventory, etc. I also include different variations of those products in multiple different packages. I want these to be connected to an order table. What is the best way to organize this? Should I just add a different check box for each package to products table that says if they are included in that package? This would be connected to the order table. or Should I make a package table that has a relationship to the products table? Then connect both to the orde…

I have a relational database with three charts. I need to parse the "name" field of the "client" chart and use it to automatically generate a series of records (invoices) in another chart, one invoice for each name in the "client" chart. I have this working manually: if I enter a client's name in the "invoice" layout, the invoice populates automatically with the rest of the client information, including billable time and such. I just want to script this invoice generating procedure so that the user doesn't have to manually create a new record and choose each client's name in turn from a client name popup. (For clients with no billable time, I figure if I get this scripted…

For my business, I have a lot of people who refer clients to me for credit assistance. When a person signs up, we note who referred them. I want to be able to look at any client, and not only see who referred them, I want to know about the people who referred before them. Example, John is a Loan officer, and refers Bob. Bob then refers Chris. I want to be able to look at Chris, and see Bob, and John, and some details about them. Since all of our clients are asked to refer business, this would have to be some kind of relationship to show the previous 2 referers in the line. Where it gets complicated is, if the person who referred them is not a client of ours, t…

I seem to have run into a little problem (bug?) in FM7 with related records being deleted. Or, perhaps I'm just doing something wrong? :-) I have a single file with many tables. One table, Students, keeps track of student information. Another table, Student Interests, is a list of possible student interests. Student Interests JOIN joins Students and Student Interests. Students and Student Interests are also joined directly. A global in the Students table contains a "1" and each "active" Student Interest receives a "1" via calculation in the Student Interests table. This global field and ActiveFlag field are related. This shows a list of all available Stu…

I started out to whip up a personal-use database that would track computer network wall ports, network devices (mainly switches, potentially hubs, routers, and bridges) attached to them, and hosts (computers, network printers, etc.) My goals are: to easily find what is connected to what for network troubleshooting, host identification (when I'm given a report of an infected computer off a wall port, and where I may not have the MAC address yet in a database); and for bandwith planning. My initial thinking was that each wall port would connect to one uplink of a switch, each switch would have 0, 1, or many active downlink ports, and each active downlink port would have 1 h…

I found out how to dp basic relationship stuff. Took awhile but it workks. Now I need to be able to pull databases from a million different database files. into the portal and or a basic non portal relationship file. Think of it like a master database for all and every recond alive. Is that possible ? When I tried it with the portal it will olny see the data from one file as far as I can see it. I have the demo of FM7 Will that help any ? I know that the biggest new feature is the use of more than one tab;e but I am not sure what good that will do. FileMaker Version: 6 Platform: Mac OS X Panther

I am trying to reduce a relationship from an inherited database. I think I have an idea, but I wanted to see what others thought. I have table A, with two fields. The fields are E and S [A:E and A:S]. Field E has redundant names. Field S is dependent on field E, meaning that I can't have S without E. As it stands now, each record is composed of a single E and a single S. Therefore, i could say that the combination of E and S give me a primary key. Would it be wise to break the E field into its own table (forcing a new Primiary key) and creating new tables for S. I assume that this would mean that I would have to make new relationships for S. But, I would re…

I would like to link 2 diffent data base files using a button. Is this possible? Thanks for your help. FileMaker Version: 6 Platform: Mac OS X Panther

I've recently been having problems with established relationships - it has only been within the last couple of weeks, which is why I'm puzzled! I have a multi-file solution with about a dozen separate files, joined in various ways. Lately, Filemaker can't "find" files in a specified relationship, and when it asks you to open the file it claims that the file is "single user or the host can't be found on the network". All of the files are multi-user, and I currently don't have passwords on any of them as I'm still in the development stage. It has been happening specifically with about 4 of the files. The solution rests on a remote hard drive, which everyone in …

I've got 2 files I've created a relationship between. The main file has one look-up field, a phone number which is auto-entered when a name is entered in the main file. I would like to add a look-up for an e-mail address as well, but this has not been an existing field. I've added a new e-mail field (text) to both files, created the look-up, but I can only get the e-mail field to populate in my main file if I enter the name. So, the problem, friends, is that existing records don't execute the auto-enter for the new field. How do I make this work globally/automatically without going through each record and updating the names one at a time? (I hope that made sense) …

Beginner here - I've created a lookup between two files that both contain repeating fields. When a catalog# is entered all I want is the related file info for that catalog# to come up. Instead I'm getting only the first field from each record. It's not recognizing each field as unique. Is there a way to do this within what I have already created? Version: v6.x Platform: Windows 2000

I have a tiny question: What limit should you experts put for how many fields that one file should contain without getting to big and slow? In one of my files (1 of 7) I'm up to maybe 120 now (moving towards 250? before I'm done), and it's practical for me to have them in the same file, even though in most cases only 30 of them are filled in every record. There are also many calculation fields and I thought it would be a good idea to have them in the same file as the fields that they are based on, am I right? FileMaker Version: 6 Platform: Windows 2000

Hello. I understand what the Primiry Key is and can assume what is the Foreign Key. But, could someone explain what are the Alternate Keys, Compound Keys and Multi Line Keys in FileMaker relational database design? Thank you very much! FileMaker Version: 6 Platform: Windows 2000

In only a few records of a file the font must be one of the Central European fonts, e.g., HelveticaCE. This record is accessed in a portal of a third file. (There is a join file between the first and third files.) I can't find a simple way for the proper font to show in the third file. FileMaker Version: 6 Platform: Mac OS X Panther

Hi all, Can anyone tell me how to access related data from a third file. I have a file A which is related to file B. And file B is related to file C. Now, I have to access information stored in file A from file C. How can I do that without having to create 'calculation fields' in file B and then using those fields as related fields in file C ? Thanks in advance ! Version: Developer v6 Platform: Windows XP

Hi, I have two files which have relation... and only one layout for taking thus values... but the validations are not preforming for the relational db fields..... how can this will be work.... the below i am giving an example... x.fm5 is a one file and y.fm5 is alother.... and i am creationg a layout which will be available in x.fm5.... there i placed the y.fm5 fields to take that values... this fields are having validations but thus validations are not happening why ? FileMaker Version: 6 Platform: Windows 2000

Let's set up a self-join relationship on field A, indexed, text. record#1: 111 record#2: 1.11 record#3: 11.1 record#4: AA record#5: A.A now, i've found that record#1 is related with both #2 and #3 (and itself) (record #4 is not related to #5) this is very annoying for me because field A stores an identifier (KEY) from an imported file. And the relationship doesn't work well because dots between numbers are ignored. Why? Are there other charaters ignored when the field is indexed? How would you work this around? Thanks in advance, Paolo

Is it possible to set up a relationship that will match two empty fields as being equivalent? (Seems simple enough but I have tried calculation results equal to "" as well as global text empty fields, and many other ways, but nothing seems to work...) Version: 7

Howdy. Is it possible to only do a relookup of the CURRENT record, not all of the records in a layout. And, if so, how!? Version: v6.x Platform: Windows XP

I have 3 DBs: A is Attendees, B is Activities, and C is an Attendee/Activity Join that allows me to see which activities an attendee has chosen and vice versa. I had to do a strange workaround in A (Attendees) because some of the overall attendees are actually guests of the primary attendees (i.e. someone brings their wife and children on a trip) - in essence, I use a script to tag the "Guest" record with the unique record ID of the primary attendee, allowing me to establish a relationship that shows each guest of attendee x, while still having a complete record for each individual in one file. My problem: both attendees and their guests participate in a number of a…

Folks, I'm trying to figure out why creating a new record in the portal window doesn't automatically increment my serial number field. I set up the child db to have a serial field that increments by 1 and can't be modified, yet, when I create a new record in the portal. I get another record with the same Serial! What am I doing wrong?

My "Define Relationship" button is greyed out and I can't access it (I can't access a few other things as well - ie Scriptmaker) - is the file locked? If so, how do I go about unlocking it and viewing my relationships? Thanks so much in advance! Mike Version: v5.x Platform: Windows XP

i have four databases at the moment, 'users', 'dvds', 'lists' and 'orders'. in 'orders' there is a field called 'amount paid' and a field called 'dvd chosen' where i will put the name of a dvd e.g pirates of the caribbean. the 'dvd chosen' field has a relationship going from it to the 'dvds' database where there will be a file with all the information on pirates of the caribbean including cost. can i make it so that in my 'amount paid' field in the 'orders' database, i can make it look at the 'dvd chosen' field, see that it is pirates of the caribbean then look in the 'dvds' database, look at the pirates of the caribbean info page, find the cost and then display it? …

I need a set of files that track pupil attendance for a teacher to use. It's got me stumped. Ideally, with a student's record, a portal will show the days of a particular week, and the teacher can simply check a "Yes" box to indicate that the student was present that day. Any suggestions? What group of related files would work best? I figure a Students file, a Days file (one record for each day), and something else. It's the something else that I'm bogged down with. Steve Version: v5.x Platform: Mac OS X Panther

I have not set up relational databases in FM before and am now trying to. I find the instructions less than helpful. So, I figured someone here has to have the answer I am looking for. I have a database (dataX) that simply has two fields: Team Name & Coaches Name. In a seperate database (dataY), I have created a dynamic value list of the "Team Names" from dataX. What i am trying to setup is the following. In dataY, when the Team Name is selected from the pull down menu, I want the Coaches field to be populated from the corresponding "Team Name" in dataX. Should be simple enough, but I have not yet been able to make this work... Any input would be very…

I hope someone can point me in the right direction because i am going mad here. I am working on a database for entering invoices. Each invoice (record) has an ID and fields for entering job codes and price, and then the total is shown at the bottom. So typical records would look like this: ----- ID: X1 CODE PRICE c 100 d 200 a 100 c 50 c 50 TOTAL : 500 ---- ID: X2 CODE PRICE c 100 d 200 c 20 b 50 TOTAL: 380 ------ There are 2 db files. The master one holds each "invoice" and ID and sho…

I have a database I a rewriting in v7, which is allowing me to eliminate many intermediate databases and scripts due to the ability to do less than/greater than relationships. However, I have a many to many complex relationship to the class database (eligible classes) that I want to invalidate the user if their record is in another many to many database (enrolled classes). The way I was thinking of doing this is to place a record for each studentID that is in enrolled classes into a repeating field in the class database, then adding another line to the eligible classes relation that eliminates that class if the student is listed. I would like to do this on the fly…

I have 2 related databases that I use to allow people to sign up for weekend activities at my school. Signups is the database for public use. Each signup is one record, containing the person's name, and the activity they want to attend (selected from a dropdown menu.) Events is the admin database that has one activity per record, containing the event name, date, capacity, etc. There is also a portal to Signups that lists the names of those who have signed up. Here's my question: when a user goes into Signups, selects the activity they want, and click "Sign up" how can I have a script check to see that the maximum capacity for that activity hasn't been exceeded? Versi…

I have a system with 4 tables: user, manager, account and a user_account join table. I have two selection portals with type ahead filters (multi-keys in the related files). One will show accounts filtered by the account number, and the other will show accounts filtered by the managers last name. This is possible even though the manager_last_name field is not in the accounts table by setting up the relationships gMgr_filter=mgr_key=mgr_id in the user, mgr, and account tables respectively. I would like to combine the two portals into one and filter by both criteria, but I am running into trouble since the two multi-key fields are not in the same table. The fi…

Hello all, longtime lurker here, I can usually find the answers to my questions by doing a quick search here but this one has me stumped. I

I have been racking my head over this and the best way to do this. I need to link two databases (contact, invoice) and transfer information from two fields in the contact database over to the invoice database. Version: v6.x Platform: Windows 2000

Hello. Here's what I've done. I work for a residential school. We have, among others, a student info database ("Students") and a database of parents/school contacts/emergency contacts, etc ("Contacts.") Each record in Students is given our ID number, called "MI#." Likewise with each record in "Contacts," so logically MI# is the key field between the two. Now here's my question. I want to be able to display some of the info in Contacts in Students, namely the names and phone #'s of the related records. So, I define a relationship between the two using MI# as the key field. In Students, I place the related field "nameFirst" which lives in Contacts, and th…

Arrg, I just lost a long post because of a browser crash I've been trying to implement an OO-like product catalog and inventory system with FM7 over the weekend. I've come up with two different methods, which both have their pros and cons. Unfortunately the cons are pretty serious, or at least annoying and inconvenient. I'm hoping to bounce these ideas off people here to see if a good solution can be created. Maybe some of the FM geniuses here can recommend some tricks to work around the flaws. Method 1: Model all objects with 2 tables. This is a technique I've used with a lot of SQL/Java web apps. There's one table called Objects, or in this case Prod…

We use several databases hostsd on a FileMaker server for job tracking and billing. From time to time I need to make changes to the various databases. Minor changes can be made while users are logged into the db's. More involved changes generally require all users to be logged out of all the db's so I can get access to all the field definitions, relationships, etc. It's not really feasible to have everyone log out of all db's for an extended period of time during business hours. Due to that, most major changes have to be done on weekends. To get around this, my thinking is to leave the live db's running on the server and copy the set of db's locally to a machine runn…

I want to link these 2 db. They both have title fields, they both have author fields, ect... There are a couple fields that one db has that the other doesnt of course. i want the "HBtemplate" db to be the primary db and when i add new records or import new records the "RWtemplate" db adds those same records to it and puts the field values in the corresponding fields. How would i do that? Database.zip

I have files set up like this: 1. Invoices 2. Invoice line items (sales) 3. Inventory An invoice can contain multiple line items, with the possibility of one particular inventory item appearing more than once. Invoices are related to invoice line items via key field Invoice #. Invoice shows line items via a portal. I want to be able to see a summary on each invoice of items invoiced and quantities summed by item. E.g.: Here are the sales line items on an invoice: item 1 - qty. 6 item 2 - qty. 3 item 3 - qty. 5 item 1 - qty. 4 item 2 - qty. 2 I want to be able to see something that shows these results: item 1 - qty. 10 …

I have two db files, one for Invoices, one for Products. In the invoice file I have a repeating field (Item No's) formatted as a pop-up list of related values from the product db field "Item No's". In the Product db I have 3 fields for 3 different price breaks for each record. I am trying to make a calculation in the Invoice file that will determine the correct price break based on the qty entered for each product listed in the repetition. Any calculation I make only works for the first line in the repetition, every line below will get the same pricing data from the first repetitions record even if it is a different record. I've tried using the extend function with no s…

Hi guys, I urgently need to have this problem solved. I have a 2 files, one that keep track of the resellers and the other stores the salesperson record. The records are linked by unique serial number, after the admin enter the reseller details, admin will have to assign a new unique serial number for the salesperson as well. The serial number for the saleperson looks like this : 0001-001 ->companyA salesman 1 0001-002 ->companyA salesman 2 0002-001 ->companyB salesman 1 0002-002 ->companyB salesman 2 0002-003 ->companyB salesman 3 What i have to do now is for every reseller, when i add a new salesperson, the salesperson…

I have come across something unexpected and am baffled. I thought you might help me find what is happening. I attach two files: Quote_Costs.fp5 and Quote_Test.fp5. Quote_Costs.fp5 in List view lists several records with the keyword in Cost_Type field. The keyword is either A or B. Quote_Test.fp5 has two key fields Cost_A and Cost_B and shows corresponding related external Description fields in two portals. Two local Description fields are also available with a Pop_up menu that is related to the Description values in Quote_Costs.fp5. For left hand portal Cost_A = 'A' and the relationship displays corresponding values in its portal. The same values…

I've always found good advice here... so I'm giving it a shot! In our department we have a few hundred relational files all hosted on one of two NT servers, and "collapsed" in the hosted list under a handful of front-end opener files with descriptive names. We have about 100 clients using both Windows and Macs (v 5.5). In the past, all clients who opened the Hosts dialog were presented with the complete listing of these files, viewing both servers. Recently, my company has undergone a complete IP restructuring, which has split my FMP user base into two separate subnets (this cannot be changed). This means that users who are on the SAME subnet as the servers (mys…

I have files for Dates, Contracts and Vendors. What's the best way to relate Dates and Contacts? Currently, Dates have Contracts and Contracts have Vendors. I have a calculation field that places Vendors in Dates, but it can't be indexed, so I am using a set field script, which of course has to be activated. A lookup would also have to be triggered with a script. I will need to track, from within the vendor file, much of the data in the Dates file which is not contract related. I have toyed with the idea of eliminating the Contract File and loading up the Dates file, which although it will work in this specific solution does not seem like the right way to do it.…

Here is my problem. I want to display only the current school year information from a related file. My key field is defined as a calculation "schoolID&Year" this is the same in both the main file and the related file. Now I've tempararyly added a field to the record view that has the current year value. I'm planning on making it a global/preference type of field. When I change the current year field my portals display the related records for the correct year. Now I was thinking I would like the user to be able to update/create records in the related file as necessary. (The related file contains the dates as to when the school has submitted paperwork as required by…

I need to use both the full set of records from a relationship and also a subset of them in a month-end report. I'm developing an inventory and POS database, which has progressed thanks to this forum (and of course the people on the forum), and I'm working on the month-end report for sales (inventory reduction) and orders (inventory increases). Each of these are separate files, along with the reports file, and they are related using a date range (thanks CobaltSky for the date range example). What I need is to have the total for the month, and the fiscal year-to-date total on the same form (report). I can either get the records for the month OR for the fiscal year, bu…

Hi, I have created a database of animal records linked to a database of customers using animal ID _ customer_id. No problems there. But how do I arrange to enter the customer id in the animals table and get a matching address appearing as a read-only field(s)? I know how to look-up individual fields from Customers.Would appreciate your help. Many thanks Roger

I have searched and can't find anything on this. If it's been discussed before I apologize in advance. I have field for Staff in my projects file. I want to make it so several staff can be assigned to a project so I changed it to a checkbox which pulls the StaffID from a value list in staff. So far so good. Several staff can be assigned to one project. I want to establish a relationship from projects to Activities but I want it to treat each staff (checked in the checkbox) as a separate key field. No prob if that's all the key is ... but it also has project and date. My current key to activities is: ProjectID & StaffID & MonthYear MonthYear i…

Does anyone know the sequence in which FMP searches for a linked file, when it can't find it in the correct place?

I currently have a customer database that populates an orders database and aside from that I have a mailing list database that shares some of the same data. The relationship is created by a field with combined first name last name and email address. However, the relationship always seems to be case sensitive. No matter how I display the records (upper or lower or title), the database retains the actual format that it was initially entered as. So for example, the layout might show JOHN SMITH but the entry is acually John Smith. How can I make sure that John smith will match john smith in my different databases? Is this something that must be changed i…

I have ten related databases, what is the best way to generate a listing of all unique fields in these databases?

Aloha, my nonprofit can

Hi everybody ! I'm wondering how it is possible to create subtypes with FileMaker. For example, let's say I have a file clients, but I want to create a distiction between private and professional clients, which have different properties and heritates from the common properties of clients. I would thus like to obtain 3 files : clients, clientspro and clientspriv, the two last being subtypes of the first one. And I still need the file clients, if i have clients that do not match both subtypes. I'm waiting for your help ;o) Tanks a lot

Ok I have a odd question but can you create a record through a relationship without using portals? Heres my problem... I have 1 portal that just shows a list of current records that are related... then I have 7 global fields that are filled in by user then a script does a check on it when you hit save and then copys the data to the portal. This works 100% with no problem but I hate having to the portal show a new record line at the end everytime.. So what I did to fix this is to create 2 relationsships one for the portal(viewing no create) and one for saving.. The one for viewing doesnt show the empty line at all which is perfect.. The one for sa…

I need to be able to relink a moved file to other files in a master folder. The FMP setup is as follows: I have a cost estimating solution I have written in FMP. The solution relies on scripts and lookups to move cost data from a set of master solution files into a job CostEst file. For each new costing one file, the CostEst file, is duplicated and moved into a new folder elsewhere on the hard disk. It is then given a new name, say CostEst.230204. The rest of the solution files stay in the solution folder. The problem is this: The duplicated CostEst file relationship keys still relate to the solution files as the solution file location hasn't changed. However the so…

I've been working on this problem for months. I've posted twice and (not because the answers I received weren't good) but because Im having trouble GETTING it. Things have ben explained generally and I need specifics. Please help me guys, because this is important and for some reason I seem to have a block of some sort understanding it. Networked Server 5.5, Win XP, FM 6. I want people to switch to a form and see 20 small boxes of color. When they click a color, the background on every db they view switches to that color. I assume global so each person is different. I've read an INVALID global can be used but don't know what that means. I've heard use regular Conta…

i have run in to two problems 1- when compressing a file the relationships are severed and have to be rebuilt by hand. is ther any way around this? 2- i recovered a dmaged file and rebuilt all the relationships. in a related file i ahve a portal with a field from the master file, that one imported correctly. i also have a calulation [sum] of the imported field. when i go to rebuild the calculation in field definitions i am told the related field cannto be found - even though it is in the portal TIA dan

I have created a simple relationship and selected an indexed text match field from the master and related files. When entering text in the related file, I get the following dialog box: "The field cannot be modified until 'blank' (field) is given a valid value." In the master field, entering data is problem free, however, input the same data in the related file match field and the dialog box opens and the data is erased. Also, I made certain no other files with the same names or parts are on the computer.

can someone explain whether or not this is possible. I need to represent the following heirarchy in my files. ok an Item belongs to a series and a series belongs to a collection. no big deal, except that I have only two files in my database, an item file and a collection file. In the collection file there is a repeating text field that you enter the series titles. currently in my Item file I can assign what collection an item belongs two via a relationship and a value list (drop down list) based on the contents of the collection file. I would like to also be able to assign a series (again a drop down list)in the item record but limit it to the series (basi…

The current set up of my contact database is one to many. I have one file with contacts and another with companies. When I click on the goto to company button it sometimes doesn't display the relating company, the scripts are as follows: from contact - Toggle Status Area[show, Lock] Go to related record["organisations"] (with only show related record not selected - does this cause a problem?) Perform Script[sub-scripts. External:"orgainisations.fp5") above script in the organisations: just points it to the correct layout? I suspect it is something to do with scripts that maybe have been paused? Any ideas would be apprecaited, thanks Simon

I've created 3 separate databases for our archived materials (boxes, tubes, and cd's) and each has a common denominator which we call our job number. Each database also includes a box number or tube number or cd number field, so we know where in the basement these are located. In the job number field, there can be multiple numbers. When I link the databases, the search will only give me the first number in the field (they're separated by commas if that makes a difference) All the numbers in the field are relevant and need to be incorporated. I'm trying to link these databases so that when we need to pull an old job, we can find all relavant material without having to sea…

Hey fmforumers... Ok, I have a 3 database problem here that I'm trying to solve with scripts, but I'm stuck. The three DBs in question are Inventory, Shipments, and ShipmentDetails. These are incoming shipments and the Inventory file has a field for each product that shows the number of units on order. Shipments has a field call Received that's either "yes" or "no". ShipmentDetails and Inventory both have a ProductCode field. I want Inventory::OnOrder to be the sum of quantity in shipment details of shipments that haven't been received yet. I figured I'd need a match field that is ProductCode & "-" & Received. In Inventory it's simply Product…

I have been working for sometime on this problem and just can't quite seem to get it to work - I am attempting to sum a field in records which have a common ID field while omitting those with the same ID which have a checkbox checked. Should I be using one or two self relationships? Thanks.

I'm running into kind of a thorny design problem and I thought I'd ask for a bit of advice. I have a database that will contain responses to a survey, a series of 16 questions with answers in numerical format from 1 to 5. What I would like to do, is somehow relate each of those sixteen answers with a record in a different database. There's a few statistics calculations that need to be performed on each question and I was hoping to take that off to a different database file because of the repetitive nature of the calculations. Is this even possible?