Relationships

I have 3 tables and i need to combine certain fiels all into one table. Im fine with creating the fields i need in each of the 3 tables the problem is how in the world do i get all the information from each table combined into one single table? and please im new to this so im not that familiar with the terminalogy though im very good in navigating my way around and figuring small things out here and there. Please help me ive been trying to figure this out for 3 months. and keep delaying it more and more and now im at a point where its killng me and i realy need this

This should be so simple but I can't work it out! - Can someone please tell me how to set a specific layout to be the layout that is shown whne my document is first opened. Thanks v. much..

I have a relationship based on 3 fields. The 3rd field is sometimes blank in both tables. Whenever that is the case, it seems the relationship does not work correctly. Is there a way for this relationship to work if a field is sometimes blank?

Say you have a layout in a parent file that shows a portal to the child file. When you open the parent file, the child file will open "hidden" -- it shows in the window menu as (child) with parentheses. When you select the child so it's visible and then close it, it doesn't actually close, because the portal on the parent layout forces it to stay open. So it goes back to its hidden state. That makes sense. But suppose you go to a different layout that has NO fields from the child on it. You would think that at that point you could bring the child to the front and close it. Nope! The only way to close it is to close the parent file. Even when you have NO relati…

So, I have a moderately complex database I'm working on which uses record IDs for pretty much all the tables. But the users get to dynamically build a lot of the relationship records via portals. I've done a lot of these in FM over the years, and now I'm trying to figure out the best way to do this requiring the fewest fields & relationships. This sample database has a simplified version of the type of relationship I have to use in many places. The trick is that I'd like to hide the ID numbers from the user (they're meaningless to them) and allow the user to build the relationships through the portal (via a popup), based on the name. In the past I've handled…

I have an inventory with Customer,Vendor,Invoice,LineItem - it all works great. But I have something additional. Please read below. Here is the scenario. We purchase spares parts. We are a small department that looks after equipments in lecture theaters of an institution. We keep these parts until we get request from the institution to install/replace these parts in a lecture theater. So we want track in - in which lecture theater a part has been installed (location field) – a drop down menu - Who the part was issued to (name of the (employee field) who took the part from the department to install) – a drop down menu - In which equipment in the l…

I am using v8.03 advanced and I have two fp7 files. The first one is the main one and the second one I am using as the lookup file. I have set the relationship and that works fine. How do you get filemaker to get all occurences of the match out of the lookup file? Ex: Mainfile.. 100 name address city state Lookup file.. 100 dollarone dollartwo 100 birthdate marrieddate. When mainfile addresses a record with field 1 = 100, filemaker sees the first record match in the lookup file but I need it to continue through the file to find the next occurence. I hope I explained this ok. Thanks in advance for the help.

[color:green]EDIT: The link has been fixed. I have been argueing with my friend with regards to this relationship. I have attached the database. Please take a look at the relationships. (Right Click, "Save target as"). Each Sector can contain any amount of activities. Each Activity can contain any amount of Actions. Each Action can contain any amount of steps. (Action Step Number - The order in which the Action is performed) Sectors are defined by the user - so I am arguing that when a new Action record is added to the database, the user should select which Sector this action falls under - hence the need for a link from the Actions table to the Sect…

Hi I am using FM 8. This is the scenario. File1 -> contains data tables File2->contains all layouts that diplays data from File1 using relationships File3->contains Report layouts that also need data. My query is this. I will go to File3 (reports)from File2(layouts) when a user presses a "Print" button. Do I need to create the same relationships in File3 to show the data in Reports or the relationships in File2 will be active since I am activating the script from there. Thanx

Hi all, I am trying to map out and ERD diagram for a Solution for my company. I am having a hard time determining what i should seperate as entities or attributes of an entity. I have other areas that I will tie together such as contacts etc but i want to focus on the problem area i am having. I have about 5 different products. So obviously i would have a product entity. I also have Prices for each of these products BUT i am starting to sell more overseas and i need to have Multiple prices for these products. So Now i have a Many to Many relationship.My first question is Should i have a seperate entity for currency? and then make a join table between Currency an…

Hi all, I'm trying to add a relationship from my wps_submissions table to project. But im not able to because the system believes I have already created one. Not sure why? I'm getting a "There can not be more than one relationship path ..." See attachment. thanks in advance for any assistance. WhereIsTheSecondRelationship.pdf

I have a relationship that has been working a treat. Until now... I have just discovered the reason and hopefully some one can help. The reltionship is: Table A 1. number field: Field A 2. Text Field: Field B 2. CalcField: Field A + 50 Table B 1. Field A (number) 2. Field B (text) Table A Table B Field A > field A C_field A < Field A Field B = Field B This relationship gives me all the values that that fall within my tolerance band. great, it works.... However, it only works as long as my field A in Table A stays below 949. Then anything over that means that C_fieldA is over 1000 which confuses the…

Hello Team, I'm working on a database that I will use in a school district to report on Attendance information. Attendance is tracked by our student information system, and I'm exporting that data to FMP. Incidences of attendance are identified by letters. For example a "Tardy" is identified by the letter "A", Tardy +30 by the letter "B", Unexcused Absence by the letter "C", etc. The attendance codes range from A through L. With each letter representing a unique entry. I've designed the database from the perspective of wanting to identify instances of Attendance and link them to students. That seems to be working well. But I'm now trying to figure out…

Has anyone integrated the Meetings 2.0 and Tasks 2.0 Solutions available from FM? Or does anyone know of a white paper describing the process of eliminating redundancy between the solutions? Any suggestions would be appreciated. Thanks.

I'm creating a shipment receiving form for my client. Among other things on this form, I want to show the "Ocean Freight" company, the "Customs Broker" and the "Drayage Company" (local trucking company) for a particular shipment, along with all their respective contact info. Click on the company name field under the Ocean Freight Company heading, and a value list pops up. Choose the company, and their contact info appears below. Then choose the Customs Broker under that heading and the same thing happens there, and so on with the Drayage company too. So far I've done that easily with 3 separate tables called Ocean Freight Company, Customs Broker and Drayage Company. Als…

I have a raltionship that counts the number of pages with certain statuses. Similar to a cross tab report in the other database (excel) lol Job Table and Page Status Table. When I view one particular job the count does not appear to tabulate at all. Although the other jobs that use that same relationship tabulates fine. I've tried creating another relationship think the relationship is currupt and also creating a new record with no luck. I've changed the layout to show the related records in a portal with no records showing up for the one job, but the correct records on the other jobs. I'v ealso copied the key field and did a search in the related…

I need help. Two tables - ORDERS ("one" - every transaction by a customer) and ORDER DETAIL ("many" - each line item in each order, maybe 8-10 items per order). What I want to do is find, from among the ORDER DETAIL records for order "X" the one where ITEM NUMBER equals "Y" and return the value for that ITEM QUANTITY. In other words: For ORDER NUMBER 123, and ITEM NUMBER 321, the ITEM QUANTITY is 5. (And quantities for every other item as well.) Clearly, I can run a FIND fo these parameters, but I want the data to populate a form automatically The layout I'm using has (needs) every ITEM listed (we only offer 14 ITEMS each month), and I'm trying to po…

Suppose I have two tables, one (Table1) has only record and will always have only one record. In a layout based on Table1, I want to see every record in Table2, which is alot of records. I've done this a couple of ways. One was to take a number field in Table1 (containing a 1) and cartesian join it to a numeric serial id in Table2, which already exists for other purposes. The other way, is to put a "constant1" field in Table2 and do a simple equijoin between them (Table2 autoenters "1" in that field in that scenario) So, I'm wondering... which is better? Seems like at some point I ran into some weird and very slow indexing using cartesian join and that was when I…

Hey everyone. I almost hate to post this because I should be able to figure this out. Pretty familiar with FM now and have done conditional value lists before. I have an inventory DB. There are about 2 dozen different types of structural items and hundreds of different dimensions with specific weights. I have a table Inventoy and Lookup. Lookup has the type, dimensions, and weight. In the inventory table, have drop downs to select type, then specific dimensions. This would then be used by a calc to define the weight. Thought it would be easy but the calc is using only the first records weight. Check out my file. Any help would be greatly appreciated. matltrack…

[File A]-< [File B] How do you solve this? I have a basic one to many relationship that, when I look in reverse (from the many side) I'm seeing multiple records when I should only ever see one. Puzzled, I examined the relationship and saw nothing odd there. I examined the keys and they were normal. I looked for duplicates in both files and only one record represented each. So I created a new field in the many file (we'll call it file and remapped the relationship to that new key. I did a replace on the new key with the key data from the original. It worked, I was now only seeing what appears to be a one to one relationship (remember I'm viewing this from the p…

I have a small problem, im hoping someone can help me. I have a table of requests that are made to a group (they are assigned a #). there is a status field in this table that is an unstored calculation that looks at start and end dates to determine is the request status is open/pending/closed. I then have a 1 record table (called flag) that is related to the requests table. In this table i have a Status constant set to "open" that is related to the status calc in requests. I also have another calcualtion in this table that is set to the users groups # which is related to the target group number stored in request. Thus the relationship between the two looks l…

This seems like a topic that has probably come up in the past but I'll post it now since I can't see anything similar in recent posts.... Is there a way to make FileMaker display related records when the join fields are both empty? Ex: gData is the global source selector and the related table has a "Data" field in it of various values, some of which may be empty. When gData is empty, is there a way to get the relationship to show records from the related table where the "Data" field is empty? It would be nice if FM had wildcard special characters like * or ? to variate the relationship parent field to pull a range of related records when the relationship is a …

I own a small food business in which we change our menu every month. My database structure is: Customer >-< Order >-< Unit Meal >-< Meal Info -> Meal History (please see attached sample file). My goal: to be able to add a new order and be able to see only the menu items available for a particular month (and input quantities of meals). Thanks in advance for any help! RCB Food_Biz_Sample.fp7.zip

Hi I am a bit of novice with Filemaker Pro 8 and there is probably a very easy solution to my problem! I have two tables, "Contacts" and "Projects". I want to have the ability to list many projects to one Contact in a portal on the contact layout (I know how to create the portal!), I have two layouts, "contacts" & "projects". I have created a relationship between the two tables as follows: Contact Table: ContactID (Parent) to Project Table: ProjectIDfk (child). I created the field "ProjectIDfk" on the project layout hoping to see the "contactID" information in that field thus relating any project created to the client but I see nothing? …

Hi again, I'm having more relationship issues-the FileMaker kind, not in my life. My project is a kennel management system. I have most of my tables created and related the way I think they should be. However, I'm very confused when it comes to setting up for payments and deposits. I specifically can't see for sure how to do the deposits. Like a people hotel, a deposit is taken for peak times. It is then applied to the invoice at check out. I have charges related to Payments via a table called Payments_applied since multiple types of payments might be made (i.e. maybe some cash and some credit card). I have the deposits related to the Reservation. I have a fiel…

Hello, I am hoping someone can point me in the right direction. I am trying to develop a permitting solution for the Fire Dept I work at. We issue 14 different types of permits; two of which have 4 classifications each. Each type has some similiar information and each has unique information to collect as well. I would like to set things up so that the Fire Administration could change the pricing on permit type as needed. Each permit type also has a different look when printed (due to the unique information). I thought I was on the right track by setting a table to store each permit type, it's price, pertinent information (for printing), etc. and a second table for …

I've been using "The missing manual" book for FM Pro 8 as my guide. From what I've read, my application doesn't need relationships, but maybe I'm wrong. Here's what I've got. I've got one database that contains my company name and address, etc. A 2nd database that contains a contractors info and their buyers info. What I'm trying to do is generate form letters from these databases, different kind os form letters. So trying to make a report layout, I try to use info from the my company database, and the contractors info from the 2nd database. I indicated that the report (actually a letter) would be using the 2nd database primarily, and I have no problem ha…

I have a general FM relationship question: I have a one-to-many relationship between my table City (one) and table Contacts (many) so that I can select the city from a drop-down list when entering the address in the Contacts layout. I need your opinions about if the relationship should be using a Auto-Enter serial no field assigned to each City, or simply the City name ? I think, in a general view of database relationships (and please correct me if I'm wrong), that it would normally be considered more "efficient" to store in the Contact record the SerialNo field of the City (or any sort of unique and short value allowing to identify a unique record), instea…

I have a large list of customers in a relational database. When performing a find within this list, the higher numbered entries are not found. The data is intact and I can scroll to it, I just can't type in a number and go straight to it. Any thoughts?

OK. I have a purchasing database. The main purchase entry table has a unique ID field that is related to the line items table. It is also related to a coding table. So a PO may contain 5 items that are split between 3 departments. All of this is working fine. Here is my problem. In order for the purchase requisition to print with variable sized line items, I have to print from the line items table, but when the PO is split between multiple departments, I need to generate multiple purchase req's (to be signed off by different department heads) that show the proper coding information. But when it prints, I only get the data from the first coding record. …

I have a table that contains tree types of data. Let just say TYPE A B C.... I wan to create a layout that shows just type A's Just type B's or Just type C's. Now I can do this by creating a script, using Goto layer and preform a find for each of the types, and this works, but what I am looking for is a way to just return type A's without preforming a find? So if someone uses the cmd J 'find all command' it still returns only they type they were on. It seems that I should be able to do this with some type of relationship but I am not sure where to start.... oh on a side note: I have type A,B,and C in the same table because they are linked to a list…

Hello All, I'm just getting back into FileMaker after several years of not using it. I have taken on a project for a friend to try to help out and can't for the life of me make it work the way I want. Here is the situation. I will have two separate databases, the first will contain individual products. Lets say it will have fields of part_id, part_name, part_size and part_price. There will be several hundred individual parts, each with a unique id. This part I have under control. The second will be a quote and invoice database. In the body of it I will have an ID field the will have a relation to the ID field in the first db. It will populate the rest of…

I have a table that has a list of dates in one field (called "Issue Date") and a field called "Year". I would like the Year field to be a Value List and when a year is selected, it will only show the dates for that year. For example, In the Issue Date field will be dates 3/15/06, 6/15/06/, 9/15/06, 12/15/06, 3/15/07, 6/15/07, 9/15/07, and 12/15/07. When I select "2006" from the Year value list, then the only dates shown should be 3/15/06, 6/15/06, 9/15/06 and 12/15/06. If I select 2007 from the value list, then only the '07 Issue dates will be shown. Could someone point me in the right direction as to how to do this? TIA Kevin Kersey

So my found set consists of some 70 people I've phoned over the past couple of weeks. I'd like to bring my database up to date by noting that I've made these calls against each record in the found set. My database has two relevant tables: PEOPLE and ACTIVITIES. I can manually add an ACTIVITY (the phone call) to each of these people in the found set, but isn't there an easier way to do this for the entire found set than to go through each PERSON record and create a new ACTIVITY child record for each?

Hi-- I have set up several relationships between tables in my file. This is a songwriter's database. The first relationship is between the Songs table and the Discography table. I have added a field to check to see if the relationship comes up with a valid record in the Discography table. If(IsValid(Songs_Film_or_Disc::TITLE);"Yes"; "") However, when I go to a Title in Discography, the field is coming up blank (invalid). I have placed the Title related field from Songs onto the Discography layout, and the Titles match perfectly. I've checked the relationships and they're set to Title (Discography) to Title (Songs) What am I doing wrong? All My Bes…

I'm sure I must be doing something really stupi. I've got a very simple invoicing DB running on FM7 Mac. I have a script that simply selects a customer, selects an item to be purchased then switches layout and offers to print the invoice. No matter what I do with the script everything works apart from the customer fields (different table) in the invoice layout fail to show anything untill I close the file and then reopen it. Any clues please anyone. TIA

Ok. I've never done a many to many before. Here's my situation. I have two databases (this is from an older version I'm now trying to get to work together). The first is a songs database, with a composers songs that he did for various films, and other purposes, in the 30's and 40's. Then I have the films database. In each film there are one to several of his songs. While in Songs, the song can either be in or not in a film. What I'd like is to set up a button in each file which would find all the records related to each. So in Songs, the button would find the film a particular song is in. That's the easy part, and so that's now done. But to go from Fi…

I am stuck trying to relate a many to many relationship.... Can you help me? I have a project table which is the parent. Then I have a Location table but a single location can be associated with multiple projects. So I used a join table to create a record that links up the location with the project... works great. I also have an "asset" table that works the same way with the Project table (an asset can be associated with multiple projects). Then, I have the assets linked to the Locations with a join table too because each asset can have multiple locations.... That all works fine(I think). I have portals going between them all and it seems fine. Until I go back …

I have two files, one is really screwed up, and the other is in good shape. When I say screwed up, it lists voters with the wrong precinct, legislative district, senate district, and congressional district. Its literally like somebody threw all the voters into a hat and jumbled up thier information. What I would like to do is compare the correct voter file with the bad voter file and determine what percentage of the database is correct. The result will be something as simple as: Match = 85% The definite match in each file is address & city. What is questionable is the Precinct, Leg District, Senate District, and Congressional District. Also,…

I am currently working on a solution for a small restaurant, where the waitresses can enter their orders fast and efficiently. The idea is that they have one main entry screen (layout) where they can easily switch between clients. Food and drinks are divided into categories (Entrees, Main Courses, Desserts, White Wines, Red Wines, etc.) so you get a fast oversight of what has been ordered. For this purpose, I want to make filtered portals for each category. In fact this is a subset of the line items, filtered per category. So in portal 1 you can enter only the entrees for the client (with a related value list), in portal 2 only the main courses, etcetera. You get the…

Hello Team, I'm having what I hope is a simple problem. But I can't see the solution, as the behavior is not making sense to me. I have a database with the following fields DB = Students Field A = Primary Language Field B = Home Language The idea is that I can identify the primary language a student speaks, as well as the language that is spoken in the students home. While each field could have a different response, I want to use just one table to pull the data from. I use a table called "Language Codes". Each record has the language "code" (00 = english, etc) as well as the code "value" in plain english. The idea is that the code is …

I have an invoicing database with the following tables: Invoices, Line Items, Products, Customers. I have a standard discount field in the Customer table. Once I start an invoice and start entering line items into the portal, I want that standard discount to show up in the Discount field for each line item. We do this on each line, because there might be different discounts for different items on the invoice, and we want to be able to change some discounts manually, if necessary. I would do this with a lookup, but the customer table is not directly related to line items, except through the invoice id in Invoices. The current discount lookup in line items uses th…

I'm working on a project, that I think may be reasonably simple. But, with my super-newbie status in the world of FileMaker, I'd realy appreciate some feedback from people who actually know what they're doing. The project if for a sign company, they make vinyl signage, do car wraps, magents...that sort of thing. They currently have a website that has a shopping cart, with use registration and such. They utilize QuickBooks as thier accounting / job info package. Here is what they've requested be done. Quickbooks is quite expensive for multiple licenses, so they only have a couple, they don't need access to billing and invoucing information, but they wuld like to …

I have 2 tables. Table A with a relationship to table B. In the table A should be possible to chose values from field 1 in table B. So long so good. Now, i should bee able to chose, in table A, values from field 2 in table B. But only values that are related to values in field 1. In the same way i should bee able to choose values in table A from table B, from field 3, but only values that are related to field 1 and 2. Table B. Also, in field 1 a have manufacturers, in field 2 i have products lines, and in field 3 i have products. When I choose some manufacturer, in the second field should be visible (possible to choose) only product lines from this manuf…

Hello, I'm very new to FM although I do have extensive SQL skills(maybe that's why I'm not really getting anywhere with FM). Anyway, I have a database with a Parent-Child(one-to-many) relationship between two tables, one for Bank Accounts, and one for Transactions. On the transaction form, I have a button to execute a script. What that script does is to: 1. delete current record 2. go to related record(in this case, the bank account the record -- the record that has just been deleted -- belonged to) The script works fine when there is more one transaction in a bank account(I think it's because even the current record has just been deleted, there are sti…

I have two databases, one called "mailing" the other called "members." When someone is entered into the mailing database, they automotically get assigned a serialized customer id #. Then when we got to enter the person in the member database, we just put in the customer id # and their info pops up. I have a simple question - because both databases are linked together, do they both need to be open if you are making changes in one? My boss is under the impression that they do, but I know I'm pretty sure you don't. (Lol, please tell me I'm right on this one) Thanks for indulging a newbie!

I'm hoping that someone can help me with a student database I'm trying to enhance. All of the student information is already in the database, but I want to add information on a course that some of the students are taking in a way that it will let me track their attendance for each session. I started a new table for the course information that includes course date, faculty (each week a different faculty member teaches), and academic year since we'll have to be able to look up what year a student took this course in the future. What I'm having trouble with is how to relate the two tables so that I can record for each individual student which sessions they attend…

I've spent hours creating a DB to track inventory for a convenience store via purchase invoices. Once completed everything was working the way it should except I realized it was too time consuming when it came to entering Product information because 1)My Product ID's were different from my supplier ID #'s 2) My Product Descriptions were different from my suppliers 3) I purchase the same products from several different supplies . What I now want to do is to be able to enter the Product Id's of ABC Company or EFG Company or XYZ Company and have it always bring up My Product ID #. I've tried a few different ways but can't get it to work & I'm now becoming too confused. C…

[color:purple]Hi Everyone, I've tried to hunt down a very basic instruction for creating a relationship or more and though there's stuff out there, it's not basic enough to get me started. I'm so confused! I'm the sole administrator for a small school and I want to create a database to make life easier for myself (it's not working...yet!). I have students, I have parents, I have staff, I have classes, and all can intermingle. One parent can have several students and one student can have several (up to four) different parents and would always have at least two. One class can have two teachers and loads of students, of course, and a parent and teacher can even be …

table1: name calc_sum(table2::number) date weekoftheyear(date) -- text field, calculated result, always evaluate (for 10/2/06 = 40) calc_dayoftheweek ("2") table2: name number date weekoftheyear(date) -- text field, calculated result, always evaluate (for 10/2/06 = 40) dayoftheweek -- text field, calculated result, always evaluate (for Monday = 2) table1 related to table2 by name and dayoftheweek on each side of the relationship: table1::name, (Jim, for example) = table2::name (Jim) AND Table1::calc_dayoftheweek ("2") = table2::dayoftheweek ("2"), calc_sum(number) works great. However if I add in a further limitation…

I have recently come back to developing in Filemaker and have hit a bit of a roadblock in relation to filtering portals properly. This is most likely a fairly common issue so please forgive me if this has been covered before... I am currently building a project management system that is using a classic 'project to task' relationship between two tables. The tasks are associated using the project ID with no issues. Becuase our projects contain a very large nuumber of tasks I would like to use a tabbed interface based on the project with a tab set aside for each task status. (I.e. requested tasks, active tasks, deferred tasks etc...) When programming this t…

Is there an easy way to test for the existance of related records? Currently I create a field in the child that is a calculation = 1 I then create a calculation field in the parent that tests for the sum of the related records > 0 I would prefer only having to define a field at the parent level only if possible for a visual indicator of existing child records, and to use no fields at all if I am testing for child records in a script...

I need some help here. I'm a novice at FM and have hit a long-standing snag trying to develop a complex of relationships / calculations. I have a DB that records construction Change Orders "CO" (work in addition to contract work). Each change order has a summary section that records the following: 1) Original Contract Amount (easy) 2) Sum of Previously Approved COs 3) Contract Sum Including Previously Approved COs 4) #3 Plus the current CO amount First I have a Master_Table into which my data is entered, then I have a CO_Table that summarizes all CO information entered into the Master_Table. In the Master_Table I have fields that retrieve data (listed…

Quite Simply I am having problems making the following One Contact Table that hold Clients and Applicants (defined by Type) One table that holds jobs The Clients create a job in the job table In the Job Table it adds Applicants to the job So Jobs has many applicants Jobs also has one Client When I try to do this I do a join from Contacts to a JoinApplicant table to the Job Then when I try to connect the Contacts to a seperate ID in Jobs for the one client controlled job - I get a warning that i need to make a new table occurence... The problem with this is... In the Contact card i want to show all Jobs a contact has created a…

I am trying to develop a DB using FMP7.0 which will allow me to select a template of text with the use of a button. I have probably 20 different templates I need to be able to select from (1 per occurence) and have them displayed in a text field. I can't seem to get around this in terms of setting up the file. Any help is appreciated!

I am creating a database for the non-profit for which I work and plan to manage contacts via two tables: "Contacts" and "Organizations". Each has a serialized ID. While entering data in the "contacts" table, I would like to be able to look up an organization ID and add it to the "Organization ID" match field. If the Organization has not been entered, I would like to do so (I think that's just a "create record from...." thing with a portal, right?). I would also like to be able to do the opposite (look up Contacts when entering an organization). A simple drop down list won't do because there are already over 5000 entries in the "Contacts" table and several hun…

im using filemaker 6. hope this is not too silly a question. i have 2 files. one file is 'category listing', the second one is 'main'. there are 2 fields in 'category listing' called and . there are many fields in 'main' file which includes and , , . i created a relationship to link the 2 files such that in the 'main' file, when i choose a category, it will shortlist the various 'sub categories' under it only. this works already. however, i want to make it such that when i add records under the 'main' file, the category and subcategory will also be added into the 'category listing' file. pls advise! i need the solution rather urgently.. tha…

I have attached a sample file to work with in an effort to help with my question. The problem at hand is consignment / invoicing. Here is the scenario: Each item sold is unique and will never be repeated. With this in mind this is a many to one relationship to the invoice. The selling process involves: Item shipped to the dealer. Each item is listed on a “memo” which set up exactly like an invoice. The dealer has 30 days to sell the item or it must be returned. Payment is not made on the item unless the dealer sells the item. Payment is due 30 days from when it is sent out and not from the date they sold it. The dealer is invoiced when the…

Hi, I am working on a solution for the pet hotel I work at and am confused at setting up part of the relationships. First let me explain what I am trying to do. Then, I'll explain what I've done so far. The solution is designed so that it has Pets related to Clients. You can then add a Reservation for any of the Pets. For each Reservation, a Pet can be scheduled for various Activities throughout their stay (like walks and playtimes). At check-in, Charges are created based on the days of the Reservation and Activities added. At Check-Out, an Invoice is printed. Here's what I have. Pets are related to Clients. Reservations are related to Pets. Activities are …

I had a bright idea. And now the Owner wants it. I've spent 6 hours cutting down the size and complexity of this post. Please don't chuck it aside just because it's long ... writing Provisions to an Agreement, that a business owner can use to dynamically control their business (ie, the Owner writing the calc so-to-speak) is POWERFUL stuff. Allow Owner to write the rules to a Staff Agreement (define the Provisions) and then script will carry them out and pay commissions accordingly (depending upon sales to Customer - staff is assigned to Customer). He would create Agreement record, select the Staff, specify a StartDate - end date open unless subsequent Agreement with s…

Hi, I am witnessing a weird error that may be a Filemaker glitch. Setup: two tables, one is a parent relationship. The child has a container with a document in it. This table has 2 occurances related to different keys on the parent. One relationship is one to one. The other relationship is one to many. Basically I have one field for a report and another field for an invoice. The report is a word file and the invoice is PDF. Each record may only have one report, however each invoice will have many records. If there is only a PDF, the PDF is properly referenced. However if there is both a PDF and a word file, the PDF field is wrong. It opens up a ra…

I have a table that i have displayed as a portal for users to enter default rates for specific job descriptions. For each of these jobs i have a field for hour_rate, day_rate and week_rate. Now i would like the user to be able to set these rates once and have fields in another table lookup these values. Here is where i run into trouble. If i just do a lookup based on the relation of job description, i can only pull one of the rates because i have to choose a field to look up. I would like the user to be able to set the job and the rate type (hour, day, week) and have the rate calculated based on a relation. I don't know how to set up this needed calculation …

I am trying to set up a Contact database for my business. I have about 4 different types of clients. Should I make 4 different databases or Make one? Everyone has address and contact info, but each group has different calulated fields and additional information fields. Each group does not relate to the other. I want to target my marketing differently for the groups. What are the pros and cons of seperating the databases? Could I make a different form to fill out for each group type and use the same database? What should I do? HawkLA

I have a feeling I know the answer already, but I'm hoping there's an easy way around this: Can I create a relationship with a specific repetition of a repeating field? What I surmise is, NO....that it will include all repetitions in that relationship, correct? Is there a way around it then? I'd like to match only specific repetitions rather than all repetitions. Reason being, I want a specific relationship to each repetition and only that repetition. And I don't want to have separate fields for the data in each repetition...it just gets cumbersome. But I have a feeling that's my only option, huh? Sorry if I'm causing any confusion (I tend to do …

Hi all, Am a bit confused by this... I have 4 tables, the first being the main table The key field in all tables is "serialno" I have the following relationships setup MainTable serialno -> Table2 serialno MainTable serialno -> Table3 serialno MainTable serialno -> Table4 serialno Against all these relationships I have ticked the option to allow creation of records in the child tables via the relationship What I expected to happen was that I would create a record in MainTable with for example serial number 123456 then when I went into the subsequent tables 2,3 and 4 there would be a record in there with serial number 123456, …

I know I'm probably asking the same questions as everybody else but whatever... here it goes I'm another confused newbie that needs to be bailed out. I work for a non-profit environmental organization that has three databases of information. Each database contains specific information about the work that the people listed on the contact section of the database have done. What I would like to do is create a fourth master list of contact information that contains all the contact information of the people in the databases. Also I would like to create a radio button that states whether or not the specific contact would like to recieve an e-newsletter. What I wa…

I am trying to make records listed in a portal clickable, so the user can bring up a screen of the form entry for a corresponding record. It seems to work, except that no matter which record in the portal I click, it always brings up the FIRST record in the list. What am I doing wrong?

... i can't figure them out. i haven't made databases since FM6 and i can see there's been some changes. anyway, i can't connect a "client" file with a "job ticket" or "invoice" files - the "table" graphic is mind-numbing, not really sure who's bright idea that was (hope not yours.) anyway, i know i'm just missing a step or 2 - any help out there?

Hi folks I've trawled through the forums but I can't see anything on this topic that I can relate to my circumstances. I've created a job tracking DB. Currently, when I create a new job I then add it to a related work schedule manually. I'd like to know how to get the DB to automatically add the new job to the schedule once it is created. Each job record has a unique reference number. Currently, to add a new job to my schedule I create a new record in the schedule then select the job reference number I want from a drop down list. This populates a portal in the schedule record with information from the job record that relates to scheduling. I hope that mak…

Hi, I have a data base where two tables are joined to each other in multiple ways. My problem is that on the layouts, I often want to display the names that go with the IDs(fk) of records from related tables. I'm finding that in order to display the name, I'm looking for it is sometimes 3 or more relationships away. So I have to create intermediary TO's and rejoin them to the layout TO. Is this the proper way of handling the display of data when you are frequently repeating the same relationship but from a different TO. Example: Ingred Table w/Type RatioID fk joined to Type Ratio table w/Type ID fk joined to Type table w/Unit ID fk …

What I am having trouble doing is that I want to transport data from one database to another using relationship. I work in an insurance office and when we get clients who are in a pending status once they become approved I want to move them from my pending database to the clients database by just pressing a button, but I can't find a script anywhere that can do that and I am such a noob that I haven't even figured out making scripts yet (but got good books to read). If anyone can help me I would love them long time. Thank you, caseyuptobat

My served, multi-file inhouse invoice solution has been converted from FMP 6 to FMP8.5. All navigation works fine. Bad file references that were crashing FMP 8.5 have been repaired. Now the problem is many scripts with relationships are not working and I glean it's due to the new Commit Record step and Refresh Window Flush Cached Join Results step. Also possible, the converted scripts now show $ where there may be a conflicting symbol and now may have erroneous parenthesis, although I see no conflict but need to research this some more. Question(s)- There are many Set Field steps. Should there be a Commit Record step after all or most of these, now? Can…

my files are bigger but here's my example I have a .xls file with numbers 1 to 10 in a1 to a10 I have another .xls file with 1 to 20 in a1 to a20 I import both in filemaker pro and create a relationship. This is where I am now. I want to be able to see the items on one spreadsheet that aren't on the other. Or, I expect to see 11 to 20. I apologize for the pretty basic post, but the instructions aren't very clear on this.

Hi, I've been building a db to hold information about our companies product. We are a research and devlopment company, and we need something to keep track of our product. It's been split up like this: Component: smallest possible piece Sub-Assembly: any combination of two or more components or other sub-assemblies Parts: Largest assemblies, all parts will snap together to make product. so, Sub-Assembly has many components Sub-Assembly has many sub-assemblies Part has many components Part has many sub-assemblies. The product is not huge, with about 70 unique components. Now my problem is I want to start layering my relationships to ge…

I have not found the answer to this elementary question in the forums. A calculation field with global storage references a field in a related table. The calculation is simply a sum over the related records. The calc field is on a trailing grand summary part of the layout. The problem is the calculation does not re-evaluate when the data in the related records changes. I have abstracted the problem from my project into a very simple test database (attached.) On the layout Table Two enter some new values in data1, data2, or anumberfield. On the Table One layout trailing summary there are two versions of the calculations; one global, one not. The non-globals will re-ev…

Master is the main file in my FM suite. Sometimes I go to another file in the suite and when I press the button to go back to Master I get the message, <"Master file:Master" could not be opened. (Not Found)> I, of course want to scream at it. But instead I use (and instruct my users to use) the dialog box to tell it where the file is in spite of it already being open. Any clues?

Hello everyone, I'm having my first go at building a relational database, and I've having a bit of trouble wrapping my head around it. I have two tables, one called people and one called events. The people table has several fields, including a unique ID. The events table has the following four fields: date (a standard timestamp), eventType (one of six predefined values), personInvolved (the same unique ID from the people table), and note (an arbitrary text string). I have a layout that shows records from the people table, and I'd like to be able to show, for each person and for each of the six eventTypes, a textbox that will print the most recent …

I'm displaying a list view of records using FM 8 client which connects to FM 8 Server over internet. If in the list view I display the fields that only come from the layout's table everything is fine...but as soon as I display a field from a related table the speed goes down significantly. Why is filemaker handling this so badly? Is there a way I can optimize client or server to fix this problem?

Hello, Right now I am working with two different databases, one called "membership" and one called "mailing." These two databases were created independently from eachother by a former employee. As of now we have some of the same people in both databases (all of our members are in our mailing database, but not all of our mailing contacts are in our member database). Because they aren't linked, we often will look someone up in our membership database, only to find that they have a completely different address listed in the mailing one, which frustrates everyone to no end. What would be the most simple way to link our member and mailing address fields so that when…

I'm not sure this is the right terminology for what I'm trying to accomplish, but is a self join basically replicating a table occurance alowing me to relate to the same data in a different manner? Anyway, I guess I'm trying to accomplish what I think would be a many to many relationship within the same table, or in other words I want to see an individual record in form view as I select it from a portal containing all the records within the same table and on the same layout. Or is this doable?

I’m working on FM Pro V7 for the Mac, and have a TV Pitch Database that I wanted to add capability to. I have this table within the database named: “IdeaBase” within this table are all the fields that I need to enter in each idea, such as: Idea_Name Idea_Genre Idea_Length Idea_Status and so on… I want to add a report layout to the database that will give me a master view of all the pitches, and their various status. I want this layout to be able to: 1. Sort the layout by Idea Genre 2. Under each new Idea genre, give specifics of each particular idea (there’s usually several under each genre. 3. Allow for a global “connections” field …

I have done a variety of DB solutions, all of which have served the intended purpose. However, I am largely self-taught and know that my structures aren't always the best. I am starting a new solution and would like to do it right from the beginning. Anyone mind offering how you would structure the following: I am tracking student enrollments in online classes. Students from 6 different school districts are taking online classes through my program. The curriculum comes from 3 different vendors. I want to generate a variety of things, including reports based on the courses, the districts, and the vendors. (How many students took Course A?--How many s…

I have a client table and a zip code table. The client table has the following fields: residential zip code, residential city, residential state, business city, business state and business zip code. The fields are used to allow both residential and business info. for each client. The zip code table contain zip code, city and state fields obviously. My problem is that when I create a relationship between the zip code fields of each table and try to create a lookup for the residential city, state fields that also changes the business city and state fields, obviously. How can I create separate lookups for both business and residential fields using one zip code…

I have a recipe a shopping list database that is working okay. There is: Parent Table: Recipes Child Table: Ingredients with fields: ID Quantity unit (fk) ingredient name (fk) Converted Quantity Converted Unit (fk) the units are stored in a separate table with fields: ID Unit name type (mass,volume,count) ratio of unit to mL or g So when you enter an ingredient a base Quantity/unit and then when you enter a unit to convert to the ingredient record calculates the conversion Quantity to go with the new unit. I use this Quantity to sum up on the Shopping list. That works okay. The problem is with the types of ingredien…

Hello, I understand the basics of building a relationship between two tables in the same file. My project: 1. One table has country/location entries (Costa Rica, USA, etc.) 2. Another table is used for data entry. Would like to select country via drop-down in this table , and have the appropriate locations displayed as drop-down list. In effect, I believe it's a relationship returning a list of values rather than one distinct value. Any insight would be appreciated. Sarge.

So I have two tables: -- CONTACTS contact_id contact_name job_title COMPANIES company_id company_name website contact_id -- Here's what I'd like to do. I'd like to create a COMPANY record. Then, when I'm creating a new CONTACT record, I'd like to select from the list of available companies via a drop-down list. Once a company is selected, the field companies_website will display on my contact layout. I'm sure this is really simple – I can do it in HTML & MySQL pretty easily - but I'm not sure how to go about this in FM. Can somebody please help me?

I have an application that needed 20 table of occurances of the same table. I just wonder whether there is a limit to the number of TO ? If I have that many TO, will it slow down the system or more prone to data corruption?

Hi everyone, I have 3 tables in a database, what i want to do is to make sure that when data is enetered into either one of the 2 other tables, a serial number is auto-generated that will never be the same. now the tricky part. Whenever new data is entered into either one of these two tables it will automatically create a record in the last 3rd table matching to the unique serial number auto generated in the other. Basically im trying to keep track of product information from 2 differrent suppliers and then combine them into one master product table. am i going about this the wrong way? please help ive been at this for 2 weeks and its driving me nUtS

The main table is AWARDS. The related tables are PERSONNEL and DOCUMENTS. The primary key is the field LOG NO. When an AWARD record is created, a temporary primary key is assigned, TEMP100. Related PERSONNEL and DOCUMENTS records are created. A few weeks later, a permanent LOG NO.(primary key) is assigned to the AWARDS file. Q: How do I change the primary key (LOG NO.) in all the related records when I change the primary key in the main (AWARDS) file? Right now I am struggling with a lengthy script that uses a global field (gOldKey) and finding each record in the related tables and then executing a REPLACE command. Thanks for your help!

I want to produce a field that automatically calculates the distance between two locations based on the postcode. The two loctions are in different databases (client & candidate). Problem: How do I create a field in the candidate database that will look up a value in a third database(postcodes) based on the postcode field in the candidate database? I have created the database for postcodes but need the field in the candidate database to lookup a value based on his/her postcode. basically i need a calculation that will tell filemaker to " use the postcode field in this record in the candidate database to get a value from the postcode database and put t…

I have been thinking and reading extensively but cannot figure out how to do consignment sales. Each item being sent out on consignment is unique. No two items are ever alike. Any good places to read a how to on the subject would be great. Any post at all about this would be fantastic. Thank you in advance. Drew

I have a database in FMP 8.5 to keep track of class action lawsuites. There are tables for Plaintiffs, Defendants, Cases and other things. The Claims table links a specific Plaintiff and Defendant combination together in a Case. Claims can sever from one case into another and I need to track that. To sever a claim I essentially duplicate a claim and change the case it's in and then change the origional and new claim to link to one another. Then the origional claim's status becomes severed. The Claims table has: Claim_ID Plaintiff Defendant Case Claim_Severed_To Claim_Severed_From So I created three table occurrences for the Claims table: Claims,…

I've created a database (example attached) with a primary key (Contact ID) and several foreign keys (Adobe ID, Quark ID, etc.). In all but the Hardware table (one user with several computers), the relationships are one to many (More than one user might be using the same license - site licenses for example). I've created portals in each of the layouts, but I'm trying to figure out if there is a way to bring up a particular Contact and assign the foreign key to them. Each table also has Contact ID as a foreign key - and the only way I can think to make this work is by creating a relationship between the primary Contact ID and the foreign Contact ID. If I do that, howe…

Hi there... I need a bit of help with this....pelase... We have products in a database. Some are "variants" based on color or whatever. These variants show up in a portal in each of the related records. What i want is this: merge some fields of these (related) records automatically in a field in each of these records...in other words: populate one calculation field with the content of (multiple) related records! Can this be done without scripting?? Thx in advance. MBahnen, Netherlands

Hi guys, I am teaching myself FM, and I have a sticking point right now. How do I auto insert a foreign key from another table? Say I have a customer details with a unique ID, then I jumped from customer layout to order layout. Then I want to create a new order, clicking a the new record button, then I want that FM automatically fills in the customer ID (foreign key) with the active customer ID. How is it done? I have relationship set-up, but it does not auto fill. Thank you in advance. Regards, Martin

Hello, Maybe im being a dumb ass here, but for some reason i can't work this out. In FileMaker 6 i had a relationship for several of my clients that used a dumbish multikey The Multikey was in the contacts database and consisted of several variations of the contacts name and company name. The Multikey was related to itself. The Portal on the contact record displayed other addresses for the contact and other people at the company they worked for. (very useful for testing for duplicates or quickly seeing other contacts at an organization. When you clicked on a portal row it took you straight to that record. Question: Now that self-rel…

Not sure how to approach this one. A political candidate has been doing surveys over the last few months. In one district they asked one series of questions, in another district they asked another series of questions. Mostly the questions were same, but sometimes it 5 questions..and sometimes it was 7 questions. I looked at other posts on handle the situation and I'm not sure if I should set up two tables, one for questions and one for answers. Because I need to be able to show results on a per voter basis, I don't want to display/export a littany of questions that don't have a response for them. If anyone can get me started I would greatly appreciate it. …

Hi all. Geez what a learning curve. Thanks for your help. My eyes are buggin from trying to figure this out. I need a field in my bps_Contacts.fp7/Form View that relates to another record in the same file to track my client referrals. And I need to track that on both sides. I need to see who referred a new client and I also need to see all of the referrals any one client has given me. I guess that would require another portal in the Form View similar to the portal that lists all the invoices created for each contact, but from the same file as opposed to the Sales Orders file. Is this too big for a fairly right-brained beginner? Thanks

Hi everyone, I recently moved one of my solutions from FM 6 over to FM 8.5 and I'm discovering a whole new world. Almost everything is working except a relationship I have set up for a field sitting outside of a portal. So the field is supposed to show just the first record of that relationship, correct? Well the field is showing a result even if there is NO data in the key field, and even if there are no records in the related file for it to match! The field in the related file, let's call it Appt Status is a container calculation that calculates the appt status and returns the results of one of several global containers that have a color in them. So if th…

I have 3 tables parts, orders, jobs. Orders takes info from parts. Job takes info from orders. relationship part1,order1,order details works correctly i have made copies of the relationship to make more instances of the fields but only two out of the six work but they are exactly the same except the names???