Relationships

Hi! I'm trying to make a database to manage information on books, authors and narrators of audiobooks. These three entities have their own tables and I also have a table for countries. I want to assign a country to each author and narrator, and in a layout based on the countries table I want to show related records from authors in one portal and narrators in another one. I have tried to make this work with different types of table occurences and join tables but I can't get it right. Any help would be greatly appreciated:-)

Hi, I have a database that lists buildings by post code, we also have another table / Database that lists windspeed by postcode Database B (Premises) is related to Database A (postcode / windspeed ) via the postcode matching. Problem is only one value for windspeed is imported per instance of postcode, in the premises database, meaning the rest of the premises records with the same post code do not seem to get displayed or imported. I have tried just placing the fields in the layout view of the related Table… B , I have tried doing a calculation and neither work… only one instance of the postcode is imported. For example if say postcode sw19 5ep has 10 premise…

Hi, I design a database for product-transaction, My problem is: I want when click in receive filed , then open transaction layout and enter qty of receive product. This maybe happend many times until receive product balance with qty purchace. In secodn time when go to layout transaction I want see my past record for this special (purchase and product according my design if it correct.) Please see my sample database and help me for this. Thank you. Transaction.zip

I must admit I'm somewhat new to databases, no experience so far. I was assigned to develop a simple database with Filemaker, but after hours of scrutinizing the starter-examples and the web for tutorials I don't see a solution. What I want to create is a simple database with 2 Tables: Books and Articles. Books contain multiple articles, but sometimes the same article can occur in different books. So in the formular view of the books I want to see next to the book data (author, title, etc...) also a list of the articles which are contained in the book (which is done via Portals?) In the formular view of the articles I want to see also in which books a certai…

Should the auto enter ID or Serial number field be defined as text or number. I've seen both used. Does it not matter as long as every pk and fk uses the same type?

My client's members can own boats in a number of classes. I have a Members table, a join table Class Member Data, and a Classes table. This has worked great for years. Now the client has decided that family members of a Member should be identified as separate owners of class boats. Until now, family members have been identified in a separate Family table (related to the Members table by Members::membershipNumber = Family::membershipNumber). I am not sure how to modify my solution to accommodate this. Should family members be moved to the Members table with a flag field that indicates that they should not be considered as full members for other purposes (voting, …

I think it is a matter of relationships but I can’t make it work. What I’d like to accomplish is: To show in the portal coloured in red the names of all contacts (only once each contact) related to the properties of a particular building. Once shown, can I skip them by type of relationship using the portal filter I.e. TypeOfRelationships::Relationship (or whatever fixed table) = "Owner"? I know is not correct in my sample file, I have just done it to show with an example what I need to accomplish and trying to explain. Thanks to all for your help. Relationships.zip

I am by no ways or means a database programmer but I understand most concepts enough to create what I need to. But I admit I'm always stumped on what is probably a straight forward concept for most of you. I have the typical company (just one) with typical customers (many) creating quotes/invoices (many) with plenty of items for said invoices. I have no problem with creating a master invoice table with relationships to customer table and a relationship to the items table (and some of the items have relationships to subitems)...but I really don't know the correct way to tie a single company record to all of this. Right now since the company record is just name/ad…

I have scoured the help and forum topics before deciding to post. I have 2 tables in my contact management database: 1) Contacts (where each record is an individual person) and 2) Companies (where each record is a company and their unique address). I have tried to relate the 2 tables, such that when you create a new record in Contacts, you are able to choose a Company from a drop down list, if applicable. My key fields are as follows: in Contacts table: New_Company Record Number (number field) and New_Company (text field) (Do I even need the New_Company field here??) in Companies table: Record Number (number field) and Company_Name (text field) I relate…

I have a many to many portal whereby I do a self join of categories with lookups into Product Description and Product ID. When I select a category, i.e. Hardware, I get the proper Product Descriptions on the right number of rows but only one product ID on the top line of the portal. I need the product IDs to load just like the descriptions in order to drag and drop the right one into the parent table. Is there any way to get the product IDs to load as per the product descriptions. Any help on this would be terrific. TIA

Goal: I've got a FMP file with a quite complex solution, I want to keep the solution in a single file, but I want also to separate the data from structure. The result should enable, when needed, to refer the data to another data source while being able to have the solution in a single file. Possible solution (this is the question) 1) I reference the file to itself, using External Data Sources 2) I change in the relationship graph the references to the tables using the new external data source (which practically it's the file itself, but not logically) 3) when needed I could change the references in the external data sources to display remote data or to simpli…

Hello, could anyone help me with the following please: I have two tables related one to many, each table has their corresponding children tables (line items). Table 1: Orders Table 2: Invoices Table 3: OrderDetails Table 4: InvoiceDetails The relationships are as follows: Orders."_kp_Order_id" = Invoices."_kf_Order_id" Orders."_kp_Order_id" = OrderDetails."_kf_Order_id" Invoices."_kp_Invoice_id" = InvoiceDetails."_kf_Invoice_id" Both detail tables has a field named "ProductCode" and "Quantity". I need a calculated field to know how many quantities have been invoiced from a particular ProductCode in a particular Order. I though that i…

Hello experts, kindly help me about my database, I cant make the relationship to work (expire, retest, and reorder). I have attached my data dase to make it easier for you to help me. thank you very much to any help that you can extendSupplies.zipd.

Attached sample. Payment is for rental period which can overlap months. But I need to get the 'total per month'. All I can think is to create a table with months or days? So I have $1845 for period of 12/14/2011 - 2/3/2012 which is 36.1764705882352941 per day. I need to figure out how much applies to December, 2011, January, 2012 and February 2012. I think I can get that but ... where do I store that information? Do I need a month table or a day table and if so, how do I transfer that information to it? I have not answered on that other thread yet, which is different question, but I was pulled onto this first. Ideas appreciated. How can I take a rental ra…

I'm trying to find a quick and dynamic way to perform a search of contacts utilizing a portal to display results. I have 3 or 4 fields I want to perform this based upon (First name, Last name, SSN, DOB) but I'm having issues with getting the lookups to work. I can get an exact match to work, but getting a partal match ("Chris" finds "Chris", not "Christina", etc) I don't want to use a portal filter because in my final solution we're going to be searching in excess of 500,000 records, so I believe that using a filter will be an issue with this many potential records. I've attached a sample file that uses a global table and a related contacts table to perform th…

I am attempting to create a database for stock control at a venue. The venue contains multiple bars and multiple storage areas for the stock. Not every item is stored at every storage area, as the different bars offer different menus. Creating the table of stocked items is straight forward enough, here's the problem: We want to use the database for regular stocktake, whereby a new record in a related table is created for the stocktake at each storage area on a particular date. Essentially, we want to pull the 'item name', say, into the stocktake table, and be able to add quantity information for each item which is unique to the new record. The resulting stockta…

I'm about to perform a major overhaul on an existing database that is used to track customer orders. They want to add a billing feature that automatically calculates sales tax appropriate for each state/zip code on an item-by-item basis, since some of the line items being sold are taxable and some are not. So the person entering the job will have to determine the taxability of the item and then I can set up the database to apply the applicable sales tax. Or maybe I will set up a table of items with predefined tax statuses. Haven't decided yet. But before I go creating this myself, I'm wondering if anyone knows of an existing plug-in, or something, that would mak…

Anyone know how to display grandchildren on parent layout with an on the fly filter feature? I have set up the base of this and attached. I'm obviously missing a piece, but don't know what it is or how to implement. Any help? I appreciate it! Cheers! FilterGrandchildrenDemo.fp7.zip

How do you establish the "Owner" record in the CompaniesTable? CompaniesTable contains Owner, Clients and Vendors records. The database system needs to know which record from CompaniesTable is the operating Owner. Soâ�¦ I have accomplished this using a SystemTable. DEMO file and screen shots attached to help illustrate question. I just wanted to get a second opinion on this structure. Good or bad and needs work? The SystemTable is a "one record" table. SystemTable has a CompanyOwnerID field to set the Owner in. ALL other tables, ProjectsTable for example, have a hard calculated field that simply = 1001, the SystemID. This field connects the Projec…

Hi I have a table with a repeating field called Days[15] In a related table I want to create a repeating field called SumDays[15] where each repetition is the sum of the corresonding repetition in Days for all related records In trying to find a solution to this I have seen a lot of people advising that repeating fields should be avoided if possible. Unfortunately I have an existing database with about 50 scripts that would all have to be re-written if I get rid of the repeating field. Is it possible to sum a related repeating field into a repeating field? Thanks Tom

I have a layout where on one side I have a portal displaying the name, details and image of many pictures, reasonably small. I would like that next to this portal box is a big container field that when one of the smaller images is selected, that image appears in the big container alongside. I have tried self-relate tables and I just can't get it to work. Do you think you could point me in the right direction please. Thanks so much, María *Attached photo of what I am trying to achieve. P.s. can one search within the portal for a specific picture?

I have a inventory database for which I have several locations which I go to and do a physical inventory. SO I have a locations table and an inventory_count table. I would like to create a report/layout that will display my ending count for each of the different locations by month.

hi, i know using portal's filtering is a major pain. i tried making occurance and filtering a unstored field. is that possible? What is the best way in doing a filtering of data to be displayed in a portal? i have seen ppl saying using relationship is the best bet.

Hello I have a simple issue that i can't get into grasps about, which is linking to tables to the same field With reference to the db relationships below I am trying to link "Contacts:Contact ID Matching" to "Class Taught:Contact ID Matching" but Filemaker wont allow me as it says i cant have 2 rships to the same table. Thats all fine... but, if i have a relationship as above, I create a Classes Taught Portal and a Classes Joined Portal in a Classes layout to display who is teaching the class and who is learning in the class it will display fine. However if i try to have a Classes Taught Portal in a Contacts Layout it wont be able to display the relevant…

So I have 2 tables: Customers & Jobs On the Jobs layout, I have a drop down list of Customers, so I can select which customer the job is for. What I would like to do is be able to type in a NEW customer in the Jobs layout, in the Customer field, and since the customer's name is not in the drop down list, it would create a new record in the Customer's table with the name . Am I overthinking this one? Thanks...

I have a current solution that allows analysts to review individual cases, define them with a low-level, specific issue, as well as a higher-level description for "bucketing" or grouping lower level issues. Now, I need to add an even higher level grouping that will allow execs and engineers to roll these cases up into these higher-level groupings and look at report based on those. Example: A crime occurs and a case is created. A specific crime is attached to that case, such as shoplifting (under $100). This then is bucketed into a higher-level grouping of "petty larceny". Similar crimes with a value of greater than $1,000 become "Grand Larceny". Finally…

I have a contact info database based on family records, with the main Families table having a relationship to a second table called Individuals. In my layout, I've added two portals to the Individuals... one for adults, the other for children. There is a script trigger on each portal's "First Name" field that assigns that Individual's record as being either an adult or a child. What I would like to do is calculate the Family name from the adults' last names. For example, if I enter one person, John Doe in the adult's Individuals portal, and his wife Jane Doe, I want the Family Name field to automatically return as "Doe". However, if John Doe is married to Jane Smith,…

Currently I need have two tables and one that needs to be created: 1. Inventory of all items a. _kp_UniqueID 2. Orders (a bit more complicated than that but for simplicity sake) - allows us to order each item many times and gives each order a separate barcode which is tracked a. _kf_ParentUniqueID (relationship with Table1) b. _kp_OrderID © _kf_NonInventoryUniqueID (will be relationship with Table3) (3) Non Inventory a. _kp_NIUniqueID I need to add another "non inventory" table (3) in now that will also need to have orders created. Our IT department and many projects extract data from our FileMaker program and are reliant on t…

I have a new inventory database, it organized sales by model number, so right now i have 3 different model numbers (all linked as a relationship) When I view each record (model #) I see all the transactions for that model number) I am trying to create an expense layout that shows all transactions for all model #'s, but when I created the new table it only displays transactions per model number, and I need all model numbers at the same time. any help greatly appreciated!!! Urgent!! Thanks Matt

I have a database set up with Customer records relating to multiple Contacts for that customer. I am having trouble creating a calculation to return a value from a specific related contact (one among many) based on its contact ID. For example, each Customer has a default billing contact, which is recorded by its ID in the customer record. What calculation would return the email address of the default billing contact? The calculation I'm currently using, based on case ( ), is not returning the value from the correct entry: Case ( billToContactID = customerContacts::contactID ; customerContacts::email ) Thank you, Dan

I have three tables: 2 main tables and a join (the join table is set up to intentionally allows users to delete records) Table1::_pk_Table1ID=Join::_fk_Table1ID Table2::_pk_Table2ID=Join::_fk_Table2ID In addition, I'd like a table that stores the join records history (another-words, doesn't allow deletions). I'm stumped on how to go about this. Initially, I recreated a table occurance of the join table thinking I could link it to the join and not allow deletions in the history table... but the records won't automatically generate and if it's linked to the join and a record is deleted in the join, what happens to the link between the tables. Anyway, …

my boss wants me to make a database to manage a "new product development process". every time we go through the process, we go through the same 63 steps. each step has an owner, a due date, a deliverable, a comments section, and a status. we have to follow the 63 steps in order, in 5 "phases". I believe it would be most simple to make 1 table with fields like this: step1_owner step1_duedate step1_deliverable step1_comments step1_status step2_owner step2_duedate step2_deliverable step2_comments step2_status step3_owner ....and so forth. Each project would only be 1 record, and I would manage the "user experience/correct phase seque…

Hi, I have 7 layouts and 7 tables in every one I have a ID auto-enter serial number selected. In my understanding it should generates one sequential number every time the new record button is pressed, saving that number to the related record. The number is always incremented by one as it should be, but the problem is: In all layouts the numbers are the same and the last number is set for all of the ID's records in all of the layouts, If I check the table view all records have the same number too, incremented by one if new record is pressed. I have for test only 10 records, all set to ID's 10. Thanks in advance :)

I'm not sure the best way to articulate my question, but here is a rude attempt... In the attached sample you will see "Style ( A )" and "Style ( B )" Both styles may be correct under proper circumstances, I'm trying to learn what these circumstances are. Both Style ( A ) and ( B ) only illustrate two tables, ProjectsTable and AssignmentTable. Style ( A ) only has one MAIN table occurrence per table in the same TOG. In this style UI layouts would only feed off - PRJ~PARENT PROJECTS - PRJ~CHILD ASSIGNMENTS Style ( B ) sets up a TOG for ea. table. The result in this example it twice table occurences. In this style UI layouts would only fe…

Hello Everyone; I am trying to do a prototype db , but i tell you context is a tricky thing that i still have left to master. If you look @ my screenshot i am trying to use a global to "SHOW" the Option TYpes from OptionTypes_select, but don't know HOW to! I have tested using lookup filelds and that works, but i would love to know how /if i can show data from Option Types & Options across the graph using globals within my Orders Table. The reason is i am wanting users to select and add certain criteria within Orders and after they make the selection, and choose Submit, the order is added to Lines, ONLY after they choose valid items. thanks for any a…

I'm not sure if this is the right place to post these questions - apologies if I'm being inappropriate, but I'm new to all this! I'm a junior paediatric surgical trainee and i'm trying to set up a filemaker database to keep track of patients I've seen and whose care I've been involved in. I have just purchased Filemaker Pro 12 and have downloaded the associated Filemaker Go for iPad app. I have identified the following fields (arranged into tables that I think make sense, but am happy to be corrected): Demographic Table Patient ID (this is the unique patient identifier) UR Number (the hospital's pt identifier, but given i'll be working across multiple hos…

Hi - I could use some help setting up a many to many relationship between a single table. My database is being developed to catalog real estate documents, such as deeds, mortgages, leases, etc., that are filed in the real property records of a county courthouse. The first version of my database had separate tables for each type of document, and it was a mess. I've started over, and have a single table, Documents, and each document record is tagged as a particular type of document. This data classification system is based on the demo at FileMaker Magazine (http://www.filemaker...sification.html). So, I have a Classifications table and a Tags table, which is a joi…

I'm a little unclear as to how I can go about with my relationships. I have two tables. The first table shows all of the company's risk record which are projects that we have finished and have not finished or projects that are in progress. The second table shows the risk records' Control ID which is like an ID for each risk record (I have about 100 records by the way). This second table is like a reference section that catergorizes what control ID each risk record falls under. Here's an example of what I have in each Table: Table 1: (Risk Record) Type Criticality Rating Status/Actions Issues Resources required Start Date End Date Completion sta…

I'm building a parts system which needs to include a Bill of Materials for each part. My current solution has a Parent table which includes every part, which is linked to a sub assembly table which specifies the ID of the parent part in the parent table, and the ID in the parent table of the subassembly part, and the quantity needed. For example, Part A uses 2 of part B. Part B uses 1 of Part C and 4 of part Part Z I'd like to be able to build a BOM List using this relationship which, for any record in the parent table, gives the list of parts required and the total quantity of each part. This means that the BOM for part A will show 2 of part C and eight of Part Z. …

Hello everyone; Looking for any help on this one; Having a bit of an issue with Connectiing Lines --> LineOptions-->Options ___Optionstypes. just wanted to know how to make this much more efficient jumping over to other layouts to create new records that relate accordingly or allow create wherever necessary. I am a bit hesitant for orphans so i need to figure out a delete tree as well Or perhaps just archive all my records in a utility table when necessary. Issue i have lies when on LinesOptions Table and user is adding option Types & Options eg. Product Type = Cake Option Type = Flavor Option = Pink Champagne. Now i need to…

Can somebody tell me what I am doing wrong? Tables: Clients and Lines Relationship: Clients::xclient_id (global) = Lines::client_id Layout in Clients with portal to show Lines working good Button in each portal line to show individual record for each line in another layout Used GTRR to show result in new window but it always shows only first record. Also tried Show Only Related Records (Match Current Record and Match All Records in Current Found Set) with same result Any help will be greatly appreciated.

As you can see , this is starting to become quite ambiguous. Any ideas how i can eliminate this before this gets way out of hand? 1 Product can have MANY options, there are some Products which share the same Option( this is my issue) 2. using value lists, but perhaps this is not the way ! 3. Perhaps my normalization is not normalized enough? if not, how can i normalize better. Thanks -ian

Hi. I have a fairly standard customers -> orders -> line items - > products - > prod titles - > titles database where customers have orders of products that are made up of multiple titles and I have been struggling with how to analyze "For customers that have own title a, what other titles do they own?" Currently I can run a report based on my titles table, sorted by title name, and displays a summary field in my customers table that counts customers. This gives me a count of customers that own a title. It seems a little counter-intuitive to me that this report should be based on the titles table since I really want to analyze customer i…

I am seeking some advice on how to tackle a project I am working on. there may be some flaws in my logic, but My idea made sense at the time, but I am still missing a key piece to keep everything tied together. We have PO information kept in Quickbooks that I am deriving Vendor Status reports in FMP. The information will be updated regularly from QB... but the users need to make their own notes pertaining to each line item that will remain in the historic table in FMP...when the Table of QB information is cleared and re-imported.. I am hoping I can sync it back up with the historic notes and information in the historic tables. I envision 3 tables. Tabl…

hello everyone; my first thing is i have a simple invoice system that looks basic like this customers -:Orders=: LineItems=: Products my issue is i add complexity as i have a few individual lineitems Products which have additional options. So i have to add another join table; LineItemOptions which connect line items to a product options table( another TO of Products) My thing is how do i make this more elegant or am i just being silly? Eg. User adds CustoM BAG ( product id, product TABLE) then they want to add another option(3) option1; leather strap with zebra print option2; color black with pink inlay option3; interior skin ( shark …

I have made a table ("A"), and made a table occurrence for this table. Then in a layout entitled "main table," I have a tab control, and placed a portal within one of those individual tabs. Within that portal, I provided the fields from that table occurrence for table A. The relationship between the two tables (and the main table and the TO for A) is made by one field -- a unique number. However, whenever I type information into the portal for that table, it does NOT sync up with the individual table A (in a separate/it own layout)...this has worked well for me before. That is to say, I have entered information into multiple portals in different tabs, and everything …

hi guys, first post ever. Been trying to look around for this reference but i can't seem to find the keyword for this. I am migrating from Access to Filemaker. still very new in this. i have 1 table with 3 fields Visiting log and visiting log_self where it joints Date equal date, Name equal name and PersonInCharge equal PersonInCharge id Name date PersonIncharge flag=if(id=visiting log_self::id, "a","") Totalclient=count(visiting log_self::name) Month=Month(date) 1 john 4/4/2012 Sandy …

Hi All, My problem is this; I have a self-join which always seems to 'break' (loses data in the related portal). I have a database cataloguing my pipe collection and in the 'Assets' table is the self-join (related_to & related_from). The 'Assets' form contains the 'related_to' field as a drop-down, pointing in turn to a list (briar, metal, cherry ect.) The portal on the 'Assets' form displays related records EXCEPT the current displayed record - which is fine, but when this link breaks (for whatever reason) the only way to re-populate the portal is to delete all the records and start again! I'm pulling my hair out over this! Any suggestions? …

I'm quite puzzled. I made a very simple database that has the following: A table for orders: - order ID (unique) + information about the order including the article IDs A table for article: - article ID (unique) + information about the article I linked the two tables together with a one to many relationship (because the same article can be ordered more than once) and wanted to make a layout where I could see the article information when cycling through order IDs. For example, I'd search for ID 120245 and it would show me how many of the article was ordered, the order form code, the article dimensions, colour, etc. Except that ... it doesn't do this. For every …

I have four tables: Org for organization records (name, address, etc.) Client client records (name, specialty, etc.) Office organization office location records (city, mileage, etc) Visit Visit to client records (date, client, etc) On Visit records I would like to select a Client name from a drop down list and then an office city from a drop down table. I’ve been trying to set up value lists do this, but I can’t figure out the relationships. Visit.zip

In a typical customer invoicing relationship: Customers to Invoices to LineItems to Products I can put a portal on a customer layout to see what products they have bought (portal connects to Products table). But how would I get the quantity of each product they have bought to display in that portal?

I`m looking for help with a problem with a lookup. I created a memebership Database and to simplify things i added a postcode / address /town /county table which automatically searches postcodes input into the " members" table and fills in the rest of the relevant details for the member. All was working great adding the data to both tables as records were created. Started this morning to carry on with the database to find that the records are being added in "members" but not copying over to postcode table. Nothing has changed within the database except for a button movement to a different layout.( even reverting the button back to original layout hasn`t solved the issue) …

I have been gradually restructuring a solution that someone else created and then retired and left me to pick up the reins. I am close to getting where I need to be thanks to a handful of extremely helpful people on this forum (you know who you are ). My dilemma, below is an example of a couple of records in my main table: Y214 01 08 14 Y232 14 24 32 Q In hindsight from day 1 (approx 4 years ago), Y214 should have had three seperate records due to having three Hazard Nos associated with it. I can live with it (10000+ records to deal with otherwise) but it makes a sub summary report (based on Hazard Nos) I have in mind not possible. If I do dec…

I have a relationship that is giving strange results. The relationship is complex, using 4 match fields. Some of these fields depend on other relationships, etc.... The parent table has a calculation field that equals a field in the child table. Everything "seems" to work fine EXCEPT, when there is no matching record from the child table, the result in the parent table seems to be correct, an empty field. However, when clicking in the field, there are 2 spaces at the beginning of the field. We discovered it because when we export this to Excel, there are in fact two spaces at the beginning of the field. This one really has me stumped. Note: Say that the Parent…

Hi there, I have a strange problem that I would love some help on to resolve. I enclose a sample set of files to demonstrate this issue. However to test this problem you need to open them on a peer to peer network. The problem only appears on the client side and not on the server. What I want to do in the Reports file is to limit the viewing of the records to only those with the same cost centre code (CC Code). So only those logged in with CC code 1 can see records for CC code 1. I am using the global field G CC Code to set the cost centre in each case. In the Visits file I have a portal in which I wish to display all the records regardless of whether they …

This might be real easy to accomplish, but I'm kinda stuck on this one. Here's what I need: I need a count of related records that match certain criteria. My setup is as follows: I have a Main Table with Records, each representing a patient. Every Record for a Patient also includes one Doctor and one insurance carrier. Here's the challenge: Certain Doctor's don't work for certain carriers. So, I set up a related table (that's related to 'Doctor') called 'Refuse'. Table Refuse contains just two fields, Doctor and Carrier. What I need to get done is to throw a warning whenever a patient record is created in the main table that has a Doctor/…

I have been out of the Filemaker Pro arena for the past 8 years. I’m currently working to bring myself up to speed on FMP 11 Advance and have run into an issue and I can't seem to get my arms around a solution. First let me start by identifying my primary tables, primary, foreign and match key fields, and the current database schema: tblCONTACTS (fields: [pkCONID] primary, [fkHHID] foreign, [kContact_Type] match key and other fields) tblHOUSEHOLD (fields: [pkHHID] primary and a bunch of address fields) tblHOUSEHOLD_JOIN (fields: [fkCONID] and [fkHHID]) I have a join between tblCONTACTS::pkCONID on the one side and tblHOUSEHOLD::fkCONID on the ma…

Need to allocate payments to invoices, helping a friend out (will I ever learn) and don't seem to be able to find the best way to do this. So I have 3 tables... Invoices, Payments Received and Payments Allocated (join table). I'm using Filemaker Pro Adv 11. So let's say in... Invoices. I have Job No, Invoice Date, Customer name and Total. Payments received. Payment ID, Date, Customer and Amount. Payments Allocated(join table). Allocated ID, Job No, Payment ID and Amount. I've gone in and manually created all the invoices in the payment allocated(join table). Is there a way to automatically do this when an invoice is created? Also I tried to add…

Hello, I need some advice on if this is possible or a good approach. Part of my database solution includes enrolling students into a summer music camp (an event) and then enrolling in classes in that particular camp. Here are the relevant tables I have: Students Contacts (faculty who teach at the camps and others) Classes Events (includes camps and other departmental events) Student-Events (join table to events) StudentEnrollment (join table to classes) I currently have these relationships: Students --< Student-Events >-- Events Students --< StudentEnrollment >-- Classes I also have a similar setup for the Contacts who are …

I have a classic Invoicing setup using three tables; Contacts, Lines and Invoices. I am trying to create a payment record system that integrates with this system but my mind cannot get around a problem. My process. I create a Receivables record to track payments and relate this back to the Contact. I insert a portal into a Contact layout to create a report for each client. I do the same with Invoices and thereby see all the Invoices related to the client. Only this system is not so elegant. Ideally I want to see these records showing up based on a Date in one continuous list per client. Then my client looking at the report is tracking our activity together chronolo…

Hello, I started using FileMaker only recently and I have a problem with my accounting data base. To make things simple, I have two tables - one for my real transactions (my accounting) T1 - one for my projected transactions (my budget) T2 The data base is about comparing those figures on one single report layout. Every transaction belongs to one category (rent, groceries, salary) and each category has one type: either Income (salary) or spending (rent). I made one layout based on T1 with two sub-summaries - one based on the types (income/spending) - (within each type) one based on the categories (rent, …) I would like the layout to show - …

Ok, so i have a table called Reptiles and fields for it: ReptileID ReptileCode ReptileSex ReptileMorph ReptileSire ReptileDame ECT... i need to setup a family tree, showing 5 generations,the reptilesire and reptiledame fields record the same data that would be inputed into the ReptilCode field, so they show the ReptileCode for sire and dame. In the image attached you can see that the subject is the record being browsed, and the (ReptileCode) for the sire and dame is showing, but the other fields which just show the same data from the subject because i dont have fields setup to record that info of the sires and dames of each generation in the Reptiles tab…

I am creating a system for an organization to trek $ donations by individuals held in a Contacts Table. Usually, donations are one-time events where someone just sends in a check. Other times, an individual "Pledges" a certain amount and pays it off in a series of monthly or yearly deposits. So I have a Sub-table called "Donations" off of contacts to record all actual payments of $, whether in individual donation or a Pledge "fulfillment". I also have a third table for Pledges on the far side, making the Donations table a "join table": Contacts < Donations > Pledges My questions: 1) I don't see any problem with keeping all the $ payments in th…

ok so i have a couple tables (Reptiles , Medical) i want to find the best way to relate the medical records to the reptiles, im assuming this is a one to many seeing as one reptile can have many reptile records and each medical record can only be assigned to one reptile. Please note that in the reptiles table the field called Reptile_ID is not a "ID" field like Record_ID, its more of a "Name" field but since breeders dont use names we use ID's (EX: M.PJ.3.34.11) I had a relationship once set up by relating Medical::Reptile ------ = ------Reptiles::Record_ID it was working fine for a while then i ran into problems when creating another layout to …

Hi All, This one has got me stumped - I am a noob, in the process of learing on the job - and as always I have set my goals qite high... I am slowly developing a solution to run my catering company.... and up to now I have read, tried out, backed up and (not to pat myself too hard on the back) have a system that can track all my purchases, return the curent price, the average price over 3 and 6 months - distinguish products that can be used only in recipes, and those that can be sold as dishes in their own right and used in recipes - I can create recipes that take into account the above and the average waste factor (dirty potatoes to peeled) and adjust the …

I have a mileage field in a table and a summary field (count = mileage) that sorts on the TableNameID field and it works fine. However I only want the summary to count mileage when the field Report Printout = Yes. I thought the way to solve this is to create a global mileage field with the value = Yes and add another table occurence of the table I'm working in to the graph and use Report Printout = global mileage and then use the new relationship in the original summary field when restarting the count. I can't seem to get it to work though.

Ok, here is what I need to do, and I can't seem to figure it out... I have a table of Properties. Each property record has about a hundred fields (Size, Address, etc) I have a table of Quotes. Each quote record I put the person's name, email, etc and I want to be able to provide them with a quote on the property they choose, and say "A 1 bedroom will be $1,000 per month, etc. etc" and when I select which property it is, I want it to pull some of the data over like size, address, amenities, etc and some photos in container fields, and then I can print it as a PDF and send it to them. Any thoughts on how to structure it? Thanks guys!

Hi, I am designing a database for a hospital. I have to make around 12 tables but if i get the basic structure of the first 2 tables correct, i will be able to do the rest. basically, i have a patient table which has basic patient details like patient number(unique), name etc. and then there is a disease table which has a patient number field same as the patient table, a few disease fields and a calculated field which simply amalgamates the patient number, date and the site of the disease called the disease_id. I want a one to many relationship between these 2 tables as one patient can have multiple diseases. So 1. What field constraints do i have to …

hey guys! Im having some real trouble working with a relationship and portal... i have a table that keeps records of my reptiles: Record_ID - auto serial Record_Date Reptile_IDCode Reptile_Nickname Then I have a table that keeps track of different types of Morphs (color and pattern variations): Morph_ID - auto serial Morph_Name Morph_genetics so one reptile can have one or more different morphs related to it, on the form showing reptile records i would like to show a portal listing the morph(s) relating to that specific reptile. is there a way to have this listed on the main reptile form? will i need to create another instance …

Filemaker pro experts, I've created a reasonably complex database but with very little knowledge of FMP11. I have a rudimentary background in programming but I have to admit it was a long time ago although bits are coming back quicker than I expected. I have created a report that effectively produces a job sheet for employees. It works really well in find mode in the browser but I want to create a script to automatically save each individual employees work for the month to a predetermined location. Rather than bombard you with loads of code, I've explained in simple terms what I've done. TABLE A - Booking_items includes two critical fields: bk…

I have two tables, Parents and Children. My input screen for each parent contains a portal for entering children information. For each child added I now enter a child number manually. Is there a way to enter the child number automatically. That means for the first child entered, the child number is number 1. For the second entered, it's number 2, etc. Then if we delete one child, the numbers have to reevaluate itself. The reason we need this is for charges. Each child in a family is charged less than the previous one. But if one leaves, we have to recalculate the numbers for the remaining children. Currently, entering the child number is done manually. All the calcula…

I now have my solution running pretty smoothly. But I am having a propblem getting a report to work. I have a Jobs Table, Invoices table and customer table. All I am trying to do is make a report that shows all the invoices made from the job. I have tried in my opinion everything I can think of. I have supplied a pic of my relationships. the group on the left is where the invoices are created from the jobs. the group on the right is where I am trying to get the data to generate the report. On my report every thing shows up except the invoice numbers and the amount of the invoice. Perhaps I am missing what context this report should be based on. I hope the pic he…

Hi All I came up with something I thought was pretty cool today so I decided I would like to share. I don't imagine it's an original idea or anything but I've not seen it mentioned anywhere. So in hopes that it might be useful to someone here goes... This is a small twist on a basic and familiar notes solution that allows for creating related notes that are viewed in a portal on the parent layout, with the ability to select a portal row to view the details in fields on the same layout. I have been experimenting with record creation using the Magic Key technique for those times when I don't wish to leave the layout, as discussed in this thread; http://…

I have attached a file to help see my problem. We take trips and there are passengers. So I have a People table to hold them all. When adding a Passenger, if they are not in the People table we need to add them. Most times, the passengers are already in the People table. I want to make users check the Persons list by opening the pop-up of People and hopefully select the passenger. If the passenger is selected, the First and Last name fills in because FirstName and LastName are from the people table. But if the person isn't there, they need to type into the first and last name fields to create the person in the People table and also insert their PersonID into th…

Easy straight forward approach to managing contacts is to make Contacts a direct child of Company. But many times ONE person/contact will be involved with multiple Companies. So I'm looking for some tips how to set up a oneContact to ManyCompany solution. I have made a "join" table to connect Contact to Many Companies. But this makes for an awkward contact creation experience for the user. i.e. User is viewing CompanyCogs record and does NOT see Dexter in the related Contacts list/portal. So User runs a "New Contact" script. But Dexter ALREADY exist, just has not been tied to CoCogs. So now system has TWO Dexters. Any tips or example of …

...at least I think it's a relationship problem but I could be wrong. Please refer to the attached. (Account Name and Password: test) If you go to the Business-Grouped Contacts or Personal--Grouped Contacts tab, you'll see a radio button value list that, when clicked, should show records that share the same name, company, city or state. Try as I might I just can't get it to work and I suspect I may have the relationship wrong but I don't know. (I can be thick sometimes.) I'm sure it's one little thing that I overlooked, so if you can tell me I'd appreciate it. Thanks! CL Customers.fp7.zip

If anyone can help shed light on these relationships I would be forever indebted to you. With the help of an FMP expert, I’ve been trying to sort out the best way to set up the relationships between 3-4 tables. But we’ve been going around in circles for a while, and it would be great to get fresh perspectives on how best to do this. We are focusing on three tables: Content - Projects - Subprojects. Each piece of content can be associated with multiple Projects, some of which have multiple associated Sub-Projects. We currently have a join table connecting Content- Projects, and a direct relationship between Projects-Subprojects. So the set up is: Content - [Join-Conte…

Ok, I guess this is a really basic question so sorry, but I'm not able to find it exactly. I just want to have 2 tables. STUDENTS with the typical personal data (name, address, phone, etc) COURSES with the name of the course, the hours, teacher, etc. A Student should be able to attend to multiple Courses and also, a Course can have multiple Students. The idea is to show a portal on a presentation for each table. I know how to make it "one to many" but not both sides. Thank you very much!

How would it be possible to join three tables on a single layout using a three portal heirarchal format? Or a single table? I have three tables WorkAreas -> WorkGroups -> WorkTasks. I would like to add a line item to the WorkArea table, select that WorkArea line item and add multiple WorkGroup line items to that WorkArea line item. Then do the same with the WorkGroup; select the WorkGroup line item and add multiple WorkTask items. Production (WorkArea) -> -----Machining (WorkGroup) -> ----------Beam Saw (WorkTask) ----------CNC (WorkTask) -----Sanding (WorkGroup) -> ----------Drum Sander (WorkTask) ----------Stroke Sander (WorkTask…

Taxes on products. There are sales tax, state tax, city tax and it goes on. So when invoice is created and a product added into LineItems, do I add with script each of the tax lines as lineitem records? Or do I use fields for these taxes so they calculate and adjust automatically depending upon the price entered or changed on the product? The problem is, if I enter the product and the taxes on different lineitems in same table then their amounts will not change if I change the product price, which can happen. If the taxes are fields on the product then they would change automatically if I change the product price. Even lookup will not know to recalculate the…

Basically, my database solution is a lead management system, designed to help my sales representatives manage and keep details on leads that my company receives. I don't want my sales representatives to have access to LEADS that they are not assigned to. My solution to this was to have a field in the LEADS table that contains a foreign key from a record in my SALESREPS table. Each LEAD has one SALESREP assigned, and each SALESREP has many LEADS that they are assigned to. My solution works just fine, but I have been trying to figure out the best way to assign a second SALESREP to a LEAD record. Would you suggest adding a second field to the LEADS table containing …

Hello everyone, I have a question about a estimate, job to invoice solution. I have estimates held in a separate table, I convert them to jobs via a script. The invoices that are derived from the jobs are confusing me. Each job can produce anywhere from 1 to 4 invoices (there are 4 departments), separate from each other with different department invoice numbers. I have tried to produce an invoice via the filtered portal idea with the invoice data coming from the job table and the line item tables and using an invoice number table to generate invoice numbers. Almost everything seems to work that way but, I can’t get it to add up the line items up properly. Or should I crea…

Hey, guys. Fairly noobish here. I'm making a DB for the video production company I work for and I'm having some trouble figuring out the best implementation for a particular set of relationships. So, we are hired to do a JOB. For that job, we provide EQUIPMENT. Some of this equipment we own, and some of it we RENT from VENDORS. A job almost always has both owned and rented equipment, and we often rent equipment from multiple vendors for the same job. On the JOBS layout, I have a portal that should show ALL EQUIPMENT assigned to that particular job, regardless of whether its rented or not. The rentals are what are jamming me up here. If it was just…

Hello, I am creating a database of properties in a city. I want to organize known changes over time, (physical changes as well as type of use, business names, addresses, etc.). Just about the only constant over time will be a rough GPS location. In many cases, I cannot ascertain with certainty an exact date of physical changes or the duration of a business occupation. However, for the most part I know the relative chronology. How can I maintain a known chronology among records, yet, as my research provides new information, be able to add new data within the chronology? For example, let's say I know of 4 businesses that occupied a building between 1950 and 2…

Hello, I am trying to filter a portal that shows on a pop up window, but I haven't manage to do it. Maybe somebody can suggest some directions. In my project I am using a very long value list not suitable to a reduction using a conditional value list. Not suitable because there are no criteria that I could used in the conditional value list. So I decided to mimic the value list in portal on a pop up window. My purpose is adding to each Child record the ParentName of its related Parent record. The standard way of doing it is using either a Drop down list or a Pop Up menu with settings: Use values form first field: PARENT::__kp_Parent Also di…

I'm working on a Parts system and need to determine the avarage lead time of the last three deliveries for that part - where lead time is the number of days from order placed to receipt of goods. I can establish a relationship between parts and deliveries so i can see all the deliveries relating to a specific part in the portal. I have a calculated value on each delivery that gives me the lead time on that delivery. I can build a script that when a delivery is received for a part, it finds all deliveries for the part, gets the last three (or less), sums the lead times, and averages them and then stores this result back on the part record. What I'd like to be abl…

Hi everyone, I've been trying to put together a contact / product / role relationship in my database and it's doing my head in. I think I might not have the entities quite right. This is a book database with multiple contributor role types (author, editor, designer etc). Each product is a book. Each contact has multiple contributor roles (one to many). Each contributor role is associated with multiple products (many to many). Each product has multiple contributor roles (many to many) What I've done is create a contacts table, which is the parent to a roles table. I associate each contact to multiple roles. I then have a join table (called contr…

I have a system that logs radio frequencies. For each frequency I need to compare with all other frequencies and all results of calculations. The calcultaions are achieved using 2 frequencies; Therefore f1(frequency 1) needs to be compared with f2, f3, ... fn and have all 4 calculations performed f1 vs f2, f3 ... fn and the results of which compared against f1,f2,f3 ... fn I am trying to achieve this through relatoinships alone, but see how to without a relationship for every frequency Any ideas? Many thanks

I have noticed that sometimes when building relationships filemaker tells you that you need to add another table occurence to the graph. While this seems ok to do and filemaker allows it I just wondered if you do have multiple occurences of the same table that it suggests bad structure/design?

Filemaker The Missing Manual suggests that you can acces data from other tables via implied relationships. I am right in thinking that dependent on the match keys of the tables associated with the implied relationship that you can't always obtain the data that you require and that sometimes it's necessary to establish a direct relationship between two tables using different match keys to obtain the data that you want?

I am new at building filemaker databases. I am trying to build an invoicing database for my work. I want to be able to go to the invoice to fill it out and enter a product code in 1 feild and have the discription show up in the next feild automaticly. Any Ideas? Is there any free downloads of products like this that I could have a look at. I don't mind deconstructing another database to learn how it works. Thanks!

I have a table with a list of cached incoming contacts, all in various user accounts. I want to make a relationship that refers the table to itself to get a portal list of contacts without assigned unique FM ID's like so: Of course, this isn't possible, but it would be nice. Yes, this could be done using a find, but I'm trying to save some steps if possible. From there, I'd add a calculated foundcount() field based on the related records to decide which options to show or hide. Ideas?

Not sure how to even ask this one but I know it is relation question so here goes. My daughter is creating her first db and she wants to sell necklaces. I have standard invoices with line items and products but she wants to change the each price depending upon how many. So 1-3, regular price, 3-5 5% discount and over 5 10% discount but she wants to put the prices in herself so they look more price friendly so they may not be exactly 5% or 10% discounts. Do I make another table for these group prices because there might be 1 or 5 or maybe 10 different prices for a single product? And do I use one field for the 'number' of the group? Like 1-3 and then 3-5? Or…

I know it might be easy, but I'm stuck. I need a join table to be populated automatically. Lets say this tables Persons: ID–Name–Code Animals: ID–Name–Code Materials: ID–Name–Code ALLTogether (JOIN): ID–Data–Code What I’d like to accomplish is: Every time I create a new record in one of the main tables, an automatic related record should be created in the related one with some data from the main table and its code. And so if I delete the record (its related record should disappear). I.e.: Persons: ID: 1, Name: John, || ID: 2, Name: Mary, || Code: (global) PRS. Animals: ID: 1, Name: Puppy, || Code: (global) ANI. Materials: ID: 1, Name: W…

I have an Invoices table (normalized as per forum instructions) and LineItems. We do not have Products but instead have Classes. A class can be $125. Then I add a new lineitem for sales tax and different things all which total the invoice to $155. When payment comes in for $155, do I just apply it to the invoice by selecting the InvoiceID in the lineitem where the payment is recorded or do I need to break down the payment and 'pay' each lineitem individually or can the computer just do it for me or does it even matter? I ask because I was doing it fine then a student paid $145 and I do not know which 'item' to short change since none of them equals $10. I ho…

My question is essentially what is the difference if any between an Auto Enter with replace field contents and a lookup field? I've read a lot of posts that describe using lookup fields but In all the cases I've read the same thing can be accomplished using an Auto Enter with replace field contents. Is this correct?

I'm continuing to try to learn and write an app that was started by someone else eons ago. Here's my dilemma. I'm working on a solution for our local food pantry. Clients.fp7 is the main database. I've created a second Food.fp7 containing records of all our food items plus a set of fields for each food item (a matrix) of how many of any item a certain household size is going to receive. The goal is to look at Clients when he visits, count the number of people then go lookup in Food.fp7 for the correct allotment. Then Print a customized Food Form (via a layout) for the visitor. Unfortunately, I didn't write Food.fp7 so that it would be "inside" Clients thus…

Dear fellow filemaker users, I've been working on a databases containing information regarding a project we do for some people. In short, the relevant part of the database for this question concerns the following ERD: Project < 1- many > Participates < 1 - many> Person <1 - many> Contracts In reality this relationship is more complex, but is irrelevant for this questions (seems to me). My question is the following: We'd like to define which documents are needed for a person in a specific project. So at the project level we would define that Project A requires Contract A, B, C. Now of course this can be easily done by adding another ta…

Hello, I am in process of building a database that will cover most of our university's music department activities and I need some assistance with one area. I have tables for: Contacts Schools Events Contact-Event Join Table The problem I'm having is scheduling the bands for our annual jazz festival. Most schools bring only one band and have only one director, which makes everything simple - I add the director to the Contact-Event Join table and get a list of only the directors participating in this year's festival (and can keep track of that year to year). However, some schools bring multiple bands with the same or multiple directors. Would it be …

I'm trying to reconstruct a solution that was built by a colleague. Far too many entities were built into the same table that have led to the (now) obvious limitations. This solution aims to manage route risks for train drivers on railway routes in the UK. The main table contains 10000 + records. At the moment I have to create a script to find all the route risks that apply to a particular route. There are many scripts and I'm unsure of the total number of scripts that you can have in scriptmaker before you eventually hit the limit. Instead of creating and running a script for each route I would like to have a table with each route as seperate record and then see the…