Relationships

I had this idea to create a "temporary" table in filemaker for searching among thousands of sales records. Our office has several fm users who might be performing imports, deletions, and finds in this new table; but if several people are performing their own imports and record deletions isn't that a problem? Isn't fm always updating the data in this table? For example, if user_1 imports a thousand records into the table as part of a search, and user_2 is deleting those records in order to start a new import, how does fm handle that? Are the records for each user stable? Thanks for your help!

I have a basic many to many relationship. Ie. Two tables. One table contains phone models and the other contains cases. It is a compatibility checker. One case can fit many phone models. One phone can fit many case models. I have trouble setting up the relationships as my phone fitting factor is a defined range: Case size: HxWxD in mm Case: 103x53x20 Phone 102x51x15 The phone will fit this case. The phone that has dimensions of 100x50x17 will also fit the case. The phone that has dimensions of 98x50x17 will not fit the case as the case will be to big in H dimension. The range is H - 4, W - 3 and D - 5 In other …

I am working on a Parts database. Any given part can be an assembly, made up of other parts. I have a Parts table and an Assembly table, as the middle table. I need to print out a work order that shows Parts and for assemblies, the Parts that make up the assembly. The problem is when there is more than one level of Hierarchy: Part A Part B Part B-1 Part B-2 Part B-3 Part B-3-a Part B-3-b Part B-4 Part C Part C-1 Part C-1-a Part C-1-b Part C-2 Part C-3 Part C-3-a Part C-3-b Any help on how to set up a layout to show me something similar to the above? Let me…

I'm brand new at this and feeling completely idiotic. I'm trying to develop a databsse for managing and researching a collection of art objects. I have three tables -- object, maker, value -- that I have set up following the recipe in the Missing Manual. Using the database manager, I established relationships and entered fields. All seemed fine until I looked at them with the browse and layout buttons. The Maker table was missing all its fields except the first Maker ID field. But when I went back into the database mangager, I could see all the fields. After fiddling and checking the online Knowledge Base, I decided to delete the Maker field and try again -- whic…

Hello everyone, I'm trying to come up with a simple scheduling feature for my databases. I run a manufacturing business with daily installs and projects. each project has an assigned date/installer etc. there are 4 different databases that include different info: i was trying to nest all the info from my databases into the Filemaker Calendar 4, so i would have one schedule for each day. i tried the relationship and lookup, but that only works for one record.. any clues?

Hi, and sorry for the interception. I'm studying how to deal with a crosstable as well. I've read all entries in this post with interest. Could it be useful to use a very simple example to discuss all functions needed and if available handling methods for the cross-table problem? I have the impression that the main question "how can I manage a crosstabel situation" are still unanswered. I'd like to use a very simple example for this Table a contains fields id_a (primary key) and a (some value). Table b contains fields id_b (primary key) and b (some value). Table axb contains id_axb (primary key), and fields id_a (foreign key), and id_…

Hi I'm trying to join two tables, hopefully via a drop-down menu. As an example the table structure can be reduced down to the following tableA tableA_ID: Number (a PK field) tableB_ID: Number (a FK field) tableB tableB_ID: Number (a PK field) name: String (some text) There is a relationship between them defined as tableA::tableB_ID = tableB::tableB_ID I have a layout for tableA and I would like to put a popup or drop-down menu in it that displays all the values from tableB::name, and when something is selected from this menu, it sets the tableA::tableB_ID to the corresponding tableB::tableB_ID value. Currently I can achieve this via a butt…

If I have table x related to table y which is related to table z and I'm looking at these 2 relationships within the relationship graph, i have two separate opportunities to SORT table Y by some criteria. If I want table y to be sorted, does it matter which relationship specifies this, and if so, what's the difference? Assume that x is a parent to y and y is a parent to z (if that matters). Related to this question, I have a table that is sorts records by ID descending. So am I correct in assuming that 'previous/next record' commands in the scripts will work with that descending order and thus not operate chronologically? in other words, prev/next does not refer …

I'm fairly new to Filemaker. I'm trying to set up a basic sales order & invoicing system. This part I have so far works, 4 tables: Customers Products SalesOrders OrderLines SalesOrders relates to OrderLines via a unique SO serial number, and the SO-OrderLines portal works fine. But I'm stuck trying to set up the invoicing. Problem is, each sales order may have multiple line items, and all line items may not ship at the same time. So 1 sales order may link to multiple invoices. I don't understand how to set up the relationships between SalesOrders and Invoices, and Invoices and OrderLines. Do I need a separate InvoiceLines table?

Hi I would really appreciate some help on this as i am very new to FileMaker. I am building upon the one of the free business productivity solutions and have become stuck trying to add features. What i need is a database where i can keep track of nuts and bolts which will then be made up into packets. I would ideally then be able to say go in and say that i want to make "5 of packet X" and it would subtract the items needed from the database and add 5 of the packet into the inventory. I have attached what i have achieved so far and would appreciate it if someone could give me a hand here. Database.zip

Hi to all. I have these tables: - agenda: - addresses; - price_list. Agenda is to used to make an offer for renting items listed in price_list to a customer in addresses. Thus, I made a relation agenda -> addresses by means of memorizing the unique_key of addresses in agenda choosing from a drop-down list showing name + location from addresses. Now the problem. I'd like to input the offer having: - a drop-down showing the items in price_list; - show the price from price list; - make calculations to obtain a total amount, etc. I made multiple fields (I know they're obsolete, but as an old RPG programmer I'm used to build up arrays and man…

I have a relationship between two tables but when I try to create records in a portal, I get the error message “pk_payments ID” is defined to require a value, but is not available on this layout. Use another layout to assign a value”. When I click OK (instead of revert) a new record is created anyway. Pk_payments ID is the record Id in the child table, set to auto enter, not empty and unique value. The only way I could eliminate the error message was to delete the not empty and unique value requirements. Anyone know why the above message should occur, and why removing the validation requirements solved the problem?

My goal is to use a single table and build it so I can sort and report it by Assignment and Product. There would only be parent table and child table. Parent being Project and child being Item. The challenge is Items can be labeled either as "Assignment" or a "Product" and a "Product" needs to be related to an "Assignment". Confused yet? I'm having problems making the correct relationships and sub-summary sort set up. Its hurting my brain! Here is a outline of how I wish the report to look: ======================================= PROJECT (parent) ======================================= __Assignment One (child) ____One Product 1 (child related to A…

I'm setting up a database for estimating the cost of all of the built-in's in a house that we're building. The problem that I am having has to do with pricing options for different finishes and materials for specific parts of a built-in. The DB is setup with one main table that has a record for each built-in unit. With in that record, I have it setup to choose all of the different options and variables for that specific unit via pop-up menus. The pop-ups menus pull there values from separate 'reference' tables and a relationship is formed to effect the price of the unit based on which options are chosen. Where I'm running into trouble is when I'm trying to us…

Hi, I'm sure other newbies have asked this one before so I'm looking for articles or pointers or howtos... I have two tables : PRODUCTS and SALES. PRODUCTS is linked to SALES by product_ID In an FM script, how would I select the products that have no entries in the SALES table ? How does FM handle joins when there is no join ? TIA Bobbby

Hi, I am new to FileMaker Pro, we are thinking of using it for work and I am trying to build a database now. Basically I have created two TABLES. One which is our Job Sheet and another which is our Customers. I am trying to link the two so I can have a drop down box in the Job Sheet where we can choose the Customer and then the rest of the customer's fields (Tel no, address, email etc) will automatically load into the Job Sheet (to save us re-typing the information each time. This is possibly quite simple, I have been trying to do it by going through the Help files but keep getting stuck! I would be very grateful if anyone could help me. Thanks for your ti…

Hi Folks, I have a table containing information about climate sensors I manage. I need to determine the most recent record for all sensors by serial number according to the most recent calibration date for that specific sensor and then set that sensor's status to "Current". I currently have a script that sorts the table by serial # and calibration date and then does a row-wise comparison to determine if two adjacent records are the same. From this result I then create a new variable called Current Status. The script seems overly cumbersome and time consuming and I'm thinking there is a more cleaver and less computational intensive way to figure this out. Tha…

Could anyone explain how I can make a list using a self-join relationship of unique (most recent based on descending Date and Time) incidence of a duplicate value? I am a chiropractor and want to generate a list of the most recent visit for all patients within a date range. patients each have a unique ID and the daily visits are identified via a text counter serial number field. I appreciate any help... Thanks!

I have a layout called book. On this layout I have characteristics of the book. The bottom half of the layout is about pages of the book. In the pages area (body, viewing as list) I want all the pages to come up but I only get one. I have a book number that links the two. I also have a layout that views as form. It has a portal showing the pages. This works perfectly fine. Both portal layout and view as list layout are setup the same but I only get one record with View as list. Please Help. Thanks in advance.

HELP!! I'm using the Business Productivity Kit which came with Pro advanced 9. I apologize, but I am a complete beginner although I had previously been able to figure out some things. i am trying to email invoice -which I was able to do previously, but can no longer do this and a message comes up saying 'this action cannot be performed because the required table is missing'. I think I may have inadvertently changed some of the settings ?maybe relationships, but I can't figure out which. I would appreciate any input.

Hi: In the example attached on the Ad Order Table the Start Date is 13/09/2008 and the Expire Date is 18/10/2008. Sales Number only resides on the Ad Order Table. My question is: How I display Ad Inserts in a portal on the Viewer (Join) Table for a specific date range, i.e 27/9/08 to 11/10/08. The data is imported from an external file. VAXtrac.fp7_2.zip

Hi! I have set up a one-to-many relationship that works as expected. However, when my client tries to click on an entry in the portal, he's sometimes (not always!) directed to another entry! I have tried to reproduce this weird behaviour on various computers while logged in as my client, but every time the relationship just works! I'm starting to suspect that it's his FileMaker copy (or his computer?!) that's causing it. Any ideas, or is there something that I've missed?! regards

I am trying to use a FileMaker file that is saved in an encrypted read/write disk image. However, whenever I try to add data to the database I get the following error: "There is not enough room on the disk to complete..." There is plenty of room on the disk image and I can copy other files to the disk image without any problems. It seems to be an issue with FileMaker not liking to write data to files stored in a disk image. Does anyone have any experience with using disk images and FileMaker. Is what I am trying to do possible?

Hi There, This seems to be such a simple thing but I can't get it to work even from scratch. I created a DB with all fields in the same Table, then realised that I was duplicating a lot of data. The database is for recipes, so I initially want to have four tables, one for Reference Books, one for Recipes, one for Ingredient Source and one for Ingredients, ie the quantity, measure used, prep method etc - each ingredient has the potential to be used more than once in a recipe so this is in a portal. The database works OK as a single table but I'm sure it would work better as four. This is because it generates a new record every time I add an ingredient to a recipe, s…

Hi How would I go about sending data inputted in a form to two independate tables?

Hi all I have a database laid out as follows Contacts, sales, and products with two joining tables between contacts/sales and sales/products. I then have a portal in Contacts to show sales. Ideally I would like to be able to enter sales directly into Contacts. Unfortunately I cannot get the drop down list of productID's on the Sales portal in contacts to show up. The portal fields in Contacts are set up the same as the corresponding fields in sales and these fields perform a lookup from the Sales/Product join table. Can anyone poss give me some clues as to what I maybe doing wrong. If anyone needs anything clarifying please don't hesitate to ask Many thank…

i have a table that contains data, i have another table with records that contain images. they are related by a loan number or inspection number. So I can have between 3-5 records in the images table that relate to the inspection. What is the easiest way to create a report to add the images to the bottom of the report without wasting space. I tried using a portal, but since you need to specify the number of records I can potentially wind up with an extra blank page at the end of the report. any one have any ideas?

Hi All, Question: What is the most efficient way for me to use a pre-existing data set such as the one attached to this message? Basically, I already have a solution that contains fields for a person's name, age, and their time on a particular psychological test. I also have large excel tables that look like the one attached. My goal is to calculate my client's scaled scores by looking up where their age and time intersect on these tables. These existing excel data tables is data that is not expected to change (I'm just using pre-existing data from large research studies to my score conversions). I'm wondering if it would be simpler to write large custom funct…

I was SO close to finishing this database. That is, of course, where I slammed into a wall. Relevant part of my relationship table: On a SHOWS layout I have a portal that calls to the PAYMENTS table. I use this to show the payments received for that particular show; the portal that displays the payment list includes information from the LINKS EXHIBITORS TO SHOWS and PAYMENTS tables. This works well with one BIG exception: If a given exhibitor is assigned to two different shows, ALL of that exhibitor's payments (not just those for that particular show) appear within the portal list. The conditions for this is somehow keyed to show attendance; if Exhibi…

i have a database that relates stock items to jobs. mostly the stock items are individual and have as specific "serial number" hence are individual records. In this case if a stock item is related to a job i simply fill in a jobID field in the stock item record and the relationship is made. However some stock items are multiple, like "10mm washers" for example. in this case i reduce the stock quantity to match how many have been used on a particular job. the problem comes when i go to write the relationship. As i only have one jobID field in the stock item record, when i go spread the washers across several jobs the jobID is overwritten and the relationship with all but t…

Hi I have a problem getting a portal to filter properly. I have, for purpose of this discussion, 3 tables Account <- Order -> Product Accounts have Orders Orders are for Products Products have Product Types Product has globals for each Type for example "TYPE_SUBSCRIPTION = 'Newsletter'" Accounts have Account IDs and calculation fields that represent each Product Type. For example there is a field "Account::TYPE_SUBSCRIPTION" set to "Product::TYPE_SUBSCRIPTION" This is done via a "1" = "1" relationship to Product Orders have account IDs and Product IDs Products have Product IDs and Product Types I wish to add a portal to …

Hi there, reasonably new to FMP i have a database that stores information on equipment and the date it was last tested. At the moment it can store when it was last tested but not anything prior to that. I was just wondering what the simplest solution to keep say the last 20 test dates in the database and be able to bring up a report on the item with all its test dates appearing. Attached is the database in its current form Thanks in advance for your help Stuart. Test_Tag180808.fp7.zip

First I apologize if this is the wrong forum for this post. I have two tables, one is a list of contacts and the second is the dates of service interaction. I would like to to be able to see on today's service interaction the last date the contact came for service. Last (Date Seen)provides me with the current date. Help is appreciated!

Hi all, I have the below requirement, can anyone help me to achieve this ? 1. I have a table company, which contains fields: company name & company id. 2. I have a another table with fields: employee id, name, company name & company id. 3. I have created a relationship between company & employee using company id field My question is, 1. I have to provide a drop down to show list of company names to reference to the company field in employee 2. Once the user selected a company name. I need to show the information related to company using company id field in employee 3. If someone change company name in company table then that has to…

Hi all, I am new to filemaker. can anyone shine some light on the following situation? currently I am trying to cross link among a few filemaker files. I have one luminare filemaker file and i have a lot of project filemaker files which have reference to luminaire list. now what I want to do is to has extra column to list all the projects this luminares has been used. many thanks

We have "Product Photos"...literally 1000's of them. A product - identified by a unique part number - can have many photos of it. Some photos contain more than one product. A product can have many photos, a photo can have many products. So I would create a join table inbetween, as such: product ----< product_photos >---- photos Is that correct? Next, we have "Product Illustrations"... literally thousands of these with the same parameters above (many-to-many) so I would model those as: product ----< product_illustrations >---- illustrations I have other "assets" (screenshots, charts, logos, etc.) that need to be related to a product. So I ha…

I'm revamping a PR company's workflow, turning what was once a scattered collection of Excel spreadsheets into an FM solution that will service their entire company. One of the challenges along the way is that they're used to seeing things in a certain way (i.e. spreadsheets), so my goal has been to provide reporting that mimics those spreadsheets. So far so good. Except with phone call reporting. They have the following Excel chart: Rows: clients Columns: employees Cells: number of calls to client per employee I import the phone log data as basically a list of all the calls, with each call having a client and an employee. I've been able to ta…

I have a problem with my solution, I try to integrated a invoicing product solution. The main problem I have is most likely relationship related, but I cannot figure it out. If I choose a Product in the invoice layout under Product ID and I choose a product it wont populate the information throughout the portal. Please see attached file. Log: 1 Pass: 2 I really appreciate any help. Thanks Thomas : Clone9v11_copy.fp7.zip

I have three tables: Table A: Casino Names B:Slot Machines C.Game Types Casino => Slot Machines => Game Types Field(Casino ID)=Field(Current Location of Slot)=Field(Game Type) Very simple question. I want to have a portal in the Casinos Table showing the game types and number of games there. If there is 10 games at the casino, but there are only two game types among the 10, I want the portal to show two rows with the QTY for each row to be 5. This should be easy but I can't remember how this is done. Thanks

I am making a substantial addition to my main DB, which currently has two portals and about 40 fields in the parent table. Question: I will adding about 60 fields for some new forms. The fields could just as well be in the parent table but I thinking that it will be awkward to have about 90 fields in the parent table (awkward for me the author and user). Is there a way to create such a joined table that has a 1 to 1 relationship to a parent table, in order for me avoid have 100 fields in my parent table?

Here is my setup: Tables: Company Table with ID Invoice Table with CompanyID Item Table with InvoiceID, ProductID and ShipQuantity Product Table with ID Order Table with CompanyID, ProductID, and OrderQuantity Relationships: Company::ID = Item::CompanyID Invoice::ID = Item::InvoiceID Product::ID = Item::ProductID This setup allows invoicing and reporting for each shipment. I take wholesale orders - placed once per season - and fulfilled as products become available. Therefore I have an Orders table that has one entry for each combination of Company ID and Product ID (i.e. company 1 orders X# product 1, Y# product 2... company 2 orders…

I'm trying to find out why switching one of my key fields from a static text field to a calculation field is causing the calculation fields of the parent table not to work. Initially, the static text field was manipulated by a script, and the parent table had no problem calculating other fields based on this relationship. Now the parent table's calc fields provide me with nothing. There's something about the field now being a calc field that's breaking up the relationship. Any ideas?

Hi, years ago I ran across an example where in a self relationship you could exclude from the portal the current record being viewed. My searches have been futile. Could anyone point me to an example or provide a clue. Thanks very much. Graham

Hello, I'm trying to understand how Filemaker is managing it's tables and relationships. I've seen examples of Filemaker databases where relationships are created following Codd's relational database design. I've also seen examples of databases with seemingly lots of data duplication and non-traditional key placements. I'd appreciate it if someone could explain to me how Filemaker is dealing with data integrity. Thanks.

Using the To Do List template in FileMaker Pro 9, I would like to set up a script that would send an email reminder (new field) to the responsible person for the project (new field) when the project is is within may 15 and 5 days due. I would also like to send the person an email when the project is mark complete. Both email messages would be brief. Maybe courtesy reminder and thank you the project is now completed. If there is a way to automate this when the file is opened, that would be great. Any sample scripts or other useful information would be greatly appreciated. Paul

Hi, a very very newbie here. I am creating a customer database and that seems to be going well. I also want to keep track and record when a customer visits and be able to review that log of their visits later. I have a table 'customers' where I can enter a customer's information and perform a find. Once found, I want to hit a 'next' button that will log the customer's visit, preferable on my table named 'log' which has two fields: customer serial and a timestamp. I have the button which brings me to the 'log' table but I can't figure out how to auto-enter the customer's serial number into that table. Ideally, I would then like to hit another button and return to…

I have created a database where I catalog my print music, some individual scores and some in a collection. I have created a Layout titled "Collections" to enter songs that are in a single book. My tables are: Collection Songs Occasion Lineitem The Occasion table is to label the music for "Christmas" or some other season. My layout is based on Collections and I am entering songs through the song portal. My problem is I want to enter the occasion as I'm entering the song title and other information. When I enter the occasion, it disappears. When I click out of the portal and back in it again, I then can enter the occasion. My relationship …

I need to prevent users from assuming the company record they want to create isn't already in the database. An exact match of proposed company name to company name won't cut it. Am I correct the way to do this kind of check is a calc field that tests for common mis-spellings and other common user errors and use that for a match? Or is there a better technique? Much appreciated.

Hi all, I have a database setup with these 5 tables (CUSTOMERS, PRODUCTS, ORDERS, ORDER_ITEMS and CUSTOM_CODES). CUSTOM_CODES is used to "override" the internal PRODUCT(prodID) and is linked to the CUSTOMERS table by CUSTOMERS(id). In essence, each customer has its own set of "sku's" for the items they buy. I have made a portal of ORDER_ITEMS related to the ORDERS by ORDERS(orderID). In this portal, I need to list: PRODUCTS(name); PRODUCTS(prodID)/CUSTOM_CODES(sku); PRODUCTS(category) PRODUCTS(price) The problem I am having is how can I tell FM to lookup the value of CUSTOM_CODES(sku) for any CUSTOM_CODES record where CUSTOM_CODES(custID)=ORDE…

I'm an old hand at FMP6 and before, recently migrated to FMP9. I've yet to figure out the functionality of the X relationship. I'm looking at a file with a TO to a TO, both using the same table, both relating to the same table, same serial ID field. The relationship is X rather than =, ≠, >, <, etc. One of the TOs is used for a portal to a layout using the same table. I've yet to see any documentation of this, for me, strange new-to-me relationship. I'm particularly interested in what functionality this relationship provides to FMP. Are there other uses than a portal into itself? Kind regards,

We have a truck dealership and I am using FM to keep track of inventory and customers. Now I want to create invoices out of FM. Since each inventory item only has one sale (one invoice) and one customer, do I need a separate table for invoices? One customer might buy several trucks, but each would be invoiced separately. Would the invoice just be an attribute of inventory? I am having such trouble getting this to work right. Can anyone help me get this started right. Everything I try doesn't seem to work right. Thanks alot Cathy

Hi. I am creating a meeting minutes database. It is related as follows; Minute table--------Meeting number table--------Attendee table. I add the minutes and the attendees via portals in the meeting record-so far so good. Trouble is when i wish to print i need a layout based on the minutes. I put another portal into the title header of the layout and the minutes in the body. Unfortunately meeting number (and associated dates) and the attendees shown are from the first meeting. The reason I set it up this way was because the minutes sometimes carry over to the next meeting hence my portals (and report) only show "open" minutes. I think I might nee…

I've got a reasonably good beginner/intermediate grasp of relationships but I'm now in a position where I have not one, but TWO separate databases (with no relation to one another by any definition) that have the same problem, and I'm stumped. I'm creating a database (or trying to) for a friend of mine who runs an outdoor art show production business. There are three main tables; "Shows" which contains information of each show (title, location, etc), "Exhibitors" which contains information of each exhibitor (business name, etc), and "Payments" which contains information of each payment he's received (check number, etc). There are also a few "link" tables that ar…

i'm trying to put several portals on a layout that lets me quickly look up stuff like this: portal 1 should display the main category (unfiltered, i.e. a list of things). clicking on a portal row should filter portal 2. portal 2 should contain subcategories. clicking on a row should filter portal 3. portal 3 should contain a list of items. clicking on a row should reveal a number of detail fields. - the way i have it set up now is with the table focus on main category. i get the list in portal 1 using a selfjoin. clicking on a row sets a global field to the ID of the main category and portal 2 now displays appliccable subcategories in portal 2. …

Hi - my db has a table of contacts (people), a table of organisations and a link table between the two (i.e. contacts at organisation). The same contact can exist at many organisations (the link table resolves this many:many) however contacts can exist without being related to any organisation too. I want to produce a query that will show a result set of contacts and their linked organisations (effectively 1 row for each link record), COMBINED with contacts that have no link records. I could do this in Oracle using an outer join or combining two select results with a union - any advice on how to do this in fm would be really appreciated. I have found a…

I need two fields for country. One is the standard country name (Unites States) and the other is the ISO 2-character country code (US). My plan was to have the 200 and however many country codes that we use live in a separate database table. From that table we would be able to keep the relationship of the country names and the ISO codes pretty clean. Here's where my issues come up. I've managed to get it work going one way, in which the country name can be entered and the ISO code for that country appears in another field. However, I need this to instead work both ways. Entering "US" in the country code field would put "United States" in the country name and vi…

I have “one” inquiry with many items, if I want to input the price for each item from the selected suppliers after they send us their Quotation. What should I do? Could anyone help me solving this? or share some of experience with me? Thanks My requirements: 1.Type in the items’ name using portal for each inquiry. 2.Select supplier(s) from a list. 3.Input the items’ price for selected supplier(s). 4.Calculate the total amount for each supplier. Regards, James

I will try to explain this as best as i can. I have two layouts, Customers and orders. Each customer is tagged with a unique ID# which is also used for there orders made on the Orders layout. What i need to do is have a field that shows the customers sum of all orders on the customer layout. Now each order placed is a new record on the order layout. I have figured out how to put a portal on my customer layout that shows all of that customers orders but cant for the life of me figuer out how to do this. Hope someone can help

I think I am cracking up with this. I think it should be very easy but my brain is pickled so please help. It relates to tracking sports performance of athletes. I have an athlete table with personal details including age and sex etc. There is also a personal best table linked via an athlete id number to the athlete table. I also have a ranking list table showing county times for each age for a number of distances. I would like to show a portal on the ranking list table of all my athletes with their best times for the distance and age showing on the ranking list record. Its how to link the three tables together that is sumping me. Not sure I …

Hi, I have a DB schema representing houses. The tables windows, heating and cabinets have exactly the same structure. I had to duplicate the tables to make lookup works. Is there a way I could use physically the same table for all three so the lookup would work? Thanx

Hi, Is it possible to create a calculated field in which the result depends on the context? I have a table which is linked to two tables, but in two different context. Let say that physically there's a table D and a table E. Table D lookups in table E. Table D is linked to table C There's another occurence of the related tables D and E in the schema which is linked to table B. Table C is linked to table A Table B is linked to table A So I repeat the question Is it possible to create a calculated field in table D in which the result depends on the context? Example of the calculation: if the foreign key is C then lookup…

I have a need to determine if a customer already exists in the table occurence and if so to use it's original id. Here are the tables: Customer: Name Associate Status Coupon Codes: Name: Associate ID Coupon $ Amt Coupon Code Currently one selects the name in Coupon Codes from those identified in Customers as with a positive Associate Status. The Associate ID is a serial id generated on creation. Then one enters the Coupon $ Amt manually and the Coupon Code is generated by calculation. The problem is an associate customer can have more than one coupon code, but should retain a constant Associate ID. Obviously the Associate ID can't be a …

Hi all I have three tables: customers, Sales and Sales Products. It works really well when there is only one sale per product per customer. The sales table holds foreign keys for the customer and sales product tables The problem is a single sale can have several different customers and one product can be sold to many customers. Basically I need to set up a sales table/layout which can show, for example, several different customers and one product (several people from same company attending a single event) and then generate one invoice for the whole sale. Have been tearing what's left of my hair out trying to figure out. If anyone can give me a few pointers wou…

Hi, For creating children records starting from the parent table, I use a script like: Go to layout (target table for child records) New record/request (new child record) (Capture the child record ID) (Do some other tasks) Go to original layout The problem is that the database is growing and going to the child table layout is not practical anymore because when it does, it shows all child records (several thousands) before creating the new record. I cannot use a Go to Related Record because there might be no related records. Scripting a Find before the going to the layout is not practical either (does not make any sense, anyway: what am I going to fi…

Trying to make a seat ticket reservation something. I have from the main page, seats(images with script) that I click and and with some scriptstep I reserve in another table inside a field for reservation (just picked a value: 0) and I can reserve more than one at the time. Ready for printing, a scriptstep send them to the printer and now I should clean the reserve fields, flag it as sold in this record and the other records in the foundset, and flag the seatfields in the main page that is corresponding to the seats actually ordered. There I have a textfield that should fill the seat reference from the sold/printed ticket and a container(to fill with color flagg…

Hi all, I have created a database for books. There is a 'Publisher' field which is a drop down list. For each Publisher, there is a Distributor and Address details. How do I avoid data repeatedly entering the Distributor details for each new record when I select the relevant publisher? eg. If I choose Oxford University Press, I want the name of their distributor and address details to automatically appear in the relevant fields. How do I achieve this? Do I have to create a child 'Distributor'database to link to the primary and then link the Publisher field to the Distributor fields in some way? Secondly, could I add new Distributor details dir…

I have two databases that are related by a part number field. Information is looked-up from the "parts database" and copied to my other database based on the inputted part number. I have duplicate records for two customers that share the same part number. What can I do to be able to specifically choose which record to use for the look-up when there are more than one related records? I'd prefer not to build the table relationship with two related fields.

Hi folks, I'm creating a database and I'm having problems with relationships (I am a guy after all). I have 3 tables set up, Authors Agents Manuscripts I'd like to import all the old data I have into these layouts, all the fields are correct. Going forth, when creating new manuscript records, I'd like it to grab the information from the Authors and Agents tables, if the Author already exists in the database. I have serial number fields and have had it work to grab the Authors info, but not the Agents. I've attached a copy of my database, it's still in the beginning stages, so nothing is final. Any help would be appreciated. thanks in Advance …

I have a relationship that intermittently displays the appropriate related information. As I was troubleshooting this, I renamed the relationship and, even though the new name carried thru to the appropriate fields, the relationship stopped working. So I backtracked thru and checked each piece, to no avail. Even when I renamed the relationship back to the original name, it continued to work incorrectly. I resorted to a backup and continued adding additional days, when, suddenly, it stopped working again. Any ideas why this calculation, based on different relationships would not work? The field in question simply determines the start time of the shift the record repre…

Hi, I am totally new to filemaker and have made a database containing a materials table which holds records of all different materials and prices per unit of measurement. I also have a quotation table which holds the same materials in separate fields. Is there any way to create a relationship between the name field in the materials table and the separate fields in the quotation table, therefor creating a relationship of several fields and the one name field or should I just give up? I would be so happy with any response as I can't seem to figure this one out.

Total filemaker beginner here, learning databases in general as I try to figure out new solutions. Right now I am working on an inventory db and was hoping someone could tell me what I am doing wrong. I have two tables, Items and Vendors. Because they are many-to-many I created a line item table. I use auto-generated serial numbers for the IDs and numbers for field keys (Item ID = item fk; Vendor ID = vendor fk). Right now I have a portal on the form layout of my Items table that I would like to be able to use to list vendors (and create a record for each). I also have a portal on the Vendor page where I would like to be able to see a list of all the products we ge…

Hi. I'm a total FileMaker newbie and I'm moving data from Bento. Is there any way to set up related fields in FileMaker to be similar to Bento? For example I have a list of client records and events they have attended. The events record shows the list of related clients that attended the event. What's the best way to do this in FileMaker? Thanks in advance!

Apologies if this is obvious to everyone else but me... I have two files, A and B. (Each is an .fp7 file.) In file A, each record has a unique record number. For each record in A, there may be one or more corresponding records in B. B has a field "RecordNumberFromA" I want a script in A to do the following: For the current record in A, create 1 or more new records in B. In each new record thus created in B, I need the field "RecordNumberFromA" to contain the number of the current record in A. My script in A successfully creates a new record in B, BUT the new record's field "RecordNumberFromA" is blank. (Since the field by which A and B a…

I have three tables. Table A is related to Table B using >= and a primary key. ie The _pk in table A must match the _fk in table B, and a Date field in Table A must be greater than or equal to a date in Table B. The relationship shows multiple records in B but its sorted so that the record in B that I want appears at the top. So far so good. Table B is related to Table C by Primary key only. (_pk in C = _fk in B ) What I want to do is display only the related record in C which is related to the record I want in B (ie the top record). What I’m getting is the first record in C, in creation order sequence. Its not the record in C that is related to the re…

Here's a dilemna that I have. I see two different solutions, both have pros and cons. I would like to know your opinion. I am a creating a personnal FM application for car evaluation. People will call to evaluate their car. To evaluate the value a car I need the subject description (wheel type, engine, exhaust, body condition, paint, ...) and comparables which are sells on the market. A subject need at least one comparable, but usually more to give a certain value to the car. A sell can be use for many subject. For now I know that there are at least 3 tables. Subject (pkSubjectID, ...) Sell (pkSellID, ....) Comparable (pkComparableID, fkSu…

i'm back with serial keys, my fav confusion topic. for the longest time, i've used timestamps as serials, but now i've heard that normal (hidden) auto-entered serial numbers are best to use as keys. presumably, the child end of these keys use numbers, which are not auto-entered, correct? now imagine this: User1 has a copy and creates some records. User2 living somewhere else also has a copy and creates some records. User3 wants to get the combined knowledge and asks User1 and User2 for their copy and imports their data. User3 now ends up with lots of duplicate serial numbers and non-related stuff showing in portals. in this scenario, it seems that auto-…

This is driving me berserk and I know it's simple. But I don't get it. I'm including my db. I need to get the reminder, end and grace dates. I got the numbers. But for some reason, I can't do the date lookups/calculations/what the ((&*T&*^*.) Any help would be truly appreciated Jay Gamel issueNumbers.zip

For some reason I am struggling with a multi-criteria relationship and it is driving nuts! I am sure someone can put me out of my misery. I have an order database, and an items database with a sub-table consisting of multiple items each with a quantity and price for discounts. I have created a multi-criteria relationship between the order item and the discount table consisting of the Item Code (=) and the Qty (>=) in order to lookup the price based on the quantity. As hard as I try I cannot get a match beyond two values. In other words in my discounts table I have 4 value quantites, 1, 12, 28, and 35. The relationship will bring back the first two but…

I have 3 tables: Company Addresses Contact The tables are connected by Company:_CompanyID_ ->Addresses::_CompanyID_ Addresses::_AddressesID_->Contact::_ContactID_ Right now there are about 6000 Contacts, 4500 Compnies and about 5500 Address associated with the Companies What I'm trying to setup is to have the user (when in the Contact Table) to be able to select a company that the desired contact works for (from the Company table). I then would like to have the user be able to select from one of several locations that the company operates out of (from the Addresses table) without seeing the addresses associated with the other companies…

Is it possible to calculate with a condition on a relate field? Lets says table B is a product table which has a category field, a price field and a foreign key to a record in table A. The category can be X, Y, Z. Each product has a pr Table A is let say a bill table (I know that's not the way it should be, it's only an example). Is it possible in table A to create a sum field of the price all the products which belong to category Z? In SQL, it would look like SELECT Sum(tableB.Price) FROM tableA INNER JOIN tableB ON tableA.pk = tableB.fk WHERE tableB.categoryID = 'Z'

Hi, What would be the best pratice for specialization type? Here's an example : In a librairy a member can borrow some documents. The documents are not only books but could be DVDs, CDs, magazines, books... Each of these specialized documents have their own field, but also share some fields. Books have ISBN, DVDs a serial number, magazines (volume, edition, ...), but all of them have a title. What would be the best way to design the tables? A document table with all the fields in it. OR a table for each specialization link to a generic "document" table. What's your point of view in this case?

I have a DB "Medical Escorts. I have 3 tables. 1st Table: Medical Escorts. 2nd Table: Itinerary. 3rd Table: Documentation. Each table has "RNME Control Number" as the first entry. I established a relationship with RNME Control Number in all three Tables. I use control numbers RNME 2008-001 using the Auto-Enter --> Serial Number --> Next Value: "RNME 2008-001" --> Increment by "1". Auto-Entry works fine by entering in 1st Table, but does not enter RNME 2008-001 in the 2nd & 3rd Tables. [color:brown]I want to be able to place the control number in Table 1 and have the same control number simultaneously placed in Tables 2 & 3. I have tried to…

Hi, I am new to FM, and I am ploughing my way to The Missing Manual. (I am currently on page 303.) : Could someone lend me a helping hand: what I would like to find out, is how I can have FM opened the next option only if the previous condition is met. E.g.: does this person join the dinner - if so, how much did she pay? Does she have a special diet - if so, what diet? Topics like these are hard to trail in the program or the book. I'd be very happy if someone could point me out where I should look. Now I am going back to the book… page 304… :

I'm working on a database of votes in the california legislature. In one of my tables, every record is a vote. (i.e. if there was a senate floor vote on SB 1 on 1/1/2008, that floor vote would be it's own record, storing, among other things, fields that list everyone who voted yes, no, and abstained.) What I'm sure is easy, that i'm not sure how to do now, is get each record to know if it is most recent of it's kind. Specifically: If i have a record of a senate floor vote on SB 1 on 1/1/2008, i want that record to "know" (like have a new field with a value of 1) whether it is the most recent Senate floor vote on SB 1. for such a record, there …

As in the last post I read, nearly complete n00b; pretty good at Access a few years back, but now just using FMP for my own things. I'm a music teacher, and I have a FMP8.5 database of my students' info and our instrument inventory. I use the serial numbers from the Inst. table as the autocomplete list for the Inst. Assignments on the student records. Some students borrow more than one instrument, so there is a field for Inst. 1 and Inst. 2. Now, I'm trying to get the Instrument records to show the student names that they're associated with. When I link Inst. 1 to the Serial #, and add a single-row portal on the Instrument page to the student name, I s…

Hi! I'm new in filemaker pro 9 and I'm trying to do something but i'm not able. I got a table named model, one named Option and an other one named Model_Option which link the 2 previous table. For each model, I can have from 0 to X options. I'm able to display in a portal all options for a specific model but I would like to display all the options that are NOT used by the specific model. Is it possible ? If yes, what would be the syntax in the graph ?

How can I move data from field 1 in table 1 to field 2 in related table 2? Using lookups doesn't seem to do the job except on new records.

Full newbie here, so not sure if this is in the right place or if its been covered a million times. Dont really understand the gargon so its hard to search efficiently. I am trying to set up a simple datebase to record who has pictures in a number of set magazines. I have 5 fields. Magazines, issue, person, page number and size of picture. I managed to set this up with drop down menus very easily. What I want to do is somehow link the magazines and issues. For example, 1 magazine has 10 issues a year, whilst another has 6. Each issue is named different (eg. spring or Jan/feb etc). I wanted to set up the layout so when you select a particular magazine, it will t…

Example: CRM database with multiple clients and sellers. One seller starts in a new aera and his/her clients needs to be transfered to other sellers. I would like to make it done by a dropdown menu in from the company table (no portal) Tables (there are others as well but not affected): Company Seller I have the same problem as posted on http://fmforums.com/forum/showpost.php?post/299228/ I suppose this is an easy fix - I just don't know how (even after browsing the net for a few hours) Please help... HJP PS. The similar problem would apply when Contacts change Company but then not with a dropdown menu - it would have to be with a …

Using a relationship across two files i need to stop/interrupt a script if the user hasn't yet logged into both files. So if they set their passwords to match it runs. If they haven't I'd like to prompt them to login or at least bring up a window telling them they don't have access yet. Using error capture should I use both of these or would 18 be good enough. Insufficient privileges seem wrong cause all my users should have accounts in all files, even if it just read only. error codes 9 - Insufficient privileges 18 - Client must provide account information to proceed

Hi everyone, I'm looking for a few suggestions / or best practice situations on the following. I'm designing a HR system. As I have it now, everyone will be in one table - Canidates, Current Employees, and Terminated Employees. My question is that the best way, or should I setup seperate tables. There will be about 400 active employees at anyone time, there could be 30,000 canidates, and a few hundred terminated employee records. Do I keep these all in the same table, or seperate them into different tabels, and bring them all together on 1 screen? What do all the experts think. Thanks in advance for your help! :)

I have two databases dbA.fp7 and dbB.fp7. dbA consists of two fields and 1,500 records: articleID and articleBody. The text in articleBody has been styled (bold, italic, font color, etc.) within FileMaker itself. I need to get the styled text into dbB. I've exported the articleID into the dbB but since the text formatting is lost in the export, I was hoping to create a copy and paste script but don't know if there a way to set up a relationship between two completely separate databases? Thank you!

Hey, Please bear with me... I'm not sure if this is the right to post this but I'm hoping so. If not, please feel free to move it wherever it fits in best. Here's my situation. I've got a contact address table within a solution, that contains different types of address for a student. These could be Mailing Addresses, Home Addresses, Temporary Address, etc. They are all start and end dated, so it is noted whether the address is current; and any old addresses can be kept on file for future reference. Also, not everyone will have a Mailing address. In most cases, the home address will suffice. So I need to assume that the mailing address is the home add…

I'm not sure if this is in the right place or not... I have two tables. Customers and Follow-Ups. Follow-ups is intended to keep a running log of every follow-up we do for a customer (date, caller, result). I want to create a layout that will allow me to track commissions, based on follow-up success. The problem is that when I created my list with these columns: Lead Number Name Attempt Date Attempt Result Caller and I do a search for Result "Appointment Reset", it will not necessarily show the portal record associated with the lead that says "Appointment Reset"....it may be any number of attempt records associated with that lead. Does anyone know if…

Still trying to master relationship set ups! I have a one to many relationship and I'm having a prob with something probably simple: I have an individuals table and another table with interview records where the person was weighed at each interview and each person had 3 interviews (so 3 weight records). Question: How do set up the relationship graph so that - other than viewing different weights from weight field from each record in a portal in the individuals table - I can use the average calculation to bring in the weight from each of the separate three interviews into that calculation? What I tried didn't work.

i have the most bizarre bug with my database. its a 2 table database for a magazine (subscribers details and issues sent out) and for some reason a random grouping of them are not relating to the subcriber table. See attached the sentID is the id of the enty in the issues table and as you can see theirs no consistency i have late entries in the 1000 range, 900, 600, heck even 30s and 17 range going up blank Company id is the id from the subscriber table and even thought the id is valid its not loading the subscriber record. All the other field are identical to fields in working records and yet...

I setup an original DB to have four tables: Assets, Customers, Transactions and TransactionLineItems. (relationships: Customers:ID::Transactions::ID, Assets:ItemID::TransactionLineitems:ItemID, Transactions:TransID::TransactionLineItems:TransID). All works fine. I am able to create a layout (setup from the Transactions table) where I can enter the customer number to create an order, I then have a portal where I enter the itemID and it will retrieve the item info (from the TransactionLineItems table). Except now they decided to kit items together. I was able to search the forum to see how kits are done, but I am missing one relationship.. I think.... I creat…

I am working on a sales database, which has two seperate sales tables (one for subscriptions and one for events), and I really need to keep these separate. The problem is I need these to both be connected to a single invoice table. The invoice table has several fields which are filled by means of a lookup but it is not possible to have them looking up the same fields in two separate tables. Is there a way around this. Many thanks for reading

Hi I have a dilemma which is not of my own making but a dilemma nonetheless. I am working on a database which holds records of businesses, contacts within the business and calls or activity. There is a relationship that runs through the above accordingly. What I need to do is create an activity or a call from the business wihout it including a specific person in that business, but have the details of the call show up on each contact related to that business. I have managed to create a call/activity record from the business, but it doesn't show as an activity within the portal of the business record which is a problem. And I haven't got a clue where t…

If I understand this properly, to "show values only from second field" in a drop down list, one 'tricks' the view by putting a text box on top of the one containing the first field. Whereas a pop-up list actually does show the value from only from second field as expected. Is this correct? If so, how does one deny user editing of the second field from that layout? How does one do auto-completion of the foreign ID field that has a secondary display field (the one that ultimately gets put on top of it) while encouraging the user paradigm of using tab to move to the next field? In my example I have two tables, products and labels. The label_id key from the …