Relationships

I'm hoping that someone out there may have a suggestion for what I'm working on. Here's the deal: I have three files, an Account file, a Owner file, and a hinge between the files to allow me to keep track of a many-to-many relationship. The hinge file has three fields: AccountID, OwnerID, OAHingeID (which is the primary key). When I'm in the Owner file, I want to be able to look at all accounts which belong to an Owner. So, supposing I click a button which says, "View all Accounts", it should take me to a list of accounts. In order to do this, I end up copying the OwnerID, then searching for that OwnerID in the hinge. In the hinge, I come up with se…

I have two related databases - A and B. A layout in A has two portals (X and Y) to B based on the same relationship link. I want the records in B to appear in one or the other of portals X and Y base on the contents of Field K (a Yes/No value list) in B. I tried to create different links for Protals X and Y by concatenating the basic link between the two files with the result of Field K but that isn't working. Any suggestion on how this might be done will be greatly appreciated.

I have a Contacts DataBase and a Scheduling DataBase. While in the Contacts DB, the user selects a date to work with, which I then store in a global date field (called Global Date). I created a relationship to one of the fields in the Scheduling DB called 'Schedule Date'. This works fine...all appointments for the Global Date are displayed in the portal. What complicates and raises the problem is the following: I have other activities, such as making a phone call, a task, a deadline, etc. that have no dates and should appear on the schedule until the user has marked them as being completed (in Completed field). I tried to change the relationship, but nothing works be…

Hello, I have 2 databases. One is a recipe database with a related field called ingredients which relates to an ingredient database. The ingredients field is a repeating field that uses a value list from the Ingredient database. next to the Ingredient field is a repeating field containing the ID number from the Ingredient database. When I browse and check off the ingredients the ID field only shows the first ingredient Id. I'm more than a little confused. Any help would be very appreciated. Thanks a million Alan

Hello! I previously posted this message at the bottom of a thread in "Define Fields", but it may be more appropriate in this forum. The respondents to my original post suggested that the best way to accomplish what I wanted to do was to upgrade to a newer version of FMP. I upgraded. (Although only to 5.0v3 for now). After upgrading, I was able to solve part of my problem. I am trying to print mailing labels from a relational database. It consists of two related files, "household" and "dancers". Each household record is related to one or more "dancer". I want to be able to print one mailing label for each household which lists all of the individuals related to that house…

I have a situation where I have two db that must relate to each other by two fields... for example, "title" and "location", and it needs to pull the info from a record to fill other fields if and only if those two fields match each other. I tried the following without success in a field called address. RelationshipA was built between db1 and db2 through the location fields. RelationshipB was built through the title fields: "address" field calc: If ( (title = ::relationshipA:title) and (location = ::relationshipB:location), ::relationshipA:address, "" ) I am not getting any errors, but nor am I getting anything in the address field. I also ju…

Setup: Database 1 (main) Database 2 (main/layouts) Reason: Since there's no realistic way that I can keep users from editing layouts and screwin' up stuff, I've created another database that allows the users to access the same information and make layouts as needed. Issue: I have these 2 databases and they switch through layouts quite often. If I perform a find in the MAIN database and then go into the second database it goes away (kinda obvious). Question: How do I bring over find criteria to a related database? Is there another way I could be doing this? I'm buggin' out here! Thanks! ~Addam~ Question

The Situation: I am attempting to create an FMP solution to run our Fantasy Football League. There are two main databases: FFLTeams and NFLPlayers. The NFLPlayers database has an "FFLTeam" field that designates which fantasy team he is on (if any), and a "CurrentStatus" field (containing a 1 or a 0) which designates whether he is active or inactive for the upcoming fantasy game. A fantasy team owner will be able to change the CurrentStatus of the players on their roster to designate a starting lineup for the upcoming game. The Problem: I need a way to determine if a team's starting lineup is legal (ie., are too many players active? are there two starting quarter…

This should be very easy to understand. I have 2 databases, One is family and it is related to many family members, by a family ID. In the family member file I want to be able to see all relations i.e. members of the same family. I have self join relationship for the family members based on the family ID field. My problem is that in the portal I keep getting two people of the same name even though I have only one of each, I can not see the other people in the family that I want to see. EG family name Murphy members of family John and Margaret. If I go into Margarets record, In the Relations portal I see margaret twice but can not see John.

A quick question. I have a computer set up as a host to share a database over our local office network. The database is relational with 2 other database files. Do I need to have all 3 files open on the host computer to have them be shared? Or do I open the main database only, and since it is related to the 2 other files will they all automatically be shared over the network? LR

Hi Everyone, I've struggled with this one once before, and ended up using a scripted solution, but I'm not really happy with using scripts if I can avoid it. Basically, I am trying to set up a relationship that links to the most recent 10 records created. If anyone can tell me how, I'd be much obliged. Obviously I could just set a portal to only have 10 lines, but that doesnt help for a web based solution. I really need there to be only 10 related records. Cheers, Sean.

Dear FMers, I developed a solution, with one file being a menu with buttons to open other files (common UI). When the user closes the FMP application with X (Windows), or File->Quit (MAC), I want all the files to close automatically, without without related files bugging users to be reopened. Now, off course I can script an individual file to first close all related files, and then close itself. But how do I tell an APPLICATION to close a cetrain file last? The problem is with the menu file. When the whole FMP application is being exited, it sometimes dicides to close the Menu before certain other files, and the "open with password" or "cancel" d…

Example: A view has a list of items that have been ordered from a company. Each item is attached to an order with an order_id relating the items file to the orders file. How would I count the number of orders BEING VIEWED? That is, I need a global count of something like sum(unique(order_id)) Am I making any sense? Thanks for any help you beautiful people. -wald

I have 2 db, the master contains 2 fields named "client" and "page num." The other db contains "page num." and "client", ""client2", "client3", "client4". The fields named "client#" are pop-ups using the same value-list. I want to get the master "page num." field to show me the page number based on the relationship between the master "client" and any of the fields "client#" in the second db. Thank you

I need 2 different sorts of invoices, each depending on a differten database of products. So far no problems but the seial number needs to keep counting independently meaning: 1 invoice a 2 invoice a 3 invoice b 4 invoice a ... now my 2 kinds of invoices count seperatly hope this is making any sence

I have 2 databases one with housting accounts and one with dns names. when I input a new dns name I have a drop down list to select an account by it's name. what I want now is that when I change the account name in the account database, that the account name in the dns database is updated.

I have a related DB off my main one that holds a user's address and any notes about them linked by their user-id. Each of the 2 fields above are in a single box (not a row of related records). I want FM to automatically copy their e-mail address over into that DB for another DB i'm developing off of the same address DB (but doesn't use the username). Ideas? Thanks.

OK, before I get too far, let me describe to you the basic system I'm trying to beat out here... My company owns a number of weekly shopper newspapers, and I'm revamping the database they use to store and output their classified ads for these papers. Here's the problem On the one end, we have the Ads database, the main core. Now, each Ad can have multiple Publication Dates, and multiple publications - that is, it can be printed in three subsequent weeks, in three seperate papers, each week. At the current time, every publication date denotes a printing in every selected publication on that date - that is, they cannot print the ad for one week in one paper and th…

I have two databases that are related to each other by an ID number. One is a list of videos and the other is a checkout database. Everything works fine for checking videos in and out. My next task however is to create a history of the videos checked out by faculty. Currently when a video is check back in, the title is removed from the list of videos that the faculty have out. I have tried using the Lookup option which I thought would work but it also removes from the list any videos that have been returned. My ultimate goal is to have a list of all videos that any particular faculty has taken out and possibly a list of how frequently a video is used. Thanks …

I have a client database - some of these clients are related (physically ie husband and wife). Each client has a unique client reference eg ABC001 I want to create a script to switch between husband/wife records - the problem is that they are in no way related (in FM terms). I am really looking for a bit of advice as to the best way of approaching this. The only method I can think of so far is to have a field 'RelatedReference' where users would input the 'related' Client Reference. I would then create a script (initiated by a button) that would just copy this ref --> Enter Find Mode --> Go To 'RelatedReference' and Paste --> Perform Find. Is…

I have a client that asked for some changes in his system and for the changes he asked for I didn't need to change all the files so I only saved the files that I changed in order to take the files and subsitute them at my clients office. I saved the files that were changed as a clone and I also tried saving them as a current file. Both ways the relationships disapear and shows <filemissing> Do I need to copy all the files , not only the ones I need ? Why doesn't the save as clone save the relationships ?

He all, I've tried everything I can think of without success, so I'd like to ask if anyone else has been able to do this. I have a file of items, sorted into various categories. When viewing an item, I have a self-relationship which allows me to see all items in the same category via a portal. I want that portal to NOT include the parent item, as it is just duplication. Possible? Any help appreciated. Cheers, Sean.

I have two db's I want to link but need to do it in a somewhat unusual fashion. The relationship between DB A and DB B is called Link and the key field in DB A is Field X and in DB B is Field Y. I start in DB A and enter data in Field X. I click on a button that copies the contents in Field X, takes me to DB B, creates a new record and pastes the result in Field Y. This establishes the relationship Link between the respective records. This works but forces me to have both Fields X and Y in their respective layouts. SetField would be a better solution but it does not work because the record I just created isn't linked until the key Field Y is populated with the data neces…

Hi, I have a sub-summarized report that looks similar to this: (Sub-Summary Part) Count of all (A) Body Items = (4) (Body) (A) Body Item (Body) (A) Body Item (Body) (A) Body Item (Body) (A) Body Item (Repeats with next Sub Summary.....(,©, etc..) I would like it to look like this: (Sub-Summary Part) Count of all (A) Body Items = (4) (Body) 1. (A) Body Item (Body) 2. (A) Body Item (Body) 3. (A) Body Item (Body) 4. (A) Body Item (Repeats with next Sub Summary.....(:…

With the changing USPS shipping rates at the end of this month, I'm forced to create a shipping rate calculator for purchases. In the past it wasn't as much of a problem because orders were flat feed based on weight. Now weight and location compute together to create the shipping total. I want to create a DB with all this info in it so i can easily change future values for rate changes and shipping costs. The DB will be turned into web pages and posted on the web site (i don't need the help with that part). Here are the variables: Zip Code Relates to a Zone (I.E. Zip Code Prefix 902 in my case ='s Zone 1, while Prefix 100 ='s zone 8). I have a listing from US…

I need to count the summarized fields in a report I am doing and am not having any luck with the count or the get summary function. I am summarizing 592 records and need to show the summarized count not the total count. A running total would be even better. Has anyone ever tried this successfully or unsuccessfully.

For a school reporting system I have a calculation (depending on the report type) that brings one of two value lists into a pull down menu. The value list that contains the values: A+, A, B+, B, C+, C, D+, D, E+, E works fine when not in a calculated value list, but when its in the calculated menu, it loses all of the + signs and gives only A, B, C, D, E. How to fix?:?

I am having a problem with my conditional value lists. When I go to enter an item it works just fine but when I go to find a record it is not working. Can anyone understand? I have other conditional lists on the form, they are all working in find mode.

Is there a way to make filemaker match the exact string instead of just the first matching substring when comparing fields in defining relationships? ex: 15-A' should match to 15-A' only and not 15-A Thanks a lot!

I am currently wanting to track attendance in our church

I have three files, call them "Main", "Related" and "Third". Main has several one-to-many relationships with Related. Each is based on a concatenated field in Main consisting of a particular global field and a field unique to each record. This is done because there is one more relationship, based on the unique field only, that shows all the related records from all the other relationships. So far, so good. The problem is that one of the relationships from Main to Related has to have the ability to create records (in Related) from a portal in Third. The problem is putting data in the field in Related that is the basis of the relationship…

My users have found a bug that when they DELETE (using a button assigned to "delete record") a record it not only deletes the current record but all records and their relationships. I do not have a funky script nor do I believe I have a bad relationship. I cannot duplicate the problem but it has happend twice cuaing severe loss of data. Any thoughts??

hi there. in my last post i was trying to creat a shell that will find info in another database. it didn't really work to well. so i will try to explain what i want to do. i have a program that is going to add/find/view information. i am able to add records and find them but i want them to come back in a portal. the problem is it comes back in different records. i want them to come back in one record just on different lines in the portal. i have the main program creating a key(date and time created) this key is related to the housing program. the key in the housing program is just a text field. i need to create separate records in the housing program (it's a sim…

I am just getting started with Filemaker, and need to keep track of mice in our mouse colony. I assume I need a mouse database (each mouse), and a cage database (many mice per cage) but we have several types of cages (breeding pairs have one male and one female; stock cages may have 5 males or females, stud cages with one male and occasional visiting females) and we sometimes move mice from one type to another. Should each cage type be a separate database? Suggestions to get me started?

i have been trying to relate a database with my main shell. all i want to do is do a find from my main shell to a information database. the fields that i am finding are the related fields on the info file. now what i am looking for "workable time" and "day of work". i made 1 field in each program and is a constant key. i used 999. the relationship works but when i do a find for a time, all the records show up in the portal window i made. example. i have 4 records that are the same day but have 4 different hours... 9,12,3,6. if i do a find for 12... all the records show up. i know that i am to make each record unique, so i made a field that is a calculation...…

I have 2 DBs, Dailysales and MerchItems. DailySales is date-based and contains information and calculations that track sales of Merchandise at shows, each item is accounted for with fields such as shirt1, shirt2, shirt3, and the actual names are auto-entered. MerchItems contains 1 record per Item. Currently I have a relationship for every Item (shirt1=::ItemName, Shirt2=::ItemName). This solution works, but is becoming labor intensive and limiting. Is there a better solution for relating these DBs?

I have a 2 files that are related based on a calculated match field. However the match field which is text is greater that 20 characters. Since FM Pro will only match on the first 20 characters, is there any work around to this?

: I am creating a graphics database. I want to link a PSD file (record) to many BMP files (records) with in the database. I would like to have the PSD file (record) show up in the records of the BMPs (that it is associated with). If the PSD file is updated in the database, I want the psd file to automatically update throughout all of the other records. Is this possible??? I am guessing that I have to do a one to many relationship. I have tried to do this but can not get it to work. Any suggestion???

Is this even possible? I am a total newbie and I need to be able to some how relate records in the same database. This is a basic Client managment/tracking database. For example: if Billy Bob Thorton is one of my clients and his wife Angelina Jolie (hummina hummina) becomes a client as well, I need that to show up on each others records. And yes they need to be separate records. This is probably a really stupid question, sorry. Joe

I'm still struggling with this problem. Any ideas would be greatly appreciated. Here goes: This is for a Credit Processing Solution which I've been building forever. There is a Customer File (with a Cust ID field) which is related to a Phone Nos File (with a Cust ID field - using allow creation of related record). On one layout of the Customer File the user can enter as many Phone Nos for a customer as they like (within a portal). The user also can enter a Phone Type (Residence, Work, Cell, Fax, etc).In the next layout of the Customer File, the user may enter a Work Phone No. and Work Fax No. (Single Fields, not in portals). Here's what I'm tr…

The data base that I am trying to put together is for a life insurance agency. I pretty much have the client stuff figured out but I am wonder what the best way to track agents is. In my Agent DB I will track basic demographics and personal info. But I also need to be able to track/view "downline/upline" agent info. Downline just means the agents an Agent has working under him/her (he gets a piece of thier action through commision splits). Upline is just the oposit, except there will only be 1 upline agent. In other words each agent can have many other agents working downline from him but works for only one other agent. Kind of sounds like a pyrimid scheme! An…

I have 2 dbs; contacts.db and orders.db they have a relationship defined on the main contact field. The contact field in the orders.db is a value list defined from the contact field in contacts.db with other related data looked up. What I want to achieve is when a user has found the contact they want in contacts.db, have a button there that when clicked opens the orders.db, creates a new record with the contact field and other related data autofilled. How would I achieve this?

I have 2 files; "Daily Sales" and "Merch Items". "Merch Items" contains info such as price and original inventory. I need to be able to track remaining inventory. Right now I have a simple calculation which subtracts daily sales from Original Inventory, but of course that only works for the first show. What's the best way to record the new inventory figures so that the next day sales are subtracted from the most current figure?

Howdy folks, So I'm wrangling with an issue that I suspect a relationshipcould solve, but I can't quite figure it out. It's with a database that tracks outreach instances and volunteers. For each Outreach, there are many Outreach Volunteers; both of these are viewed through a navigation database that has a one-to-one relationship with both of these, called Main_Outreach (which is a holding tank of all globals and no actual data). In any given month, I need to determine a total number of volunteers for the month. BUT! Because of our contract, I need to find only the unduplicated volunteers for the length of the contract year (so if the contract is a calend…

Hi, I have recently developed a solution for a client, and have encountered a strange problem. Pretty simple development, basically sales orientated, with a customers and invoices databases. Have set up a relationship between the two files which essentially allows users to create new invoices and enter a customer id (primary key field), which will then bring across relevant contact details (onto the relevant layout within invoices). Have a "Cash" customer within the customer database, and they have been roughly 1,000 invoices already generated on this contact, but now what seems to be happening is that whenever a new cash sale is to be created is takes an incredibly …

Hi, I have 2 pop up menus 1 called machine and one called ink, what I want to do is be able to select machine and it atomatically selects the ink that the machine uses. Thanks for any help Chris

I have created a Requisition File, Product File and Purchase Order File. The Requisition File is related to the Product File by a product code field. The Requisition File is related to the Purchase Order File by the P.O. number field. The user can create a requisition for a supply item by pasting the product code into the requisition file product code field. This will automatically displays the product name, description and cost. The user next creates a Purchase Order and a PO number which, when pasted in the Requisition File P.O. Field next to each supply request, will automatically display those items on the PO form in the related PO File by way of a portal.…

I've been struggling with this one for awhile. I have a music tour database where: -the main database is a one record per day. -every day has a promoter for the event -every day has a one-to-many to musical Acts (with bandID, start & end times etc), and in shown in a portal in days. there is also a promoter database which promotor info in stored (contacts etc), as well, I have a portal the shows all the days that promoter is working. I've been asked to have it show the bands which were playing on the days that promoter was working in the portal. I almost got it working by making a self-relation the Acts DB and making a v…

Just a simple questiuon really..! Is there a recomended max number of relationships for one database? I have one database which has 7 relationships all related to other databases and i just want to make sure i am safe with this. i will be relating more databases in the near future. Thanks in advance. Ricardo

How can I import records from an unrelated file (db1) into another file (db2) when db2 has related fields in its layout from db3? When I attempt to import into db2 and I access "match fields", the related fields used in db2 don't show up. Is it possible to have the related fields in db2 show up on the "match fields" dialogue box, or do I have to import the data from db1 directly into db3, the "master file/fields"? I hope this question is clear. Thanks for the help.

Hi, I have created a database in FMP and I have 2 pop up menus one is comany name and the other is the size of paper eg:a1,a2,a3,a4,a5 but the sizes are all different to each company. So the question is, is there a way of selecting a company name and it bringing up all the sizes that relate to that company that I have selected in the comany pop up menu box. (I dont know much about relationships) For example I choose Adidas as the comany the second pop up menu brings up all products that relate to Adidas On the next record I choose Nike the second pop up menu brings up all products that relate to Nike Thanks for any help …

I'm using one file that contains portals and relationship into several others. I do all my data entry and reporting from this one file. I want to use first, previous,next and last buttons to navigate. I also want a field that reports the current record number of the related file I'm browsing. My main problem is a relationship: GlobalReccordNum::CalcRecordNum If I change the calculation field to Unstored: Status( CurrentRecordNumber) my relationship won't work. Any suggestions.

Is there a way to export or save data in a relationship? I have a file with data from relationships from the children. Can I save that file with the data in tact?

Hello there! Today I get a new message Ive not seen before: I use FM 5.0. Windows 98. Pentium III. 256 Mega. I have my logotypes in one solution which all other solutions pick up when whey need the logo. On my laptop, and on the normal computer, I can go to the logo field and copy/past as I wanted, but since today when I should replace the logo I got following message: "FileMaker Pro cannot complete the operation because of an OLE (0x80004002) error." After that a new sign come up: The memory is not enough. But the system says that I only use 68% of the computer capacity. Whats wrong? Hope someone coule help me. Thanks. …

I have a FM 5.0 (Mac) database with 2 files: one with contact information and the second with transactions done with the contacts. There are many transactions for each contact. The transaction datase uses related fields to fill in info like first name, last name, etc. from the contact info to the corresponding fields in the trasaction form. The fields are related fields, not portals. This all works fine... untill we do a sort based on one of the related fields in the transaction database. Nothing happens and the records are still in the original order. A sort based on non-related fields works fine. Any clue as to what is going on would be appreciated.

Hi I want to avoid the modication of a related field. I've seen that before eg the Name field can be edited in the "A" db only. The same field can't be edited in the related db. Don't tell me it is easy as pie, I know it is but I can't figure out on how to do it. Bikeman17

Help, I have 2 files: 1 called "Jobs" that contains specific job information. Including a portal to a file called "jobItems". No problem here. The other file is called "Invoice" and contains billing information for each job, also with a portal to the same file called "jobItems" where a user will check a box when the itam is invoiced. So, the 2 seperate files need to display the same portal. At one point, i got it to work, but was not able to create multiple invoices for the same job with the same set of "jobItems" and the same job number. Here's what I think is involved relationshipwise: Jobs - jobItems = 1 - many Jobs - Inv…

I have 4 modest databases tracking business development and delivery in a professional services organisation. The DBs go through prospects, project mangagement, closure and clients. Access to the DBs is via the web feature of FP5. What is the best way to set up the relationships between the various DBs? At the moment i use Lookup, but is it best to just keep all 4 DBs together and use direct references to the respective Dbs? The issue that brought this on is how on the web do we update the lookup fields if they are changed, for whatever reason in a dependent database? For example, if we change the project manager through the job, how do we get de…

Hello all. i've got 3 questions for all you out there. I'm somewhat new to FM so these might be easily answered questions. And sorry, wishes/magic genie not offered for answers. Anway: 1) I read several FM books. But none explicitly mention the fact that relationships seem to be made in the children files. Why is this done instead of making the relationships in the Parent file? E.G. the relationship btw. customer (one) to invoices (many) is created in the invoices file. This seems odd to me. Anyone know why its done like this- to confuse beginners? Odd. Seems it would be more logical to define it in the parent file.... 2)I thought one of the rules for a db t…

I would like to be able to do a horizontal split screen between 2 portals. I would like the host record on top, and the portal records down below. But i'd also like to be able to do searches on the top window and leave the window as the same. If you need more clarification, I'll give the example. E-mails are brought into the portal, but unfortunatly the ID (key) number is not always available. The top window would be used for finding that proper ID number, and then i'll bring it over by a script. Bottom is used for browsing only, top is used fo rsearching. HOpe this makes sense. Thanks.

Also, I think I will have to change my primary key because when I change the name of the producer associated with the prospect, it switches to a record that is related to that producer.

I'm working on a report card. I have 2 files, Students and Skills which are related via student_id. Students holds student name, id, and grad year. Skills holds student id, grade, skill, score, and school year. I enter the scores through a portal in Students. I need to be able to assign each student a teacher, but since this will change every year, I don't want to do it in Students. However, there is more than one teacher for each grade, so I don't think I can do it in Skills. What I would like to do is be able to enter a teachers name/school year and get that teacher's class list for that year with the appropriate skills. How do I do this? Thanks. …

I'm using a relationship, but it's probably not the best way to do this, so this is why i've brought it up to everyone on the board. I'm trying to figure out a way that a FM file is a holder for a counter for the number of e-mail's that go in and go out. Right now i ave a single record in a counter file, consising of all globals. This works great, but i'm the data doesn't transfer between users and is erased when i exit. I'm looking for a way that all the users can access the information, edit if they need to (via script) and reset. And i want the data to stick around (keep adding up) until i reset it. Hope this makes sense -j

What's the best way to do lookups for form letters? I'm creating layouts in my database to send various letters for different occassions. It's easy if you simply build the layout. But I'd like for the text to be stagnant after I send the letter and not reflect any text changes I make to the layout further down the road. I'd also like to be able to alter the wording of the text on a per record basis without altering the text for every record. Special Edition for FileMaker suggests creating a separate database with the text that I would simply lookup and fill in my forms. What's the best way to do that? Do I simply relate it with customer IDs? [ April 03, …

I have received help on this but still no go. I will try better to explain the issue: I have 2 databases: Database 1 has a summary page to pull data from the database 1 and other related databases. Database 2 has temp employee data I want to be able to show, in the summary page in database 1, the total dollars spent on the current found project from the temp database. In database 2 I calculate the "record total" in database 2 and basically need to have a field in database 1 that says something like: case(project=databse2::project, sum(linetotal)) When I do this the field is blank. Help!! Thanks!!

Hi All: I have 2 related DBs: Projects and Project Tasks. In one layout of Projects, I'd like to view a portal of Project Task records that have "Staff" within the Project Task field titled "Task Type." In another layout, I'd like to view a portal showing only Task Types that are "Purchases." Lastly, another layout with a Portal viewing the only other option for the field entitled Task Type, which is "Services." Any help would be much appreciated... Kindest Regards, John

I looked more closely at the results of my calculations for the date to range relationship and am sort of getting somewhere with the answers but not knowing the inner workings of this rather complicated calculation I can only see patterns in the answers with no real idea of what they actually mean. In some ways the answers are more confusing than the calculations, as it seems to be something with zeroes and there is no indication that they should be ignored that I can see. When answers to calcs. are text do the zeroes respond the same way? The paterns I see in the answers may in fact be a red herring, but I am wondering if someone can help me to decide this. I have 1…

I have 3 related databases; events, venues, and people (attending events). Each time an event happens the people might change, but I would like to be able to track how many times a person has attended an event. Is there a way to do it without storing people's names in the event db? I plan on using event IDs, Can I add an event ID to a person's record each time they attend another event? Thanks.

Two related databases, each with a related field called "ID." Database 1 has 5 records containing a field called "First Name." From within Database 2 if I type in the same "ID Number" is there a way to have a field that will then contain all the "First Name" matching data from Database 1 (I know how to accomplish this with a portal, can it be done through a single field with a calculation)? I just need a field in Database 2 to contain all 5 matching "First Name" from Database 1. I don't necessarily need to view this data, I just need a field to contain all 5 names because I will be using another calculation field in Database 2 that will rely on this info. LR …

Hello I'm new and I'm struggling with the concept of primary keys. I've got four files for a farm. Planting details which times the planting of crops, and works out how many seeds are needed to be planted based on a grower's order. (Has variety, grower's name, planting date, picking date) Plant growth which just has the different growth rates of a variety given the time of year planted. (Has variety, january, february...december) Seed bank which maintains a running balance of different varietal seeds in stock. (has variety, date in, date out, then calculation fields) Growers which is a contact database of clients. (growers name, address, …

In my database, I have a button that uses the script "Go to Related Record" And of course, if the file containing this button has a match with the related database file, it opens the related database file and goes to that record. But if there is not a match, it does not open the related database file. Is there a way to accomplish both. In other words, I still want to use the "Go to Related Record" script, but even if there is not a match I still would like the script to open the other database. LR PS Everytime I open my database using "hosts" from a remote machine in the office, it always prompts me that it cant find the related database, yet they are…

Here is another "Go to Related Record" script question. I have a button using this script. Pretty basic concept. But when I view my database on the web using instant web publishing and click on the button, I get the following error: Unable to process your request because the layout "1" could not be processed. Not quite sure why it is asking for layout "1" and what it means. LR

I have a brain tease for people out there. I have a calculation/relationship that allows me to have a relationship between two files using a date range to define which record to go to. Most of the times it works, but there are some dates that don't show up as related for some reason. Either there is a flaw in the calculations below or Filemaker is having trouble with it in some way. For now I wish to assume that it is a flaw in the calc. If anyone can see this flaw can you tell me where to look? Much appreciated. file 1 (tours): I have tour start date, tour end date, and artist. Then I have this calc for artist_range: Substitute(Middle(" 0 1 2 3 4 5 6 7 8 9", …

I am Flummoxed!! I have three files related my first file is departments, it is related by department to its catagories which is related to it's subcatagories. My big problem, when I create a Department everything works fine. When I create the catagory Everything also seems to work fine including looking up default values from department. But when I create sub-catagories the lookups do not pick up the values. My sub-catagories is related to the catagories by a calculated field 'department &"/"&Catagory this has been indexed in order to allow it to be used for a join. Am I doing something wrong?

I'm creating a report card for an elementary school. It is K-6 with 2 or more teachers for each grade. Each grade has its own set of exclusive skills they report on (although there are a few skills every grade reports on). I have the following files: --Students.fp5 with student demographics, including graduation year and student_id. --Skills.fp5 with fields for the skill, the score earned, the grade the skill is scored in, and the school year --Reports.fp5 which is a join file between the two. I also have Teachers.fp5 with the teacher name and grade taught. (I'm thinking this should be an attribute of Skills.fp5, but I don't know how to account for more than…

I distribute product for the travel industry. I am try to build a database to help me mange the distribution process as it grows. I have db01 containing the names of my clients, db02 containing the client's products and db03 containing the names of the agents I distribute to. The agent db has about 3,000 records. I have constructed a portal using simple relationships (A:A) to display the clients products. Each client catagorises the agents (a, b, c, etc.) by product. So agent one may get 20 of product A from client 1 but 6 of product b from client 2. The problem is that each I need to catagorise the product displayed in the portal specific to the agent and …

Years ago I built a database using relationships...As I recall it was between 4 database files. I have not had a use to do this for quite a while. Currently I have a database file I am using. I have built a new database lfie with a layout for custom reporting purposes. It wll get the data from my existing database. From what I remembered I have to have 2 similiar fields in eash database. In this case I have went with a ID Number field. In the databases. These fields are set to do an auto number+1 entry each time a record is created. All I really want the new Report database to do is flow along with the Maste databaser. In other words, if I create …

Can someone explain what a multi-key is, how it works, and when it's best used?

As a fmp "newbie", I don't really know where else to post this question, so I'll try it here. I've noticed in a number of sample fmp applications that I've looked at, that related files are usually left open and minimized at the bottom of the window. My question is why are they left open? Is that a fmp requirement?? Isn't it possible to perform a lookup to a related table (retreive data) without opening that table and keeping it open to retreive the data. Same question for modifying/appending or posting data to a table. Isn'tkeeping these other files open all the time a potential security issue and a drain on Windows (system) resources? Isn'…

Is their a way to lock a related file. I would like to view the field and have it update but not be able to make any changes from the masterfile? Thanks Andy

Is it possible to nest portals within one another? I have a filed called Order Entry where you enter each PO with all of the parts in it in a portal to PartOrderJoin. I have a separate layout in Order Entry where I do the receiving for each PO. Right now, I have a repeating field that lets me enter 10 separate instances of receiving each part (I need the capability to receive each part on an order multiple times). Since I set things up this way, I have learned the error of my ways (NEVER use repeating fields if you can avoid it!). I would like to be able to make the quantities received relational. To do this, I would have to nest a portal to a new file that woul…

I must be missing something here. I am trying to create a related record in another file with it's own unique line item number. I am using two files, Product, and Inventory Line Items. The only purpose of the Inventory Line Items file is to accumulate what has been received per item. I will then use this total to calculate my QOH in the Products file. Products file fields: Category Item#(indexed, auto-enter, serial) Name Qty Received Date Received Inventory file fields: InventoryID#(indexed,auto-enter, serial) Category Item# Name Qty Received _Total Qty Received (Sum) Date Received Relationships are as follows: (Prod)Category::(Inv…

I have a file called 'hotels' and it is related to a file called 'events'. At present I hac have one displayed hotel for every event. How can I display the data for hotel 1 in one set of fields and hotel 2 in another in the same event record? Do I need 2 relationships instead of 1? How would I distinguish them? Thanks for any ideas.

Is it possible to have mixed case text keys in FM? The reason for asking is that I have to import leads with unique identifiers like record1: 00Q00000001gBVC record2: 00Q00000001gBVc Sometimes it would be helpful to use this key to exclude duplicates on import etc. Is there a way to make this a unique key in FM or make a calculation on this field that will be unique?

I'm having problems with one of my calculations. What I'm trying to do is this: Database 1 records individual transactions. I have a relationship from Database 2 to Database 1, and that relationship is called "NDirectMutualFunds." I have a calculation field on Database 2 that uses the following calculation: "Case[NDirectMutualFunds::PayDate >= DateRange1 and NDirectMutualFunds::PayDate <= DateRange2 , Sum[NDirectMutualFunds::AmtExpected],0)]" NOTE: The brackets in that calculation represent parentheses, but for some reason I couldn't use the parentheses in this post. My goal is for the calculation -on Database 2- to summarize a particular …

Hi, I just started working with FileMaker Pro and am really amazed at how easy it is to set stuff up. I've been trying to program stuff with Real Basic, and finally realized much of what I want done can be done with FileMaker. So, I've been working with it and also browsing this forum and other website for info. But I still have a weird relationship problem. Here's the deal. I have a three main databases. CONTACTS, JOBS, and ORDERS. Contacts may or may not be clients. Some clients have jobs, others order from another client's job. My problem is this: I want to be able to easily relate contacts with another contact's jobs as well as have a…

I have 3 databases. A (Campaigns) B (Jobs) C (Stages) Relationship 1 Many (A) to Many (: Shows jobs in each campaign. A job can be related to more than one campaign Relationship 2 One ( to Many © Shows stages for each job. The problem is I need to print this data so that each job appears with it's campaign(s). To do this I need to print in the (A)Campaign file. I need to find a way to get the ©Stages for each job to print in a portal in file (A) based on Relationship 1. Anyone have any ideas?

Are there limits on the number of characters in a related field such that when exceded, the relationship brings back wrong data? Case: We have a production batch database which is related to our catalog number database (catalog # = catalog#). The following part numbers: D-5580-95-CAL-IS-01, D-5580-95-CAL-IS-02, D-5580-95-CAL-IS-03, D-5580-95-CAL-IS-04 and D-5580-95-CAL-IS-05. If the -05 is selected in the production file, the -01 shows in the relationship. I know that there is a limit of 20 non unique characters for searchs, but this is less than that. [ March 15, 2002, 08:13 AM: Message edited by: D V ]

I have some nicely working relations between, let's say, products and orders. The orders are linked to one of the products, and shown in a portal (in the producst-file). What I would like to do is to be able to filter out a date-range in the orders, sum up the tolal order-value (of the found records) and print thes in a nice layout (in the products file). How do you do this? - Is it possible to do a search for a date-range in the order-file, then go to products and the portal reflects the search in the orders-file (so that only orders within the date-range are shown)? thanks!

How can I copy information from records in several join files into a main join file? Each join file has a Reference Number field and a Part Number field, along with a Quantity field. Each quantity will affect inventory. In order to get a total inventory count that's dynamic, I need the records in the main file to be automatically created with any changes in the portals of the master files. Is there a way I can set this up? Any suggestions would be greatly appreciated! - Confused Seminoob

I have a database in which the records are people, some of whom are linked to one another. Each record has 3 different ID fields signifying whether the person is a Host, a Guest or a Spouse. These are text fields which have number codes. There are then self-joins to match the IDs (HostGuest is the r'ship for HostID::GuestID, HostSpouse is the r'ship for HostID::SpouseID etc.) so that if I am in John Smith's record I can see he is the host of Bob Jones in the field HostGuest::ExecName. That's fine. Now when I'm in John Smith's record I also want to see if his guest Bob has a wife. So I created a calculation field Hostguestspouse where the result is the related f…

We use FM5.5 w/ server to manage our e-bay auctions. Right now it's really simple, but we're expanding. It has fields for titles, description, and the pictures and from that it generates the body for the auction, etc. Everythign is sorted by auction #. We now want to have FM generate the e-mails for us. Since i have so much stuff crammed into the main DB i want to create a second one and have it pull from it. So like, search by #, then go to second DB where you put in the db and it does all my calcs for final prices and generates the e-mail. Is there an easy way to do this or should i just put it in with my main DB? The only data held in the second DB will be e-ma…

Hello, I am trying to implement a database, but I am having some ER troubles. Does anyone know how to implement the following? You have a job (project). This project has a number of items. Every item is made by a different vendor. So I did the following : 3 tables Job, Item , vendor Relation job-item 1-n item-vendor n-1 So far so good BUT now how can i invoice this all? Because 1 job can have many invoices 1 invoice has many items on it. In the table job, i have a portal with all the items. The idea was that i could flag the items and then know that i could invoice them. …

Hi all: I have two databases: Contacts.fp5: a database of artists available for work; bookings.fp5: a database of records for scheduled jobs. currently the relationship between the two is such that you can go to the record detail within Contacts and view a portal of all the Bookings related to that artist. you can click within the portal to be transported to that particular booking. what i would like to do is add a calendar such that it appears as a 'portal' within the contact record for a particular artist. when the user goes to the Contact.fp5 layout containing the calendar, he may view the calendar dates for that artist only, with event dat…

Who can please help me with this? I want to calculate, in a timecode database, the total of working hours in a portal, compared (looked up) from 2 fields. (for example: if field "ordercode" value =8001 and if field "ordernumber"=2002-01, then add up fields "working hours". I hope I've explaned this clearly enough?

When creating a new document in a related file with a one to many relationship how can I automatically enter the Customer ID number that links the two. Right now, I'm copying and pasting this number using a script. I've got to believe there's a better way. Thanks, Kelly ------------------------------------------------------------------------

Okay, hopefully I haven't boxed myself in here. I have three files with the following relationships. 1. [Customers] --> 1 to many with [Publications] -- linked with Customer_ID -- 2. [Publications] --> 1 to many with [Options] -- linked with Publication_ID -- 3. [Options] I'm printing a report using a layout that I created in [Options]. Inserting information from either [Publications] or [Options] is no problem. However, the only way that I can include information from [Customers] is to add a [CustomerID] field, relate [Options] to [Customers] and physically enter in the Customer's ID number in the [Options] file. So here are my questions. …

Recently CaptKurt herlped me with a database problem I was having by instructing me to use the following command: If (Status(CurrentError) = 101 This actually worked great! Where can I get a list for all of the Status(Staus Flag) commands? Thanks, Kelly

hello there! may i describe a problem in its reduced form - i work on this since hours with no success. there is a main database A and a related database B. A and B are related by the field "key". I want a script to copy the content of a field of the active record in B to a field in A. this does only work when i try to copy the content of the field "key". if a attempt to copy any other field, i receive an emtpy field as result. what the hell is the problem - i am really at the end of my good mood! thanks to anybody who has an idea, bernhard (imz)

Hi again... I have my DB with many relations and I want to create another but I can't, please help me. OK, I think may be it's easy, I have MY DB (Patients) and another 2 DB (CXCOP and CXCOR) when I create a payment in my Patients DB create a record in some related DB, (depends the patient status), but I want to find to all family in my report and show me the total by family in my Patients DB. Can I do this with the mother name field??, for example: Mother's Name: Karol Scultz and show all her sons may be 2, 3 etc. and the Total to pay for all her childs. The mother's name field is in the Patients DB, please help me to do this, TKS Jorge So…