Relationships

I have attached a screenshot of the Relationships Graph for a portion of my solution that I have been wrestling with for months. I have had many questions pop up while trying to build this and the straight answer seems to elude me - I am trying to learn. My questions ranged from: 1. Can you relate fk to fk 2. How many TOs away can you "show" related data in a portal 3. How to deal with what I thought / think is a many-to-many-to-many scenario 4. And I am sure a slew of other questions. I am hoping that I can get some feedback from what I have posted here to either reinforce my line of thinking or to (once again) have input that shatters my thinking up to…

Two Questions here, 1) eMails addresses as Match Fields 2) Best way to store and reuse special characters such as Quotation Marks I'm hoping use to eMail addresses as the Match Fields to create a Relationship between 2 tables, STAFF and EMAILS. When an EMAIL is received I want to automatically make it a Child of the Parent STAFF. However Filemaker doesn't seem to like the @ (ampersand) and period (dot) in the eMail addresses and the Match Fields don't join. Does anyone have a more elegant solution than my work around described below? I already have a Relationship between KEY FIELDS with unique values in both Tables. When an eMail comes in I extract the eMail add…

Thanks in advance for your help. I'm defining a field in Products (QuantitySold) with a lookup in LineItems (Quantity) and I get the error "This lookup uses the field 'QuantitySold' in it's definition. Please choose another field." This is the only field I need. I'm not sure why I'm getting the error... Thanks

I am using FMP 9 for the first time, went through the tutorials and user guide. I have a headache and still can't figure out how to do this, which I would hope is quite basic. I have two tables coming from a CAD program, Apartment Schedule (story, apartment number, apartment type) [color:blue]story 1, apt 1, Type A.1 story 1, apt 2, Type A.1 story 1, apt 3, Type A.2 story 1, apt 4, Type B.1 story 2, apt 1, Type A.1 (…) Room Schedule by Apartment Type (room number, room area, room name, occupancy,etc.) [color:blue]Type A.1, room 1, 10 m2, hallway, B3 Type A.1, room 2, 6 m2, toilet, B1 Type A.1, room 3, 12 m2, kitchen, B3 Type A.1, room …

Hello. I'm trying to make a report but I don't know how to show both Fiscal Year values from two separate tables that don't share some of the same Fiscal Years in the FY column: TABLE_1: FY1, data x FY2, data x FY3, data x TABLE_2 FY2, data y FY3, data y FY4, data y I want the report to show: FY1, data x, data y (blank) FY2, data x, data y FY3, data x, data y FY4, data x (blank), data y I can't figure out relationships or new tables or what to make that report show the combined FY of both tables. Any help will be helpful! Thanks

hi everyone, i jus satarted usin filemaker pro7. i have two tables "purchase order" and "job". i was wondering how i can launch the Job table by entering the job code in the purchase order table(form)? do i need to do this creating the appropriate relationship between the tables or filemaker has a special feature to do that?! thank you. P.S. i've already connected the two tables.

Hey guys! Need you help on this one. I came to a problem with this Relation. In the g.Bit there is a "1" and the PostedSwitch goes to "1" when its posted, the ActivQuoteSwitch is "1" when the Quote is activated. The relation should display all Quotes outstanding (not gone out). FM just gives me a blank Portal. Can some one point me in the right direction? Thanks so much. Regards

Hi All, Could some one please shed some light on the topic talbe occurences between portals. I can't bring one field from one portal to another. I tried to find the topic on here but did not see any that related to the topic. Thanks for the help.

I've created a rather simple database, with the intention of sending it out to clients as a report layout for them to fill in each week. I require 3 tables on this one database page. Each of these tables needs 2 or 3 simple columns and new records can be created depending on how much info needs to be entered. I've discovered that the only way to create "records within records" so to speak is to use a portal. I've created very simple portals (and checked and checked that I'm doing it write in various manuals) and have allowed the creation of new records in both the master and related layouts. The information at first pulls through fine, but then when I try …

Hello All I'm struggling with a Data Modeling issue, hoping someone can point me in the right direction before I take the leap. I built a solution for a record label a number of years ago (in the days before mp3s) The core relationship (that I'm struggling with) was. Product (eg, CD) > Tracklist < Tracks Today in the digital age the tracks are both attributes of the physical product (tracklisting on a CD) and a digital product in own right. For example on itunes you can now buy a single track of an album as well as the full album. My initial thought is move to a single Product table (many to many self relationshio - with Albums as bundl…

I have three different websites that I feed from my Filemaker Database. The website feeds require specific column names in order to import correctly. Thus I need to have 4 tables because some of the column names (field names) have the same name across different websites but different data. I have one main table that has all the pertinent data and a Unique Identifier called "Model Number" I have an ebay export table I have an Amazon export table I have a Website export table Each table has a relationship back to the main table based on Model Number. Different fields will pull in the data they need when Model Number matches. My problem is ho…

Hello folx I am currently migrating an old database FMP 6 to FM 9. Now, I have companies and persons. A company might have n (0-many) persons, a person might be connected with m companies (0-many). In FMP 6 I used to make a join file. I am confident, that FM 9 has a better solution than this. How would you solve that issue? Best regards and thank you for your input. Ralph

Sorry this is so verbose - I've been fighting with this for a couple of weeks now, and it's driving me nuts. I want to make this clear. To skip the setup and just get to the question, jump to the last paragraph. I'm creating a rather complex (for me!) FM7 database to keep track of students' grades and performance for an entire year for my small school (8 teachers, 200 or so students). There are about 50 classes, in each class there are about 40 chapters, and each chapter consists of half-a-dozen datums. Students need to be registered or transfer classes at any time (it's a continuation school). The idea is that staff can monitor and analyze student performance across…

Hello Concerning many-to-many relationships, I understand that to create this type of table relationship an intermediary table is needed containing the primary key fields of the two tables in the many-to-many relationship (see below). firstTable: primaryKey1 field1 field2 intermediaryTable: primaryKey2 primaryKey1 primaryKey3 secondTable: primaryKey3 field1 field2 My question is how does a record created in the second table get associated with a record from the first table using the intermediary table? Does the user have to manually create this? Paul

I have a table of country, province, district and village, with corresponding latitude and longitude coordinates. My database contains records of known outbreak locations for infectious diseases. The diseases often break out in the same locations--I would like to create an automated lookup feature so I don't have to look up the lat/long for each new record and use the table of known coordinates. Can anyone help me set this up in FM 9? Thanks in advance, Claudinne

I have an inquiry form with many suppliers. I want to use the checkbox to select the supplier’s name from “Suppliers :: SupplierName” for each inquiry so that the supplier’s name can be displayed within the portal. I want to use it to enter more information such as “Quo date” and each inquiry has its own supplier(S). Could anyone help me and tell me the about relations? Thanks James Checkbox.zip

I have a database which has two tables; "Names" which has the names of the patients of a medical practice. this table has 3 fields (names), (ID) and (DOB) the (names)filed contains the names of the patients and the field (ID) has auto-enter serial number, every patient has one record in this table. the second table "visits" which has a record for every visit any patient has had (i.e. every patient can have more than one record). this table has the fields "Patients Name","Date of the visit" "Complaint" and "Treatment". I want to create a relationship through which i can review all the visits a specific patient had using a drop-down list with its value comes from …

Simple question, but I can't seem to find an answer anywhere - In a Contacts table, I have two fields: 1) First_name 2) Salutation When I type "James" in the First_name field, I'd like "James" to autopopulate the Salutation field (where I could then modify it to "Jim", if need be). I have a hunch I should do this through a lookup, and so I related a 2nd instance of the table to the first - but I can't get the Salutation to autocomplete - it prefers to stay blank. Where have I gone wrong?

Hello, The core of the problem is explained after the green text. You may prefer to skip that part as it's the background to the problem where I explain the local restrictions and my preferences that limit the available solutions. I have a problem. I hope it can be resolved through relationships, because they're fast. Where I work, an external company is developing our FM solution. They are off site. They have the base and upload it to us at intervals. So we have a copy of the solution on our server here. I am on site and I have to create reports. So I keep my work locally in FM files and don't interfere with "their" files. Reports are separate from th…

Hi all, I am not sure if this is the right place to ask this. Currently, I am having a solution that work within 2 files at the same time. So, I established the relationship between two files. I hosted the files in two different servers. Everything works fine in FM client. The problem was arise when I viewed it through IWP. The all process broke. When I debugged it, Fields on a layout will show My question is could FileMaker span / share services on several hosted files through IWP? Or in other words: "Will the relationship work fine between several hosted files from IWP?" All the comments are appreciated. Thank you in advance :-)

Hi, I have a problem, even though I reckon the solution should be easy I seem to be unable to solve that. I have this database, made of 11 different layouts, each of them sports a "Total Value" of the page that is the result of a calculation of a repeating field. What I want to achieve is to have a 12th layouts where it would display the "Total Value" of each other layout taken from the other layouts. I tried to make a "relationship" between the tables, by linking Field A from Layout1 to Field A from layout 12, plus I set the field in layout 12 to be a calculation field. I have been unable to display the sum of Field A though, not only, I would need th…

Im struggling with a junk mail filter. 3 tables -repository holds subject text that is known junk. -intermediate holds subject and message id -message holds subject, body, message id. I perform a find in table intermediate to delete all messages that match subjects with table repository. Once it deletes the messages in table intermediate it also deletes the corresponding messages based on message id in table message. The problem with this is that some subjects may say “Viagra now” so the match with the single word Viagra wont match and wont delete this message. The solution I can think of is to use pattern count from table intermediate to repository t…

Hi everyone. I'm new to the Forum and to Filemaker. I have a question regarding my database. I have created a database that is used to track scores at rifle matches. There are two events, the individual matches and the team matches (not all the individuals shoot on a team). I've done a pretty good job of setting up the individual portion of the database but I'm having trouble with the team portion. The team part of the database needs group team members (if they are on a team it is listed in the indidual layout), pull their scores from the individual layout, total each team score, and sort the teams in descending order. It also needs to ignore shooters from the indiv…

Hi There, I am a novice to filemaker and I am training myself by converting my CD-adminstration from an Access database to FM9. I encounter the following problem. I have a cd table consisting of Album_id, numeric (primary key) Artist_id, numeric and (foreign key) Album title Other fields There is also the Artist table with the fields Artist_id, numeric (primary key) Artist_name, alpha numeric Artist - Album is a 1 n relationship. The albumview shows all fields of Album table plus the Artist_name extracted from the Artist table. Trouble begins when I want to add a new album. Starting in the Album view I use a script which opens t…

I need to relate a group records to a portal by a date range of any record for 'this week' (monday-friday). Each record will have a date field, but I was looking for a slick way to create the key on the other side of the relationship. If Wed...date-1, date-2, date, date+1, date+2. Don't mind writing the calc, just don't want it to be a slow field to calculate. Thanks in advance David

I have two related tables. One is a "Purchases" table, which holds all records of items purchased by a certain buyer. The other is an "Order" table, which lists all those records, based on a particular Purchase order (PO). When a record is added to the Purchases table, it is given a PO number to associate it with that particular order. The Order table only has two fields: the Purchase Order and the Grand Total. If you perform a find for a particular PO number, it will show all the items with that PO number from Purchases, and the Order table will Calculate the grand total. Order is used only for displaying data, not for editing it (since there is a lot more f…

I am creating a CRM DBase, I have 2 Tables (Cases) and (User InterfaceTable). I need to create a relashiopship that shows all Open cases for each User when they log in. I have a gUser field and a gCasestatus field in the User_Table, both are globals. And I have the same fields in the Cases table, both with "normal" data. When I try to view the open case in a portal, nothing gets displayed even though both gUser and gCaseStatus fileds have the correct value.( "Admin" and "Open" are the 2 values.) I have created a new user, ( User1 ) and for this user Open Cases DO get displayed. Can anyone help please :) CRM_Database_Copy.zip

theirs a odd bug in my database and it really has me stumped. See the database tracks subscribers and issues sent out to them but while the issues set database contains to correct reference number (companyid from the records db) its not loading the unique id and thus throwing of various auto calculations and not showing up in the portal in the records list that shows issues sent to that subscriber. And i can't locate the discrepancy in the "off files" i use file maker pro 9. see sample screen

I am trying to find something in a portal but when I put something in the related field (in the portal) and click "Find" the following message comes up: "This operation cannot be performed because one or more of the relationships between these tables are invalid." But in Browse Mode, I see info in the portal.... thoughts?! Thanks in advance

I got two similar db's with contact info (name, phone, address) with key identifier ID#. Similar except separated by time/years. I want to identify which contacts have dropped out of the latter db. How do I form a table of exclusive relations- in the 1st but not in the 2nd database? Thanks much!

Hi I am lookng for some help. I have a spreadsheet with five rows of data for each student. Student A Field1 Field2 Fieled3 Student A Field1 Field2 Fieled3 Student A Field1 Field2 Fieled3 Student A Field1 Field2 Fieled3 Student A Field1 Field2 Fieled3 Student B Field1 Field2 Fieled3 Student B Field1 Field2 Fieled3 etc (there are 350 pupils) I need to place the data from the five rows for each Student on one row for each student. And do this for all students Field1 is a pupil ID number. Does anyone have a suggestion how this could be done? Thanks Tony

Greetings All. We have two exact tables; one receives live SQL data via ODBC the other we would like to use as a backup table of that same data. As records are deleted from the SQL db, we need a way to copy those records exactly field by field to the local table. Best suggestions on how to do this? This is a great resource... Thank You. -DGEE

Hi all, I'm trying to figure out a way of doing this without having to resort to a script. Maybe it can't be done. If a child has a parent, and that parent has another child, then that other child is a sibling of the first child. How do I get a portal to show siblings of a child in a layout based on a child's TO that shows siblings, but not the child of the current record? I've tried a couple of different relationship structures and tried using multi-valued keys, but nothing has worked. I can't get the child of the record to not show up as a sibling to himself/herself in the portal without using a script to set a key in the parent table. Thanks in a…

Hello All! Okay our FM install got toasted with an older iMac. We were able to scavenge the BPK setup and files and migrate to a new Mac. Since then, one of our Sales Order Portals won't function and I am at the end of the universe to solve it. Here's the set up: Table 1: Inventory Table 2: Sales Orders Text entry field name in "Sales Orders": Product ID Portal Fields n "Sales Orders": "Product Description" & "Price" Matching fields in "Inventory"= Descriptio & Price We need to manually enter a product ID, hit enter and have FM 9 get Description and Price from Table: Inventory and enter them in Table: Sales Orders Everything l…

Hello, I need help. TABLE with these fields in questions: _kf_TASK_ID, not unique (related to a TASK table in which the TASK_IDs in that table are unique) ROUTE_TYPE, not unique, values: "Contract", "In House", "Task Order" ORIGINAL, not unique, values: "Original", "Amendment" Basically, when creating a record, I don't want to be able to pick "Amendment" in the ORIGINAL field if there is no existing record that matches the TASK_ID and ROUTE_TYPE that has an "Original" in the ORIGINAL field. So, I know the script/relationship has to read something like: search for the current TASK_ID and ROUTE_TYPE in the Table. IF any of thes…

There are two fp7 files --- a.fp7 and b.fp7. Set the print set in a.fp7 file. If I want to get the same information as a.fp7 when the print set dialogBox of b.fp7 is opened. how to do it ?

Hi all, Please help, my student ID, which is a text field, X1234 for example. file maker seems to "not be" able to differentiate between X1234 and Y1234. when I input a class for ID X1234 it also display in Y1234's record. Appreciate any suggestions. Thanks

Hi folks, I need some guidance here, I have two databases from FM3, I'm now hosting them on FM9. One database is basically an Author contact information database, the other is a database that has books stories etc from those authors, I'd like to combine them into one database. I could do this by importing the tables right? And when adding a new entry in the story database, only have to enter, say the Authors name and have the rest of the contact info auto-fill. Is this possible? thanks Hank

The wrong items show up in one of the portals. We are using FileMaker 6. Background: this is for our auction database. People buy stuff in the live auction and in the silent auction. So we set up a relationship between the file of bidders and the file of items for sale. But we want to print the live purchases and the silent purchases separately. So I set up two relationships between the two files. The items file has a field for live winner and a field for silent winner, and the two relationships are based on these two fields. (Any one item will have an entry in only one field.) But what shows up in the Silent portal is several copies of one of the live purchases…

Hi there! I have a couple of tables (Travels, Companies and Countries) that have a many-to-many relationship. This means that in my Travel input form, I am using portals to add the visited Countries and Companies (1 portal for each). Is it possible to list only the Companies related to the selected Countries in the respective portals?

I have a database with a customers table and related appointments table. I use a related field 'Appointments::Date' on the customers layout and with FM8 this would show the date from the most recent appointment. With FM9, it shows the date from the first appointment. Is there a way to switch back? Or maybe a better way to go about showing the last appointment date? I also use a calculation to show how much time has elapsed since the last appointment. Thanks in advance.

I don't know how best to frame this question, as it only became important to me upon some very recent "big" learning steps. I guess I have two major ponderings... Is there any good example file out there, that shows an efficient way to build the structure for a Product-Assembly-Component type database. I hacked my way through this and am not satisfied with the "cost" of the results in terms of both complexity and speed. Anyone reading or contributing to my Summarize Grandchildren headache has the background to see what I mean. Also, as far as contact (CRM) relationships are concerned, is there a preferred structure, where people have many to many relation…

What is the purpose of the colors in the entity relationship graph? Is there some sort of accepted standard in how colors are applied to TOs? I've recently come into management of a solution where colors were liberally applied to the TOs (blue, green, grey, red, etc.) and I've yet been able to determine what criteria was assessed when selecting their colors. Any input on different techniques used would be helpful. I would like to re-colorize them to fit the situation as I understand the underlying relationships better, but I don't want to start changing things if there's a reason behind the existing colors. Thanks.

Dear FM DB Meisters, A DB i am working with has a file reference which points to a file (eg "olddb") that is usually not there since the file reference is to deal with upgrading the file. There are 3 table occurances that use the file reference. In the upgrade script there is a test if a table exists in the file reference (using isvalid(table_occurance::somefield)) to see if the upgrade should import any records found, otherwise "olddb" doesn't have it to import. Totally unrelated (pun intended) if the "olddb" file is not there, when a sort by the user is performed, one gets the message: "olddb could not be opened. (Not Found)". If the "olddb" file…

Hi, Need some help with this. Can't seem to find the solution I have two tables: Projects Currency In the Project table I have the following fields: Project number (serial number) Purchase Goods 1 in Currency (number) Purchase Goods 2 in currency (number) Purchase Goods 3 in Currency (number) Currency ID 1- Purchase (text) Currency ID 2- Purchase (text) Currency ID 3- Purchase (text) Currency 1 - Purchase (number) Currency 2- Purchase (number) Currency 3- Purchase (number) Purchase amount in Euro 1(number) Purchase amount in Euro 2(number) Purchase amount in Euro 3(number) Sales amount in Currency (number) Fields in Currency tab…

Hi guys My relationships screen is starting to become a tangled web which is slowly getting very hard to see through. Any tips on how to organize your relationships between a large number of tables? I was thinking of maybe using new table occurences for each new relationship group, but that seems a bit far-fetched. Or is it? : Tips welcome!

I have developed a training database which includes tables for employees, courses available, course runnings and courses attended etc. It is a easy to see which courses an employee has attended and which employees have attended a particular course. A key requirement however is to be able to run reports to see which employees have not attended a course especially those courses that are mandatory. I have tried setting up relationships based on the comparative operators but cannot get these to work. I am sure it is me doing something silly but any advice on how I might achieve this would be greatly appreciated.

Have File A with campers info including a field called qualify. Have File B with staff members also with a field qualify. For all campers and staff members that qualify (=yes), I want a combined alpha listing. I'm creating File C with the fields needed for the listing and planning on selecting all yes's for each of File A and File B and copying those records into File C in order to print them out together alpha. I have a feeling there's gotta be an easier way. Anybody? Mike

I need help figuring out how to setup key fields for a FileMaker database that imports from another (mySQL) system and also allows for the creation of new records. The import brings in several tables (eg. student, address, advisor, courses, etc.) all related by a shared unique key field (student_pidm). I need to be able to take advantage of these relationships, but also need to be able to add new records to the same tables in FileMaker without using the same key field. I have considered setting the tables up in FileMaker with two key fields each, and two different relationships. For example, the STUDENT table will have the key fields of "ID_Student" (Filemaker auto-s…

Im having trouble relating 2 DBs via fields that are both calculations (getting index missing). Think this is something to do with storage and indexing. Is there a way around this? thanks

I am new to Filemaker Pro. I have some experience with Access. I just started a new job that requires me to develop a database with FM. I have the database created, but need to do an outer join for reporting purposes. Can I do an outer join in FM? Thanks! Patty R

I am brand new to this program. I have two tables one with staff name and email, then another table with events they are attending. i have a drop down box on the event table to chose the person attending the event. i want this to get their email from the other table so then i can create an email to them telling them. i only want it to display their name in the field, possibly having another next to it that autofills with their email. how do i go about doing this?

Not sure if I am having a relationship problem with my database. I was going to add a serial ID. But when I go to Records to click on "Show all records" it is grayed out. I have 180 records in the database. What could be the problem? Thank you.

Ok, still working on my Time project, redesigning and so on. I have a question about basic table relationship. I will be tracking time and I am not sure how to set it up. I would most likely like to keep track of in and out as individual records. Basically it will be run on touch screen computer. There are four buttons on the layout: In, Out, Break In, Break Out. Employee hits the appropriate button and a keypad layout opens in a new window, employee punches in their employee code and hits enter. New record is created with their employee id, value for determining if they punched in or out (basic field with In text if they punched in or Out if t…

Here is what I have: I have a data file which has a single field per DataID (FileDate). Users will change this date sometimes. I also have trigger file which views what is going on and fires emails as needed. This trigger file needs to see that FileDate has changed for a specific DataID and if so, fire another email. So in theory, if trigger info is different than data info, trigger should see it in a relationship. Currently I have a relationship which is based upon: trigger::DataID = Data::DataID AND trigger::FileDate = Data::FileDate From trigger, I want to have a portal (or relationship) to all records in DATA that doesn't meet that criteria. W…

The wrong items show up in one of the portals. We are using FileMaker 6. Background: this is for our auction database. People buy stuff in the live auction and in the silent auction. So we set up a relationship between the file of bidders and the file of items for sale. But we want to print the live purchases and the silent purchases separately. So I set up two relationships between the two files. The items file has a field for live winner and a field for silent winner, and the two relationships are based on these two fields. (Any one item will have an entry in only one field.) But what shows up in the Silent portal is several copies of one of the live purchases…

I have been trying to add a portal with a self-join relationship to our primary SUPPLIERS table/layout and can't get the function correct. Here's what I have: 1. Added new SUPPLIER table occurrence "SUPPLIERS_Categories" 2. relationship based on; SUPPLIERS > _kp_suppliers_id = SUPPLIERS_Categories > _kp_suppliers_id AND SUPPLIERS > category1 = SUPPLIERS_Categories > category1 3. created a new portal "Category_Portal" in the SUPPLIERS layout with all the portal category fields from "SUPPLIERS_Categories" table reference. I have added a new scripted button: * Go to Object [Object Name: "Category_Portal"] * Go to Portal Row [Last…

I'm burning my brains on something that has to be as easy as 123. But for some reason it doesn't work. I'm working on a timesheet and want filemaker to mark if a person runs a second shift (on the same day). I have a calculation field which generates a unique id by combining the date and employer id. This number generated is unique, except when a double shift is run (date and employer id are the same, so same unique id is generated). I then tried a self-joining relation, where this generated id is the relation. I figured a count or sum calculation would do the trick, no luck... Please comment.

I've been working flat out to create what I've done so far, but am stuck. I'm using another file (MagnumCh7.fp7 from Filemaker Pro 8: The Missing Manual). The related fields from different tables work properly in it; changes are reflected in the other layouts. I can not duplicate this process in my own file (New DB.fp7), despite an exhaustive comparison of the two files. They're both attached, but "New DB.fp7" is the one I'm concerned about. Does the Relationship Graph make sense? Also - to make it clearer later, is it OK to have different names in tables for the IDs which are connected (to make it clearer to me which is the primary key, and which is the forei…

what is the best architecture for a booking system, i want each "booking" to have a list of items allocated to it i have considered a table for bookings, then a table for every item on every booking but this could get a bit big! any suggestions/ ideas?

A Noob question. I have three tables Products, InvoiceLineItems and Invoices where InvoiceLineItem is a join table. Each InvoiceLineItem records a quantity of a product on that line of the Invoice. The products table tracks the QuantityRemaining of a product using a calc field that takes the sum of the related InvoiceLineItem records. On my invoice layout I have a portal with InvoiceLineItems. I'm using a value list to allow product selection via drop-down and would like to omit products in the value list with no inventory remaining. I just add a TO of Products and add a 'InvoiceLineItem::gZero < Product::QuantityRemaining' relation to help generat…

Greetings! We have an access database we've been using to control inventory for years but are switching to FileMaker Pro this summer. Unfortunately I have no experience with the new program and am confused as to how I should go about setting up this database (amongnst many other things). I've always been told the best way to setup a serialized inventory system was to have two tables, one for incoming inventory (tbl_Inventory) and one for inventory sold (tbl_InventorySold). To check current inventory in Access I would run a query finding items in tbl_Inventory that were not matched in the tbl_InventorySold. How would I go about doing something like this in FileMaker? …

Hi there, I just converted pro 6 databases into pro 9. Some functions don't seem to work as before. With pro 6, I used to add items from my Inventory Entry database and send that information to the Inventory database. Fields located in the Inventory db are looked up through the Product Code field. With pro 9, those fields do not look up properly. See the attached folder. Feel free to change things to make it work. Solution.zip

Hi, I'm very stuck and having being trying without success to find out how to do this. Please forgive me if this is an obvious question. I am trying to set up a lesson planning database. I have a bank of ideas in one table with criteria (e.g. written task, verbal task etc). In another table I have the lesson plan. I have created a portal in this table so that I can see the ideas that match the criteria. What I want to be able to do is to select an idea and it complete information in the lesson plan table. Table: Ideas (currently 50+ records) Fields: Title Description Criteria Table: Lesson plan Fields: (amongst others) Criteria (ch…

Hi. We have an IWP-based solution with several generations of one-to-many tables of objectives, projects, tasks, etc. All roll up under a particular fiscal period. We would like to add the capability to create a new fiscal period in the same file, then populate it with copies of all (or most) of the existing tables into it, along with all relationships. When completed, both fiscal periods should exist "side by side" with their respective copies of the child records and relationships beneath them for editing as desired. (Locking the old fiscal period afterward is not necessary, although we can probably figure that out.) Since export/import is not Web-compatible, is the…

Hi Guys, I have two tables related to each other. On the first layout shows records from (table1) I have a portal(table2) also. What i want to do is make a report second layout shows records from (Table1) and show a specific record from (table2). ex. one person many licenses Table1 -------- person1 Table2 ------- License1 License2 License3 Report----- Name | License ------ -------- person1 | License2 I hope you can understand what i'm trying to explain. Thanks test.fp7.zip

Hello again. 'Just got another stumper to puzzle on. I have a table of patients and a table of tests. There can be more than one test per patient, and therefore the test table is the child of the patient table; I've related them by serialized patient-record ID's. Now, the patient samples are tested 2 patients per test slide. So, to utilize certain quality controls and have certain quality assurance, we need to keep track of which patients were neighbors on the test slide. Is that clear? To put it another way, I need to associate patient tests with one another based on whether these tests were run adjacent to one another on a microscope slide. It'…

I have made a database with a portal within a portal. It seems to work just fine. I was surprised because I seem to remember warnings against trying to do this in earlier versions (I'm on 8.5). Is this technique now possible? Are there any warnings or caveats that I should be aware of. Thanks in advance. JJ

I have some fields in a layout in my "A" database that are actually from database "B." The relationship is based on ID. There are multiple records for some IDs in database "B", so sometimes I get the older data pulled from database "B." But actually, I always want it to pull the most recent record for any given ID in database "B." Does that make sense? Anyone know?

I have a "Resources" table which pools all the global fields needed for a solution. I'm migrating to a multi-user server setting. Luckily, most of the globals are initialized and expected to "persist" only for the duration of a login session: they don't have to retain their values across sessions. But there are a few that have to be able to be permanently changed (company logo, most recent batch number, etc.). What I did was -- change all such "persistent" fields to be non-global -- add a global calculation field g_One that's permanently set to 1. -- add a nonglobal calculation field c_One that calculates to 1. -- created a TO based on Resources calle…

I’ve used FileMaker for many years, and now use FM 8.5. The biggest headache I have is naming TOs. I’ve seen many schemes for doing this, but they are all very cumbersome and difficult to adopt. “Squids” is a case in point. I think the problem with these schemes is that they are trying to name a TO in a way that it describes the [color:red]relationship between two tables. This is not very satisfactory. I think a better approach would be to name the relationship between two TOs. For example I would name the relationship between Contacts and Addresses as contacts/addresses. It’s simple and straight forward. I would like to see FileMaker provide a tool to name relati…

Having a heck of a time getting this. I have 2 db's. The first is our main job ticketing system. Each job uses paper. Each paper has a name. That name corrsonds to a paper in my inventory db. That is basically a list of each paper and how many sheets we have. as a new ticket is written, I want to deduct the ticket paper from the inventory paper but for the life of me, can't get it to work. Help

I'm trying to create an order item list for items that are being purchased and my relationship to the item table does not pick up the appropriate items properly. Another table lists the items that could be purchased by customer and it appears that in the items being purchased it is selecting only the first item referenced for this customer. I've attached the relationship chart for reference. Son_relationship.pdf

I am trying to create a many to many relationship with a join table between two People TO and able to view through a portal from both sides. Also need to describe the association from both sides.

i'm fairly new to filemaker but i know a bit about programming. currently i'm working on an application containing 3 tables [patient, test, results] and are related as such [patient <- patientid -> test, test <- testid -> results]. tests and results tables are in a single portal while patient tb is on the main layout. everything works fine when i create a new record. as i enter the info into the patient table it created a unique id for it. then when i go to the tests tb and start to enter data, [in portal on the same layout] it takes the patient-id from patient table and adds it to the test table and generates its own unique test id as the same time. …

Good evening everyone, I am a complete new starter, studying Filemaker and a 980 page book for almost 2 weeks. Please excuse this probably "less in two minutes answer to my question" question: As part of a contact management database, I have created tables company, contacts (people), adresses, telephone, and email. Now I want to create a layout based on company, which displays a full list of company records in a portal. Next to the portal all fields in the company table should be displayed, with all related telephone and email addresses (shown in seperate portals in the same layout). An additional portal in the same layout is then displaying all related contacts…

hi i have been working in a apparel orders reception so i have to order quantities depending in sizes and colors ... here i attache an example .. .. i want to know how to related a table with some fields... i have to record different information from each id product i enter in to the order .. in the same fields... i hope you understand here is my file and pic order.zip

I find that FM is like a foreign language: If I don't use it every day, it fades away. I have what I believe is a simple project, and maybe one I could have done back in the day when I was more fluent in FileMaker. I have four kinds of data: 1. Information on students (names, grades, etc.) 2. Information on schools (addresses, admissions requirements, etc.) 3. Information on associations of schools (northeast, south, etc.; all schools belong to at least one association, some belong to several) 4. Information on scholarships. Some are available for certain types of students (for example, athletes), others are available at certain s…

I have been struggling with trying to understand the basic concepts of modeling - i.e. entities, keys, etc. I have been creating AppleWorks databases since about 1993, and need to port it all to Filemaker for greater stability (and because AppleWorks won't work forever). If I have to, I'll create flat files, but would like to use more of Filemaker 8.5's capabilities, including web publishing, so I can access my databases if I need to while mobile. I want to keep it as simple as possible!! I have no inventory. My business is essentially recruiting and consulting: providing staff for the wine industry. I have a client/prospect file (about 2000 records) (AppleWork…

Hi everyone, I put this in here because I thought it was a relational problem. I have a solution that has a job table (job) and a job line item table (JLI). The JLI table is made up of unit codes and quantities for the job. I want a field that will keep a running total for the units on a job. I need this to be in a JLI field. I created a self join relationship for the JLI table relating the job# to Job# and Unit code to unit code. this does not work. It gives me the summary of all units in the job, not just the unit for that JLI record. Meaning all records in the JLI with the same Job number have the same summary value. like the example below. Each line represents a …

Please people, it is an SOS post: please tell me what am I doing wrong, because I guess it's a silly thing, but I just can't realize what to do! I have 2 tables: their fields "customer ID" is related... What I want to do is creating (automatically) a record in the 2nd table when I create a record in the first one! I have already check "allow creation... " and "allow deleting...". Is it right? Sorry for bothering you with this kind of things but I cant solve it! Thanks a lot!

Many products, from various mfg and grouped into N+1 categories; show classification as filtered list, but if classification not available, add from larger list, and if not correct, add to it. Confusing? Let me detail out the problem in our medical equipment remanufacturing business. **** Problem Set 1 (of 3 related problems) ***** 1) we receive product made by many manufacturers 2) In order to receive it into inventory, as well as be able to selectively find it (Read: Sell it), we need to classify it into categories. 3) Once an item has been received, we want to enter the mfg name (if discernible), and a classification list will be available in the …

Can a relationship be designed to match multiple fields in one table to a single field in another but preserve single match criteria nature? Problem description: Need to lookup a percentile rank value, from table B for each of 15 raw scores, in table A. Table A includes 15 raw sub-test score fields i.e. math, language, science, reading, etc. Table B includes one raw score field (records contain values 0-100) and a percentile rank field for each of the 15 sub-tests (math, language, science, reading, etc). The obvious solution is to create 15 separate relationships that individually match each table A sub-test raw score to the table B raw score which would allow the …

Hi there, First excuse my english because its quite poor. I tried to find the solution of my problem, but I can´t find. Perhaps its too simple... Here is it: - I have two different filemaker databases, one for contacts and other for invoicing. I want to use my contacts data with my invoices and i don´t want to type in all invoices the same data i have in contacts. How can I do it? Thank you and sorry for simple question.

I am trying to decide or resolve how to construct my filemaker database. I need to create what I would imagine is quite a complex structure. It includes: Contacts - several different types requiring different layouts for data entry Groups of contacts - presumably requiring relationships between the contacts and the different groups Events - attended by the different contacts or groups. Various types of events - some requiring group data entry, some requiring single contact data entry. Finances - payments between contacts and other contacts related to specific events The whole thing seems to come to a web of relationships. How many tables are actually necessar…

hola! I've got a many to many working with a join table. Peeps ID name jointable PeepsID ClassID table3 ID classname I have it working so that when I am viewing a Person, I can see what classes they are a part of. And when I am viewing the Classes, I can see what Peeps are in which class. However, I can not figure out how to show what Peeps are in the same class while viewing a Person record. IE, if I am viewing Fred's record, I can see what classes he's in, but, I would also like to see who his classmates are as well at the same time under Fred's record. I think I have to create a TO of the Peeps table, but not sur…

OK, I thought that I understood table relationships, but I'm still trying to wrap my head around it, and I'm feeling dumber with each failed attempt. I've uploaded a copy of my file, which should help illustrate my problem. I have 3 tables - Customers, Stores, and Work Orders. Each table has a self-joined instance. The ViewCustomers object for viewing a list of customers thru a Portal. The ViewStores object links to a global variable "gCustomerID" in the Customers table so a 2nd portal can show only the stores that belong to a specific customer. That part is working fine, however... I really make a mess of things when I try to link Work Orders to …

Hey everyone, again thanks for all the help in the past!!! So I have a silly problem. We have a database that serves the web via CWP (xml/xlst). There are two "types" of records, a "PROSPECT" and a "ACCOUNT", based on these two choices in a single field , "type". All of these records have previously been reviewed and edited for accuracy and duplication. We want to create another database to use to import large lists of information via excel, etc. "LEADS" The issue is, I want to create a database for sales people so they can see a record from EITHER table/DB in one layout. (There will be assymetrical loading of records in each table, so difficul…

Hopefully someone can help here.... As part of a larger solution, I have included an event scheduling module, with a visual 14-day per view calendar. The structure is as follows: 1) There is a Global table which has memberName and viewStart fields. viewStart can be moved back or forward one week at a time, and there are 14 calculated date fields based on its value – i.e. gView1=viewStart, gView14=(viewStart+13). The viewStart field is a global field to allow multiple users to have different values. 2) There is an Events table with owner, startDate and endDate fields. A container field is used to colourise the event based on criteria (i.e. office, sick, meet…

have three tables: staff, time punches and job tickets. time punches is a database to track user's time. they put in a job ticket and the date and time and their name is auto entered. what i need is a portal on the job ticket database that will show me all staff members and the total time spent to date for that job number. right now the portal shows me each instance of a time punch. so if joe shmoe worked for 3 hours one day and three hours the next for job x, i want the portal on the job ticket to report joe shmo 6 hours. this way i have one portal record or line item for each staff member with time on that job. thanks, Limore

I am stuck on creating a relationship in a printed music catalog database. I would appreciate any help you can give me. This database tracks printed music. My desire is to create 1 layout and but view music titles with tab views and portals. An example is attached to this posting. I want to view the portaled music with the following tabs. Song Details By Occasion By Style By Theme Here are my tables Songs Occasion lineitem Style lineitem Theme lineitem Note that each song can have more than 1 occasion, style and theme. My questions isâ�¦can I develop this using only one layout and tabs, or am I going to have to use multiple layo…

I want to have a portal with products and weights. That portal menu takes the info from the product list but the values in the product table should show up also in the data table. It makes more sense to look at the test file to understand what I'll try to do. Is it possible to have Value1 and Value2 showing up from the product table in the data table. relation.fp7.zip

I have two tables [1] a master table containing a numeric summary field that is 'looked up' from a detail numeric summary field in a detail table and [2] a detail table containing several records related to the master table by 'job number'. I also created a temporary portal in the master table using this same relationship to verify that my lookup totals are correct. When I perform the lookup operation in the master table, I get a value from the detail table that represents the summary value of the entire detail table [includes all jobs], while the temporary portal, shows only the records as they relate to the currently active job number in the master table. I…

I have a simple to do list, and I want to present the total number of "to do's" on my systems fron page. Ie: Field: (2 To Do's) Button: (Enter ToDo list) how do I calculate the total number of posts in a table?

What is the advantage / disadvantage of using real names (Company Name / Employee Name) as opposed to Company Code or Employee Code as a means of forming a relationship between two tables? I would like my relationship from my Parent table to flow down and automaticly create the link in the child table (Less data entry less mistakes). I want to bring in other information like week number, manager name, forman name, and company name. I want thes all to flow down to the child table But if the relationships to each field are not made the information will not flow down to the liked tables. My solution was to create a single meaningful code That links and flows down with t…

Dear All, Can someone please help me to resolve my question about the basic table structure in my database. I am creating a database which should contain the following: Contacts Table Students Table Lessons Table I am confused when it comes to the following point: The students are couples. I have to be able to have a man and a lady from the contact list, but I cannot seem to find a way to bring two different 'Contacts' and their respective contact information - i.e. telephone numbers, emails, etc. into my 'Students' layout. I am assuming that this is some form of 'Many to Many' relationship. More than one contact exists in the 'Students' coupl…

I am trying to set up a dynamic value list and played around with some of the relationships to get there but I think I messed up somewhere! I have 3 relevant tables, Companies, People and Job Orders. I am trying to set up the Job Orders table in this way: There is a drop down of Companies that I can select when setting up a new record in the Table. Once I select the Company I would like to set up Contact 1 and Contact 2 (they need to be separate people) from a filtered value list that only shows those people who work at that Company. Once I select the Contact, I would like that person's email and work phone number to autofill. I am attaching a copy of th…

I am brand new to filemaker and this forum. I appologize if I'm not under the correct topic i think I am. anyhow... My issue: I have a database. There is a table of room bookings associated with a billing table by index. This works fine. I have another table that holds the block quantities of the rooms and its not associated with anything other than the type of room. The billing table is not associated with the block quantity table but I am trying to do calculations and have a report display the number of block quantities - the number of rooms booked. I tried creating a joint table with the calculation but this doesn't work. Is there an easier way to get the report to…

Hi, I have a Job DB. I also have a Client DB. When entering a new Job the first field the user needs to enter is Client ID. Client ID has a Drop Down List associated with it. Once the correct Client ID is selected the following fields are filled: - Client Name - Contact Name - Contact Phone Number The problem is this - I would like the Drop Down list to display the Client Name AND the Contact Name. Currently this is not possible because the first field in the Drop Down List needs to be the Client ID in order to correctly propagate the 3 files listed above. Any ideas? Thanks, dmg

Table called "Order" has fields "Serial#" and "Name" and a global field "ActiveYes" which is auto enter set to equal 1 Table called "CustomerList" has fields "Serial#" and "Name" and "Active" The table is filled with customer data and "Active" is set to 1 or a 0 (to tell if the customer is active or not) I can successfully make Order:Serial# pop up a value list showing ALL records from Customer:Serial# but I can not make it show me only the Active customers. Following the instructions in Filemaker's help files, and creating a self-join works only if you are popping up a value list from field in the same table. How do I make it work across related tables?…