Relationships

Hello I am a brand new user so bare with me. I am creating a database for stock research and every one of my relationships is many to many and I am having trouble getting my head around the relationship concept. If any of you could help out i would really appreciate it. Here is a simplistic version of what i have: Stock Research A: Ticker Rating 1 Rating 2 Rating 3 Stock Research B: Ticker Rating 1 Rating 2 Rating 3 Client Account: Ticker Name Account Value I am linking the tickers from the two different rating services to the ticker in the client account but they are many to many relationships. I am not sure what ki…

Hi hope someone can point me in the right direction. I am a surgeon trying to create a database where the options available in field B change depending on the choice made in field A For example: Field A has a pop-up list of limbs (right arm, left arm, left leg, right leg) I want Field B to be a list of available anatomical areas (Shoulder, elbow, forearm, wrist, hand, fingers) if "arm" is chosen in Field A or (Hip, thigh, knee, leg, ankle, foot) if "leg" is chosen in Field A Thanks for any help

Hello everyone! Once again, I have ran into a small problem that has now become a major hold up in the design of my database! I have a table labeled [color:red]Source Codes, which identifies the payment entries origin. There is a [color:orange]source code(a mixed line of letters & numbers not exceeding 15 characters) and there is a [color:purple]source code description(a description of the code itself, the only thing all data entry users will see). There is another table labeled Payments, which is where the data entry user records members payments. In this table I have place a field for [color:purple]source code description. However, in doing so, the user was una…

When I go to preview my reports I have a long wait time waiting for it to process the data. I imagine I've not configured my data /relationships in-correctly. I have basically 4 tiers of tables in my relationships with the bottom tiers calculations being passed to the upper ones. All being additionally processed at each level. What can I do to help eliminate some of this processing? Thanks

We daily run automatic email notices. I have a table called ProjectSchedules (ProjSch). It contains one StartDate and, if it is Substantial Completion date, there is the unique ID (CapReq) in the SubstComplID field. If it is Construction Start date then the unique CapReqID is in the ConstStartID field. I have a table of email notices. If Substantial Completion date falls within the date specified (today plus the global), then I need to send an email. I can ignore it if an once an email for that purpose and that ID has been sent. Substantial Completion is called eCode H and Construction Start is called eCode G. So if CapReq 1 has already been sent an eCode H, i…

I have a DVD table connected to a Actor table in a many-to-many relationship table which are joined by a ActorDVD table in the middle. I have made my DVD layout with a portal to my Actor table to show what actors are in the movie while browsing through the DVD records. My problem is when I create a new DVD record, I want to be able to add actors to the DVD without creating a new record of actor. When I type in the actor that is in the movie, I first want it to choose from the existing actors in the Actor table and then if not found create a new one. So far done the value list and stuff but it always just makes a new record of actor. I am new to using this and was …

Hi, I'm sure this sort of thing has been covered several times and if someone can paste a link to a previous relevant topic I would be very grateful. I'll use the actors / movies example. Within the same database if I have 1 table called MOVIES and another table called ACTORS where in both tables each record has 1 actor only and 1 movie only respectively... Then, is it possible that in each Movie record, I can enter what actors were in the movie, referencing the Actors table. I can't work out how to do it.

My first post, so bear with me. I'm a Doc who has been using FM for the past 5 years to do my patient records. There are several tables in the DB that relate things like medications, tests, other physicians, and individual office visits to a core table (Patient Core Info). I use three portals on my "Office Visit" layout to display medications, recent tests, and physician relationships. Although the portals reflect correct information on the day the office visit is created, every time I change a medication it shows up as a change in all the old office visits as well. What I need, I think, is a repeating field that would be populated by the "active" medications on the day …

Hi, I'm working on a business application that will be used to run the day to day processes for two different, but integrated companies. To the end user, we wish everything to be separate with the exception of the invoicing table (which should run consecutively according to the entries from both companies). My initial thought was that this could be accomplished by filtering the tables according to a tag on each entry (i.e. every entry is tagged whether it "belongs" to company A or . This has the advantage that it would be easy to maintain entries for both companies (although this is not a requirement at this stage). The down side is that maintaining consist…

Hello, I have a table of this format: CATEGORY_NAME CATEGORY_ID PARENT_CATEGORY_ID Is there an elegant way to have a text field in each record that will show the names of it's parents, grandparents, great-grandparents, etc., all the way to the root? For example: "RootCat > SubCat1 > Subcat2 > Subcat3" I tried some obvious (to me) methods with selfjoins, but I couldn't get it to display more than 2 or 3 levels deep. I found an example solution that did what I want along with many other things, but I think/hope it is overkill. Any tips would be appreciated.

So here is the situation: I have three tables: Customers Contacts Units Customers table is a parent to both contacts and units. A contact may or may not have a unit/units associated with them. A unit will always have one contact associated with them. I have tried laying this out in a relationship graph and have been unsuccessful thus far. Am I suppose to use a join table or multiple table occurences? I am at a loss of what to do. Any help would be greatly appreciated. Regards, DM

Hi guys, i'm making a standard order/invoice solution and I've had some problems: I made a layout to print the order but in my order I use to different portals: one for 'rent' and one for 'services'. How can I make the print not to display the portal rows that are empty? Thanks guys.

Need some thought on relationships: Situation #1: I have two tables -- I have a pull down menu on the parent table Called "Table1"and information on "Table2". Do I join by: Table1::Name -- Table2::Name Pros: Easy to do -- Works well when I add a listing it shows up in the pull down list Cons: When I change the names on table2 it does not change the name on table1 Do I join by: Table1::recordID -- Table2::Rel_RecordID Pros: Also easy to do. Cons: Makes multiple entry's of the same name? Is there another way? Thank you

Sorry for the subject; I couldn't imagine a clear subject that wasn't as long as the problem. Short of a calculated field, is there a way to use an attribute of a grandparent record as a relationship component between the parent and child? In my situation, my parent record is a Customer record, the children records are Deliveries, and the grandparent is the Subscriptions record, which has a primary service field. Each delivery record identifies the service it represents. I am trying to get all the Delivery records for one customer that are the primary service for that customer. For example: Customer C uses Subscription 5. Subscription 5 has Primar…

Hi, I'm new. I want to print an invoice type report. I go to the related record in browse mode. This is working fine. When I go in preview mode, it shows me all related record. I just want to print the related one. How can I do it ? This is the script that I use Go to Layout ["p_Invoice" (Invoice)] Go to Related Record[show only related records; Match found set; From table : "Invoice" ; Using layout : "p_Invoice" (Invoice)] Enter Preview Mode [] Pause/Resume Script[indefinitely] Go to Layout [original layout] Enter Browse Mode[] Regards, Riko

I used to create related records in scripts by recreating what a user does - ie going to the last row and entering data. It's always thrown up problems from time to time. Then I started using this, from Comment: But this leaves you with the problem of recreating the state of the screen you left, eg putting all the tabs back as they were. Does anybody have a solution that is the best of all worlds: simple, not dependent on layout features, IWP consistent, persists the state of the layout the user is on?

Hi, I have a group of unlimited products, the each group group_id SKU sku_order 1 AJ 1 1 OK 2 1 LM 3 I want to create a key for performance reasons for each unique group which is the concatenated SKUs so the key of group one would be AJ OK LM But to me the sku order is just a cosmetic issue, for my use LM AJ OK would be the same so I'd like to generate a key that's the same regardless the order. so I need a trasnformation that would give the same result f(AJ OK LM)=Key f(LM AJ OK)=Key Key=Key

Hi, I was wondering how you guys manage taxes on products in a database. Here's a common scenario A billing system which manage invoices and products. Facts Some products are taxable others not Some states (provinces) can have different taxes on products. (Eg : In Quebec, we have two taxes on product) Taxe rates sometime changes overtime. I could add more facts such as tax on tax, but I will keep the post simple although Canadian taxes are quite complicated. Thanks for any opinion.

I am working on 2 databases and I am trying to share the data within each of them. In the first database, it basically tracks incoming items (called an inlog). It's a very simple database as the fields are date in, date out, from, to, title, form-type, comments etc. In the second database, it will track grants. So if a grant comes in, it will be entered in the inlog database first (with the form-type of grant) and I wish to see these entries in the grant database (but not the other types of forms that were entered in the inlog database). How is this best accomplished?

I'm looking for the easiest way to copy a relationship graph from one file to another. Thanks

Hi, How would you conceptualize a zero-to-many self-join? In my case, I need to create a database that contains cities. Some cities have boroughs others none and some boroughs have areas and others none. Do I create three distinct tables or I create a single table with a parent field in it?

Most of the databases I create have been for scheduling, services, jobs. Generating stats etc.. Now I have an invoicing database I'm working on. Invoices, parts, customers all work well. Now I need to add a payment system so they can apply payments to multiple invoices. I created a payment table, to store check #, amount, reference. then I created a join table to hold the invoice# and payment id to break down the many to many relationship. I'm having a brain freeze trying to create the layout. So I can take one payment of say $500 and apply it many invoices. I just need an example file so a light bulb goes off. Can anyone point me in the right direction? any help is…

Hi everyone I am trying to write my first relational database and after reading a couple of books I am aware some of the principles but not sure I really understand them. I am a plastic surgeon who deals with trauma patients. I am trying to create a database of patients we have treated. Each patient may have several wounds and each wound may undergo several procedures both in one sitting and also over time. A collection of procedures make up an operation. Thus a wound may have several operations (many to many) and I have resolved this by creating a join table called "plastic surgical procedure"- I think this logic is correct - but I would like to know it is!! …

I have a little project that I could use some expert help on. The project file is here - you'll see I've got a few tables and a few records. I want to be able to populate the String table with select records from the Transcription table. Can someone help me get started in the right direction with this? Thanks in advance! Grant CLIP_DB_copy.fp7.zip

This is my first posting and I hope someone can help. I have a database with individual bird species, linked to this table is a list of photographs – obviously there are potentially many pictures of each single species. The aim is to select the photographs for use in different publications – some books will have multiple photos of a single bird, while other books need to be limited to a single image. I have linked tables for each book – there are two tables per book – one holding the text, the second the picture selection. The problem I have is I sometimes want to limit the selection to ONE image for a book. When a user clicks a selection I need to: (1) …

Hi, Small question here. Is the anchor buoy relationships method is better than the normal "spiderweb" method? Usually, when I develop a database I use the normal method, but I stepped on the anchor buoy that seems to apply mainly to FM world. Is that method portable to other database system?

Dear Forum members I am trying to grasp the following problem/concept and have been wondering increasingly how I can best achieve it. I’ll try and explain as best as I can. e.g I have a table called expenses which lists all the expenses as at a certain date. Then I have a table called Income, which lists all the sources of Income as at specific dates. Table 1: Expenses Fields: Exp_ID -- Date ---Description -- $ Amount E1 -- 30/11/2008 – Charges – 10 E2 – 30/11/2008 – Fees – 20 E3 – 30/11/2008 – Internet Fees – 5 E4 – 31/12/2008 – Internet Fees – 10 Table 2: Income Fields: Inc_ID -- Date ---Description -- $ Amount I1 -- 30/11/2008 …

I work for a company that makes road/flight cases. Almost 20 years ago I wrote some software using Macintosh HyperCard running on Mac OS 8.6. You enter the 3 dimensions in inches plus the cover height and it calculates a cut sheet, materials and labor pricing. Time is long past to remake this application run on our new computers (before our old macs become extinct out from under us), and FileMaker seems to be the platform to retool to. My question is how to adapt the Hypercard concept into a FileMaker world. In Hypercard there is a stack for each of 8 different case types we make. Would it make sense to have 8 tables in Filemaker for each case type layout, or …

HELP! Self-taught Filemaker user. I'm doing really well so far...but struggling on grasping the relationships. I understand what they do and how they work, but I'm missing something when I set them up. I have a database of prospects for our school/church. We make "calls" on these prospects--either by phone, E-mail, or personal visits. I established three entity tables: 1)Prospects, 2)Calls, 3)Caller (person making the contact). I want to have one of my tabs on my Prospects layout contain a portal that shows all calls made on that contact, and who it was that made the call. I don't really need to see the layouts and records for calls or callers, just…

Hey all, I seem to be having a bit of trouble with a report I'm generating from FileMaker. I am trying to make a subsummary report, but it does not display properly. It is based on the Supervisors::Name field. It repeats some values, and omits others, seemingly at random, based on if there are multiple supervisors listed. It is a multi-key field Attached are screenshots of the report and my database structure. Hopefully this is an easy fix, it's been driving me nuts for weeks...

Dear Forum members I am struggling to implement the following. Tables: Table 1: Shares (Products) Product ID -- Name [color:red]P1 -- BHP Billiton Table 2: Details Share Prices as at different dates and also details the Price Source. fPID-- Date – Price – Price Source [color:red]P1 – 31/10/2008 – 10 – Yahoo-finance P1 – 31/10/2008 – 10.5 – Reuters P1 – 30/11/2008 – 10 – Yahoo-finance Table 3: Price Sources Ranking For a certain share (e.g P1), the user can select the ranking of price sources. That is, given several price sources at one date, the final result (table) should detail that price with the highest ranking. E.g Lets say th…

I'm a beginner with FM, so please bear with me. I am trying to set up a seemingly simple DB that tracks contractors and projects. What I would like to do is being able to assign Contractor A to multiple projects. But on a second layout, I want to be able to select a project from a dropdown list and then automatically see all the contractors that have been assigned to this project. For some reason I am not able to structure this correctly. I always end up with some circular reference. Could someone please point me into the right direction? Thank you!

Howdy, We want to be able to track various events like incomming email, phone calls and fax receipts. I've got two tables; events and contacts. Contacts holds all of our contact information. Events has a "To:" field and a "From" field. Try as I might, I can't get the "From" field to display properly. It wants to show the same information that is in the "To" field. I've attached a little test file as an example. We don't want a look up field as the names in the contact table sometimes change (misspellings and incomplete names are typical). Any shoves in the right direction are greatly appreciated. :P events.zip

Ok, I don't even know where to start. I am a tech guy that has been helping out a school update their 15 year Band Camp file for the past 8 years. I don't use FM except once a year, I set up a file for them and it was working find. Last Wednesday something happen, they had 176 students then in the Account file they had 70. I was able to save the 176 students as a excel file. I tried to clear the files and import the data, however it doesn't show up right. I was able to get it to sorta work by entering the data manually. There are three files, one is the Band Camp File where you enter the data on each student. Then there is a Band Cam…

Can anyone suggest a way to implement row journaling? I.e., keeping past versions of a given record accessible but never displaying them, unless for some reason I want to go back and examine long-discarded changes. I can already do that by consulting backed up versions of the whole database, but it's hard to know which backed-up version to look in for something I want to find. This database is being used to compile dictionary data. There are two main tables: "headwords": contains individual words that have unique spelling, together with information about their frequency of occurrence, sources that contain them, and graphic variants. "definitions": contains d…

I have a bookings table that has active and cancelled booking. Cancelled booking are shown by a date in a 'booking cancelled' field. I'm using a relationship to join a table of incremental dates to give me a display of booked a free date, which works well but I can't seem to hide the cancelled bookings. I need to keep them in the table in case of any query. What's the best way to remove these records? TIA

hi , what i want to do is, i want to show particular edit box after selecting something from dropdown ......plz tell me how can i do so .

Hi All, I know that questions about genealogy are common, and I have searched the forums here for problems similar to mine. I have also read mr_vodka's page on recursive relationships. However, I am still having problems in that I cannot get my head around what I need to do. Let me explain: I study chimpanzee mating behavior. I have a table called "Copulations" (sex, basically). This table contains the following fields: Date (of copulation) Time (of copulation) FemaleID MaleID Parity - has the female ever had a baby before the day of the copulation Pregnant - was the female pregnant on the day of the copulation I have another table title…

Hi All, Been away from FMP since v8. Just bought 10 Adv for a work project and getting used to the Relationship diagrams. Need some help on proper design for this project. Incoming file has 500 lines, each line a value to define a set of insurance benefits (individual deductible $, family deductible $, Out of Pocket Max $, like that), let's call it the benefit file. Not all values are relevant on every file so I created a table where each line equals a record. That way I can have a self relation to view just the records that are relevant to that file or any other filtered set of values I want to view together. So far so good. Now I need to create another ta…

Hi Filemaker Forums! I’m building a big database for an elementery school. They want a database that can hold all of the grades from all students for all classes. Now the main relation is pretty simple: STUDENT: StudentID, Name Student, Group name, Name teacher linked to COURCE: StudentID, Grade1, Grade2. Both of the StudentID’s are linked togetter. Now the main problem is dat I get to see all of the students in a table of a random cource. But all of the groups of students have different cources. So in the table “math” I only want the see the children with the “group name” 8a, 8b, 8c. And not everyone. Is this Possible in FM Pro 10? Or are there any…

I'll try and keep it brief. I have a 'Database' and a button on that database that once clicked then writes a record in another database named 'Reports'. So far i've managed the script to take the name and email address fields and the title of the button in question being clicked and write the info into a new record in the 'Reports' database. This all works and launches the 'Reports' database and then puts a cursor into a 'Comments' field in the 'Report' database to add a couple of lines of text pertaining to that record entry. Issue 1: After i've entered the couple of lines of text into the 'Comments' box i want to hit the continue button on the script…

Hi all, self learned beginner here. I created a quotation tool where I will actually quote scopes of work, amount of hours, materials required, etc for customers. I have the data entry 95% complete. The problem is with the Bill of Mateiral (and others I will create later). I have a seperate file called Product Catalog where I will import thousands of records eventually. What I need to do is when I input a part number in the "Part Number" field in Quotations to look in the Product Catalog file, find that part number and auto fill in the Manufacturer and description. I've read and read and did alot of different things but I think this should be something s…

I'm trying to set up a self-defining portal and I'm having some difficulty. I have a table ("Recipients") which has a text field ("Name") and three date fields ("Nomination Date", "Award Date", and "EMPTY"). The "EMPTY" field is just that: empty, no data in it at all, and it is a global field. "Recipients" has two relationships defined. The first is with "Recipients 2" which relates "Name=Name". The second is with "Recipients 3" which relates "Name=Name" *and* "EMPTY≠Award Date". Portal #1 on my "Recipients" table points to my first relationship, thus giving me a list of all awards that a given person has been nominated for and/or received. This works fine.…

I got 5 tables (Picture, Event, Consequence, Cost and Login). The table picture has some container images (not global). The relation between Picture(serial key) and Login(serial key) is an "X". The relation between Login(serial key) and Event(serial key) is an "X". The relation between Event and Consequence is a "=". The field "_fkEvent" in the table Event is linked with the serial key of Consequence. The relation between Consequence and Cost is a "=". The field "_fkCons" in the table Consequence is linked with the serial key of Cost. My problem is that my images in the table Picture does not show up when I'm in my table Cost but it does in Event or Consequ…

Hi ! I just cannot understand why this portal doesn't work: I have 2 tables and a relation between them: gAnnounced = cStatusAnnounced and cUserID = tUserID In gAnnounced there is data "Announced" and cStatusAnnounced also contains "Announced". Both tables present UserID's correctly. gAnnounced is global cStatusAnnounced is calculation based on other input cUserID is unstored, based on "Personnel" table tUser is a lookup from "Personnel" -table In portal based on that relation still shows nothing. What's wrong with this ??

Not sure this is the right place for this query. I'm a beginner with Filemaker but have some experience with databases. I need an index/report which will cross multiple tables, let me explain. Lets say I want the following tables. a: Births b: Deaths c: Marriages Each table would have a first and last name. I will need to be able to show a form or report which is an "index" of all "events" by name i.e. Births table has the following: Fred Jones 2 Feb 1945 John Jones 7 Jul 1973 Deaths table contains: Fred Jones 15 Jan 1963 Anne Jones 12 Mar 1801 Marriages table contains: Jane Jones 21 Aug 1847 Sally Jones 14 Sep 1…

Hello everyone, I have the following issue: I have 3 fields - "company", "address" and "town". They are repeating in different layouts. I made a relation (using 2 tables) so when I enter all the information in "main" layout.... in other layouts ... i just select the "company" and the "address" and "town" show up automatically. The only problem is that sometimes one company has more than one address. I'm doing this, because I don't want to fill up more than once information, which I already have. For example in new record, I just want to choose "company" and the rest 2 fields to show up. Does somebody has similar issue ? Thanks

First of all let me say that I'm going on my first steps on FM, although I wanted very much since I can't remember...but I really need some help in two points for now. (so I'm counting on you guys ) The facts: I'm building a database to help me keep the records to my PhD thesis in Art History. I have several art objects (altars) that I need to save specific information, and the places where they are (churches) also need information saved. In two (2) Layouts I'm able to save all the information I need, BUT, some fields are the same in the two tables. (about 5 fields) For instance: Layout A: Churches Registration One record per church - in that layout I h…

Hi All, I've just created what I believe is my cleanest-to-use filemaker database. I used some techniques that I've found in this forum to use tabs for navigation, and to use portals for selecting and highlighting records. I'm not having any trouble with the database. It's working nicely (I think!). I have only two tables, but I ended up needing a lot of table occurrences to get where I am. In the interest of learning, I would greatly appreciate it if someone would take a look at the structure of these two tables and give me a "sanity check". Do I have too many table occurrences? And, if not, how does one annotate them to indicate to future editors …

I need to generate a report that lists 'work items' and the 'hours' and 'expenses' belonging to each. Each Work Item links to: a) many Hours records in the Hours table (eg. Dave spends 8 hours completing his portion of the work item, Mike spends 4 hours completing his porttion...) and many Expense records in the Expense table (eg. Lunch = $20, Fuel = $40...) How can I generate a list layout that- -summarises by Work Item -includes both Hours and Expenses? Should I create a calculation field that list all the Expenses against each Work Item, then... I don't know?? I may be thinking about it too hard and there's a simpler way. All I know …

I'm probably missing something obvious, so bear with me. Addresses table with a foreign Company ID field, Company table with its Company ID field. I need to switch to the Company layout such that only those records show which have a match to the current set in Addresses. In other words, a straight match of Company ID in Addresses to Company ID in Company won't do. That will provide all Company records which have a match in Addresses. I'm looking for just a match to the current set in Addresses. Here's an example. The current found set in Addresses shows these Company IDs — 101, 103, 106. Addresses records showing Company IDs 102, 104 and 105 are currently hidden…

I am having problems with table relationship in FileMaker. The issue is that FileMaker will not show data from a table if it is more than one table back. For example, I have 3 tables, Table A,B and C. Each table is related to each other with a timestamp. If I am in table C and I want the value of a field in table A FileMaker will not show that information. I have to exit FileMaker and re-start it for the information to appear. Am I doing something wrong with my table setup? Can someone please help me with this. I am not too sure if I am doing something wrong or if there are issue with the operating system 'VISTA' that would cause this problem. Thanks, Steven

Need help creating records from a portal using fields from different relationships. Please see file attached. SCENARIO: Charges use codes, which may include material costs. Each charge should have its own material costs records for the materials selected in the list. GOAL: This should be a list of available materials based on the code selected. A user should enter a quantity beside the item they used, creating a new material cost record related to the charge. PROBLEM: Currently displays the materials related to the code correctly, but the quantity (from material costs) will only create a new related record once, since the relationship is from Materia…

I'm having trouble creating a relationship that seems like it should be pretty straight forward. Here is what I'm trying to accomplish: Table A and Table B have a many to many relationship. I have a layout based on Table A and would like to have a portal that shows all of the items on Table B EXCEPT the Table B items that are related to the current record in Table A. Here's what I have now: Table A related to Join Table via relationship 1 = 1 (calculation fields) AND Table A primary key does not equal Join Table foreign key A. Table B related to Join Table via relationship Table B primary key = Join Table foreign key B. The portal showing relat…

I think this is going to be impossible to describe in enough detail for anyone the help but I'll try. I'm developing a CRM that tracks Sites, Contacts, Agreements and Payments. Sites and Contacts a Many to Many so there is a join table included. To further add to the my troubles there is a link to a participant collection database (we can just call it participants). As we collect participants we track what Site the Participant came from and now the Contact provided that participant to us. (until recently only the site mattered not the contact). The trouble I'm having is coming up with a way to record and track payments for Site and/or Contacts. …

HI people I'm looking for the very best and simple way to create my contact table and divide it with TO. I read in the FM bible that I can create many TO for every type of contact but I'm just not sure if I create many different ID for every type of contact or do I use a "type' field a sort them? please can someone tell me the right way. Oce I get that right I'm sure I will use it for so many other things. Here is what i have: contact table: -desk employees, -mill employees, -(desk and mill employee have the same type of employee id (1 to 300) -clients (related to one project only), -sellers (a seller can also be a desk employee occasiona…

I have the following tables and fields. [Orders] OrderID ShippingTotal TaxTotal [OrdersCharges] ChargeID Amount OrderID Type (can be Tax or Shipping) For EACH order, how can I show the total tax and then the total shipping from the [OrdersCharges] table within the [Orders] table? Each record in the [OrdersCharges] table can have MANY ordercharges. I tried creating a global text field called and TAX and populating it with "TAX". Then I did a self join where Type = Tax. Obviously they matched but this didn't work. Thank you in advance.

Hello, I'm building a large database for the first time and am not sure if I've got everything working right. I have three main tables: Assets, Issues and Project. All three can be related to either of the other two but do not have to. When I go to create relationships between all three (Using Join tables) I can't do it all the way around since there is already a relationship between the two (ie. I connected Assets to Issues and Issues to Projects, but can't connect Projects to Assets). Would this mean I can't have a relation from Assets to projects without having a common Issue? I think this has somthing to do with table occurences but I'm not sure what it al…

I am new to FM but not databases. I have 2 tables, Contacts and Consultants. The consultants is company based and the contacts are people. The contacts can be associated with as my companies as they want. When I bring up the company form, I want to see all contacts associated with that company. I have this working. I have a 3rd table that is nothing more then the key for the company and the key for the people. I build a relationship between the tables and used that to build a portal on the form and it lists all the people associated with that company and I can update their info that is listed. How do I go about adding a new people though, wither it is sele…

Hi All, I'm looking for some advice from more experienced users regarding a database I'm currently building. This is my first time working with FM and it's going well, but this particular aspect is a little trickier and I want to try as well as I can to not paint myself into a corner in the future here. I'm developing a complete accounting and inventory (as well as contacts and promotions tracking) database for my record company. We need to be able to track our cash flow, and alongside of this need to track our inventory using actual unit numbers (not just inventory cost). These seem like two distinct sets of data which would want two separate tables for st…

I am helping out at a nonprofit and I think their database has some structural issues that I'm not sure how to fix. The biggest issue is as follows... In their db, they are tracking product sales. There are three main tables that need to talk. 1. Orders - Includes OrderID, Order Date, Address info 2. Order Item - Includes OrderID, Product ID, and the type of item (product vs. calendar). One order may have multiple products associated of course. 3. Order Payment - Includes OrderID, and payment information. One order may include multiple payments. So the problem is that in some of the reports, I need to show both the payment info and the type of ite…

I've got a database with patient information. Each patient has a number of different types of notes associated with them. Some of these notes are "one time" types of things, like an intake report. Others are multiples, like session notes. Is there an easy way to create entries in related tables automatically upon creation of those these entries? For example, I've got a "master treatment plan" table, with patientID as a foreign key. When a new patient is entered into the database, I'd like a way to have the master treatment plan table populated with the foreign key so I'm ready to go when it's time to write that report. Any ideas? Right now, I've created a scrip…

Is it possible to display in a list view layout a restricted number of records without having to do a find first? For example (attached) I have one base table called Data, and for the sake of simplicity it has one field, an ID field. There's 20 records, and they range from 1 to 20. And there's a global field called g_Ten, with the value "10" I have a table occurrence called AllData and a table occurrence called LowData. It's a self-join and the relationship graph I have the equation LowData:ID < AllData:g_Ten Layout 1 shows records from AllData. Layout 2 is set to show records from LowData. I expected that this would mean Layout 2 would only show me rec…

Dear all I would firstly like to know whether it is possible to let users of my still-to-be-developed FM solution select an option that allows all (or only selected fields) to be “inherited” from another record. Such that selected fields for that particular record are always linked and updated subject to its “parent” record. Secondly, if the above is possible, what would be the best way to go about this in FM10. THanks

I have two tables, X and Y, the relationship is one-to-many. X has the following fields: kp_ID_X, name (unique) Y has a foreign key kf_ID_X to X, and some other fields . On the Y layout I have the "name" field from the related record in X. The problem now is that when I want to enter a value that already exists into that field, it will show an error saying it needs to be unique (because "name" is defined to be unique in the X table, obviously). The behavior that I would be looking for is that a new record is created if the value doesn't exist; if the value already exists I would like it to change to the right foreign key. Does anybody have a simple expl…

Hey gang: Back to the "well of knowledge" I have a database that I am working on that has client records (name, address, photo etc etc). What I am looking to do is be able to update the records such as changes to address, pictures etc etc and be able to show the changes in a list type format of when they were changed ie time/date stamping I have been able to add the time and date stamp to the records but not sure how to display these changes in a format where they will all be viewable... ie perhaps a portal?? Any help is greatly appeciated Thank you opieos

Hi, Is it a good practice to use a single table for all the photos in a database? I explain Let say I have a "cars" table. The user could add as many photos of the car he wants to. Logically, we need a "photos" table linked to car table with a one-to-many relationship. Now, I want differentiate the front pic from the others in my layout. How would you conceptualize this structure?

I have a field "x" that draws from a value list of the entire database's records. I want to create a field "y" next to that that shows the container contents of a photo field in x's record. Meaning, pick a spouse in field "x" and see their picture in field "y". Calculation? How do I do that? Thanks - Don

Long ago, pre-GUI, I programmed DB's, all flat or one to many relationship type. I am making some FM solutions, and guess what, everything is a one-to-many relationship with everything one hop away, just like I did 25 years ago. My daily use DB is following: central record is client, clients can repeat but they get a new record in the main table. Related to the main table, 1 hop away 1-to-many tables for: 1- time used, 2- client account #1, 3- client account #2. There are a bunch of layouts for reports & forms. - I see everything I make as 1 hop away. I can and I have been able to make all my solutions that way, just as i did long ago, but am I missing somethin…

I am a long time Filemaker Advanced User, and have learned all I know by just trial and error. I have become sort of a power user, but I am constantly seeing work by people like you that make me realize I have a lot to learn. So, I recently bought the Filemaker Training course that is being offered. I am finding that it has several good tips and techniques that I have not known about,, but they keep referring to a technique/strategy that I just don't get. I am talking about the use of primary keys and foreign keys. I do understand those, but the way they use them is what is stumping me. I have always made a primary key something that is understood by the user, b…

Hi all, I'm trying to build a song database. I have 3 tables. Songs, RelatedRecordings & Albums. On the Song layout, I want to have a portal showing all RelatedRecordings to that one particular song. The portal should display the following info: album, album artist, date of release, record label & catalog number. I want these fields to be "pulled" from the Albums table. The Albums table is not only used for the Songs so I don't want to create a foreign key to songs in the Albums table. What I want to happen is this: When the use creates a related recording to a song, FM checks if the album name or catalog number entered already exists …

Please can someone straighten me out on this one....... From what I was aware you were not able to use calcs as match fields in relationships....This is what I have been told. Although I have by default done this and it worked. Please can someone explain to me why I was told this and the reasoning behind it!!! Life would be easier if this was possible! I hope whoever taught me this was wrong!

Sorry - I don't know quite where to start here, but I have a photo database and I need to cross-reference some photos that are related within the same table. (If the photo is a composite, I want to reference the foreground and background images that created it.) i.e. In the table I have a text field called "version_name" and a photo container field called "photo". I want a drop-down menu of all images in "version_name" to select as a foreground image and then a thumbnail of that foreground image from "photo". I assume I do this through a portal, but I can't seem to get the cross-reference correct. I really don't know where to start. If they were in separat…

I'm trying to make sense portals and seem to be missing some key instruction. I have the attached database built and what I would like to do is use the EVENT_ID to display a list of competitors who have finished in the top 16 places for events that had similar conditions. I admittedly have no idea how to do this, so I thought an easier step would be to make a portal that displays the events that match the conditions criteria. I have used a few samples from this forum, but alas, I have no idea why they work, or don't work. All I need is a little push in the right direction to keep me learning. Thanks! EDIT Maybe someone can just tell me how to make the p…

And if so, how would you break it up. The data consists of 4 fields: YEAR EVENT CONTESTANT PLACE i am tracking 5 years, each year has 12 contests, each contest has 45 people. Maybe that is how it needs to be structured? data_sample.txt

Hi, I would like to know how you would structure the following "Excel" table This represent features of a new house and what percentage of the feature has been built. Here's how it works : The house inspector goes on the field and check what progess have been done and enter the progress in the value column. Structure explanation : The type columns represent the type of house inspected. For example : Type 1 is a 2 stories house and type 2 is a single story. The value columns is the construction progress of the feature. It cannot be higher than the selected type value. Here are the constraints : - There could be new building cate…

This is my scenario: Table 1: Services (contains the list of services the company gives) Table 2: Bookings (contains the reservations made for those services) -Service -Date -Time I need a relationship that shows all reservations for a Service in a given day in a given time, but if I don’t type anything in time field, I need that the same portal shows all the reservations for that Service in that date. I’ve been going over again and again: When I leave the time field blank, the portal doesn’t show anything. Any help would be highly appreciated.

I'm working on a database for managing my clients and session notes. Each client has a "patient ID" and each client has a number of sessions associated with them. My thinking is that I'd have a portal with a list of session dates that could take me to a "note" layout to write the note for that particular day. Something like where clicking on the checkbox will trigger a script. Does that make any sense? What do you think would be the best way to accomplish this? I've currently got a "master" key table which is related to the sessions table through a foreign key relationship. I'm using FMP 9.

I'm not sure if I'm describing that right but let me post a screen cap of my database: So I've got my "main" table related to my "axis 1" table via a relationship through "axes". In my testing, the patientID_fk in "axis 1" does not get created in my "axis 1" table upon a record creation. edit: I just added another relationship between patientID_fk in the "axes" table and the "axis 1" table and that seemed to do it. Is that right? I also don't know why my screencap is so small. The actual image, full size is here

I have a "Parent" and Child" table that look fine in the Relationship Graph. Each unique identifier is connected-- The parent field auto generates a serial number; the child field does not. I need to be able to generate records in the child table without adding a new record to the parent table. How do I proceed? I've tried using a portal, but can't figure out where or how to place the child field. Boy I am frustrated. Although I'm 60, I think I have enough brain cells left to manage this task. But I learn best when someone walks me through each step... simply and clearly. (As a retired English teacher, I think I have a chance at this!) I'm using FMP…

Hi All, I am trying to understand how one could show related records from one table, but that table's child related records beside it. I have simplified my example as follows: I have the following entities: Company Employee Computers Each company has up to 5 employees. Each employee can have multiple computer records. The administrator adds an employee and the computers related to that employee. The employees are setup on tabs, each tab showing the first portal record or first employee for that company, second tab showing the second portal record or second employee, up to the fifth portal record (see example database) The problem I have …

Hello again smart people of the FMForum, Here is my problem: I have an attendance table with 25 first name and 25 last name fields on it. I Would like FMP10 to automatically look up each person when they are typed in and then find them in the Member Profile table and list their status (If it helps it is a single yes or no status). The problem is that I can not figure out how to do this for each name individually. I got so frustrated I am rebuilding the attendance table from scratch, so any help is appreciated. For a basic idea it looks/will look something like this: Date ____ Group ____ First Name ____ Last Name____ Crime Victim____ First Na…

Hello and thank you in advance.... I cant figure this out: I have one table called "members" some members have dependents (a second table called "dependent"). I would like to make the dependent_id dependent on the member_id. So, if a member has an id (auto-enter, serial) of 0001 then the first dependent would have an id of 000101, the second would be 000102, and the third would be, 000103. I am NOT at all concerned about the format of the dependent_id's format, it could be a,b,c or use a hyphen or whatever. I am concerend that it be automatically generated, and that it be unique for each memeber (so there would NOT be a 000101, 000101. Any help wo…

Hello, I'm new to Filemaker, although I've programmed in other languages before. I am designing some tables for a database, and was wondering if I could sanity check the way I'm going about it with the forum. I have several tables in my database -- one that contains student records; one that contains professor records; etc. Each of these types of people needs to have several addresses associated with their record. So I've also created an addresses table. My question is regarding how to link the addresses to the people. Since the student and professor tables each have their own serial numbers (and I guess there might be overlap between the numbers since it's…

Hi, I've got a problem I'm trying to solve... I have a portal I would like to filter using three different filter variables - date, name and type... here's how I would like it to work... 1. If no filters are selected, then all records in the portal are shown. 2. If any one of the filters are populated, then only those records that have the same value as the filter are shown. 3. If multiple filters are populated then only those records that correspond to all the filters should be shown. Any thoughts would be appreciated. if you need any more info, please give me a shout. Cheers George

Hello, I am creating my first Filemaker Database using Filemaker 8 and am having trouble getting the result I want for a index-style linking system for contact records. I have figured out the 'half way there solution' but was hoping someone could help me realize the full vision. I have a table and layout called Contacts. I also have a table and layout called See Also. In the See Also table there are four fields: Source Name, Source Description, Target Name, & Target Description. There is a portal to the See Also table on the Contacts layout. The overall goal is to display the Target Name and Description as a link in the See Also portal on the Source's record an…

Hi Everyone, I would like to get some idea of what developers do in regards to tracking Contacts in a database that uses Contacts in many different ways. For example, say a company has records for Customers, Suppliers, Employees, Advertisers and Contractors. There could be a table for each type because a Customer is different from a Supplier which is different from an Advertiser and so on. If a Supplier decides to buy something from the company then they get entered into the Customer table, thus duplicating data. Alternatively, there could be one Contacts table, but that would need fields that encompass all of the different contact types, might get a little mess…

I'm stumped. I have a MAIN table with only one record (all globals) from that table I have a relationship to an ITEMS table and from items to SUBITEMS. In a layout based on the MAIN table i built a portal that shows records from SUBITEMS. This portal only works, showing records, if I'm signed in with Full Access rights. I created a different account that is identical to the Full Access rights account and still won't work. Any ideas?

I'd be grateful if someone could kindly help me... I've created a database with tables for "Customers" "Invoices" and "Lines". I have relationships as follows: customer::kp_customer_id = invoice::kf_customer_id and invoice::kp_invoice_id = line::kf_invoice_id On the Customers layout, I have a portal showing related records from Lines - and this seems to work fine and lists all the lines for each customer. I have also imported the lines from my old Appleworks database via Bento. The old database was not sophisticated and had the customer details on every new record. I have imported the customer details and line details separately to the appropriate…

I'm trying to create a worksheet for our sales staff. Ideally what I would like is to have a layout that shows all the parts for a specific year and make of car. The salesperson would then be able to go down the list and add a quantity of that part and it would be automatically added to the sales order. I have attached a dumbed down version of the database and hopefully you can see what I'm trying to do. It is currently kind of working but I need to figure out a way to get the salesOrderID, description and partNumber fields into the SalesOrderItems record. Thanks in advance Example.zip

Greetings friends and fellow FileMakers... I need your help please... I have a quotes database, an inventory database a quoteitems database (to build the specific product line for each quote) and a pricebyquantity database. Since the price of our products changes by quantity, I don't have a single price for an inventory item, I usually have 25 different price points, from 10 to a million. So I need to reach from the quote, thru the inventory to the pricebyquantity (PBQ). No sweat. But I have a problem. If I ask for the price of the itemnumber by one of the actual price points in PBQ (like 25, 100, 500, 1000, etc.) I get the correct price returned. [ NB: I am doing a …

Hi all, I have a very simple client database. I want to keep it fairly simple as it is not doing much. Very basically it is a list of clients and their contact details along with a couple of other related tables to say who speaks to who etc. What I need now is to record any transaction against each client. There are 3 transaction types... Sales Support Maintenance Now this database isn't handling invoices or stock or anything like that, we just need to record each transaction in the database. i.e 02/04/2009 Client2 Support £500 02/04/2009 Client1 Sales £3000 02/04/2009 Client3 Maintenance £250 My thought is to have a transactio…

Hi ! Why there is only two layouts available, when I use GTRR using layout X in a button setup ?

I'm trying to figure out the best way to return a subset of records by, in this case, a date. I could make a script to find all records with a specific date but there's a ton of records and it's fairly slow to do so. So what I was hoping was that I could use relationships instead. I've tried using a self-join with the Record Date matching to a global date field that I'd use as the filter. This gives odd results. Are self-joins the wrong approach or is it a matter of incorrect execution?

Hi All, How do I get the COUNT of the current record's - related records, - The number of records related to the current record.

Hi All, From the title you probably have a fair idea what my question will be - How can you loop through records in a portal, the GotoRecord/Request/Page only goes through records of the native records, I dont actually have to GO to the related records I want to stay in the current table and be able to get the value of a field in all related records.

I have a layout that is showing information from a different database. The original db is called job tickets. The new db is called calendar. I've created a field in the calendar db called id job number and related it to a field in the job ticket db called job number by an "x" relationship so that i can see all of the jobs from the job ticket db in my new calendar db. Now, i want to have the id job number display the corresponding job number from the related db in a separate layout. I've created the separate layout in the calendar db and would like to show the id job numbers in it, but it's not working. This is sounding more and more confusing as…

I'm trying hard to develop a FM9 database and I run into a (basic?) problem. Basicly, I have four tables, all linked to eachother, where I can keep track of events, notes, schedules and goals. On the start layout of my database I have buttons to create new entries. I want to have a portal who shows the summary (an existing field in every of four table) and the date. The aim is to select one date and see all the entries done on that date. Is there an easy way to do this? I am looking forward for any hint or advice! Thank you in advance for any post.

I'm not bad at FMP, but I created an invoice table that has "items" field that is related to a "items" field in a table called "Line items" that is related to the field "items" in an Inventory table. My question is how when I create records in my invoice portal showing "line items" to update my inventory as each line is created. There are some summary calculations missing here. I have something sort of working but the inventory does not update instantly with each portal record being created or deleted.

I have been over thinking this problem and now I am lost in the woods. Any information or advice would be greatly appreciated. I don't know if this should all be in one table or broken up in to two or more. I have Projects and Assets. One of my problems is that each Project dictates the name of the asset. Examples of Project names: 001_Veh_Cycle 002_Veh_Plane 001_Env_Mountain 002_Env_Rivers The asset names are 001_Veh_Cycle_Handlebars 001_Veh_Plane_Wings What I need to do is enter data on each individual asset. Design date, artist name, Approval Status, etc. But each Project that is made up of multiple assets has it's own data. Comp…