Relationships

Hello everyone, I've been using Filemaker in a flat file format for a couple years and just recently am attempting to create relational databases. I'm creating a hardware asset database to track all my schools assets. Right now I have the following tables and fields. To make it easier to follow the tables extensions are .t and the fields extensions are .f as well as bold instances of tables. Tables and Fields Building.t building_id_p.f building_name_p.f Room.t room_id_p.f building_name_c.f room_number_p.f Asset.t asset_id_p.f building_c.f room_number_c.f asset_type_p.f brand_p.f model_p.f Relationships Building.t building_name_p.…

Maybe the following question sounds stupid but I still need someone to explain it to me: What is Table Occurence? What does it actually do? and when is the best time to have TO in our database? Thanks a lot! Rgds, Ren

Hello all, This may be a stupid question, but is there a way to import a table with all it's field definition from one FM7 file into another. The table I want has many calcs and such that I don't want to have to recreate one by one. Thanks

Hi, I have table a, table b and a number of "other" tables. I want table a to relate to the "Other" tables based upon field a (common to both tables), I want table b also to relate to the "Other" tables but based on field b (also common to both tables but not table a). I have successfully created this relationship with one of the "Other" tables but when attempting to create the relationships with the remaining "Other" tables I receive the following message: "There cannot be more than one relational path between any two tables in the graph. Another occurrence of one of the tables must be added to the graph". Can anyone explain why this is the case and how…

Hello Experts, I'm stumped. Working in FMP7 on Mac OS X. Bear with me... SHORT VERSION: I would like to create a value list showing only related records, but the underlying relationship needs to be based on a calculation field which references related fields, and so is not indexed. An unindexed field (correct me if I'm wrong) cannot be used as a match field for a relationship. Is there a workaround to create this value list so that it shows only field values from a subset of records in a table? DETAILS: In my business, I sell hours of work to my clients. I have contractors who perform the work and bill me by the hour. Each pairing of client and co…

Hi, I would be very grateful for advice as to how I can set up a relational db that may need to capture multiple users. I am setting up a research report with a table for staff members, (another staff_id table to be used as a pull down menu), and a research activity table. Sometimes the research activity has more than one staff member involved. I have set up 6 fields based on staff_id and at the moment I am just duplicating the same name six times. If I change the name, based on a pull down menu, in one field it changes in all 6. I need to be able to put up to 6 different staff names against any research activity and also be able to do a search on all the s…

I have a confusing and fustrating problem. I have a Sales Order file and a Purchase order file. In the Purchase order file, the customer service picks a customer name (ex. Intel) that has a customer code (Int001). Then enters the Purchase Order Number (54322). I have a calculating field, named KEY that concatinates the Customer Code with PurchaseorderNumber (Int001-54322), it produces the Unique Key. In addition there is a Purchase Order Value, stating how much money is on the Purcahse order. In the sales order file, much like the Purhchase Order Files has a customer name that has a customer code and a Purchase Order Number. From there it is calculating a Ke…

Greetings all, Has anyone been able to link a photo from the FM7 photo catalog template to another table via a relationship?? I am trying to find out if there is a way to link my photos in the catalog to their invoice and have it all print..... I was able to create a relationship between the two via invoice ID, but don't know what to do next???

I have a database that goes a little like this. table A is all my Suppliers table B is all the Products relationship is from PK_Supplier_ID on table A to FK_Supplier_ID on table B on a layout for table B(products) I have a field that uses a pop-up list. The value list it refers to is one set to lookup values in the field PK_Supplier_ID on table A. It also displays and sorts from the field Supplier_Name also on Table A. so. when I pull down the popup list I see something like 21 Abe's Hardware 132 Bananas Goods 65 Carrie's Cars 423 Donald's Doors and so on. It has the supplier ID (which is what the relationship is based on) and the su…

Hi I have one Table call "Parts" and another call "elements". I need many differents "elements" to acheive a "Parts". So, every "Parts" records have is unique "Parts number" field . Same thing for each "element", they have there "parts number" field wich they are related with. Every "elements" need to have a "materiel type" field fill-up 2 choices from a VL: plastic or metal Here the core of my problem: I did create 2 new calculations field in the "elements" table one call "_plastic" wich = "plastic" and the other call "_metal" wich = "metal" I did also create 2 others Graph of Table "elements" with a relationship to is TO with 2 cre…

Alright - it seems that this must be extra easy but my mind is drawing a complete blank at the moment. I have 2 FILES (FMP-7) - one is a calander solution, the other is my database. I would like to be able to enter my appointment information within my database, and have it Automatically make that cooresponding appointment in the calander. It seems like it should be simple, but I am having a hard time. I have made occurances, and checked allow creation of records - but cant seem to get a record to create in the Calander solution, with the data I am entering in my main database file I think I could do it by having a button with a script attached that set a…

Hi all, I'm very new to FM Pro 7 and having difficulties in displaying records/data from different table. Here is the case: I have 4 tables; Company, Customer, Quote and Staff. 1 Company can have many customer. 1 Customer can have many quote. 1 Staff can have many quote. Summary of the relationship in order: (Company -- Customer -- Quote -- Staff) I've created a layout for quote and I wanted to display data/records from Company table in quote layout but failed to do so. However, I can show data from staff in quote layout. It seems that I cannot display Company's record because company doesn't connect directly to quote. Thus, is there a way to display…

Steep learning curve from 0 to 7 for a new kid in the class. Using the standard Contact Management template that I have expanded to 10 layouts for various types of information relating to various types of contacts. One layout I have developed for network marketing contacts and I am trying to find a 'simple' (as scripts etc make little sense to me at this stage) way of deloping a report or list that shows a hierarchy using a combination of ID's such as the Networkers Personal ID # and Sponsors Personal ID #. Presently I can do a Find on the Sponsors ID and this will give me a list of all of the first level of networkers showing their Networker ID linked to the s…

Hi everyone! I am currently trying to develop a database for my real estate business. I have been a Macintosh fan for over 8 years. I have also come to like the elegance of FM Pro. Now with the introduction of the relationship graph, I thought I can finally put my relational database design theory courses to practice!! Here's the scenario: I want to keep track of my clients (both purchasers and sellers - I am treating them as one entity), their transactions (again I am treating home purchase transaction and home listing transaction as one entity), various contingencies and deadlines (both same things) that I have to keep track of, various vendors that my clients m…

I

I have been looking at the program MetaDataMagic that fixes file reference in filemaker files prior to fm7. My question is, is there anyway to do this without a program?

I have a Products database that includes Manufacturer and Product Name. It also has a Deactivated field. Checking this field would allow the records to remain in the database, but not be

Hi All, I have having a very confusing problem and would be grateful for any help. Note that a version of this message was originally posted in the accounts and privileges forum, but I pulled it from there because it seems it would be more appropriate here. This is a multi-file solution I have been migrating from 5.5 to 7. The file in question (documents) consists of several tables and table occurrence groups, one of which (contacts) is a legacy of the 5.5 system. The others (treatmentPlans and endOfServiceSummarys) were created using fmrobot. What is happening is as follows: when I go into the accounts and privileges define custom privileges for editing o…

Hi, I'm a very new user. I have a flat file database that works well but would like to go the next step and automate some processes. I am a stumped with the problem below! I have no experience with relating info. I am trying to develop the dbase so that i can relate an individuals lists of subject (codes) taken and automate the insertion of a field next to it with their corresponding subject name. The database has the following Files & Fields 1. Enrol ID , Name , Subject code 1 , Subject code 2 , Subject code 3 etc 2. Subject code , Subject name, Unit value Can anybody give me a clue as to what is the best way to relate these files so that i can pro…

I'm working on a service database that tracks jobs on WO (work orders) done by technicians. There are two types of WO jobs. The first is a repair and the second is a PM (preventative maintenance). The only real difference between the two is that a PM consists of a group of repairs where each item in the PM is selected each time and has a special PM number (or letter) associated with that group. Repairs don't have a number. IMPORTANT REQUESTS AND NOTES NOTE: A piece of equipment can be considered both a PM and a repair on the same WO. REQUEST (mandatory): The repairs will be printed on one form and each item from each PM group will be printed on a separate…

Hello, Not too sure where to start. I have learned database mostly with MS Access. I am not sure where Filemaker stands compared to MS Access. Here is my current problem. In Access, when you have a field that is a key to a parent table, you can use a combo box with an invisible first column. The key to the parent is usually a number so meaningless to the user, so it is hidden. The second column presents the meaningful name of the option. The saved value is the value in the first column. I am lost, cannot find how to handle that in the user interface in Filemaker. I am OK with the portal for the addition of multiple record in the child table, but how shou…

Ok, I can't seem to find an answer anywhere and all my efforts don't seem to work. We have two tables, one is TimeEntries the other being ATRateCodes. I would like for when we enter in the values of RateCode and WA# within the TimeEntry table that it will look up the info related to those fields in the ATRateCodes and brings over the data for Internal and External. Unfortunately the lookup definition is that when one of those two fields is entered then do the lookup. We need the lookup to be "calculated" by both fields to bring over the correct internal and external fields. I can upload a sample of this DB if you want to take a look at it... I am just stumped. …

I'm a little new at this so please pardon if I am not asking right. What I'd like to do is when creating a new record for an existing client that all the standard info (addy phone etc.) would be automatically brought in from previous records of the same client (having the same client ID). I'm not sure what you would even call that function. What would you call it anyway? TIA for any help.

I've got four related tables: tblModel, tblAttend, tblClass, tblClassType tblModel: - modelID tblAttend - modelID - classID tblClass - classID - classDate - classTypeID tblType - classTypeID - classCategoryID The tables all relate on the ID fields. What I'm trying to do is create a calculated field in tblModel which shows the details from the last related tblClass record for classes which occurred in the past and which are for classTypeID's in a specific category. I'm able to do the date restriction no problem by adding a global date filter in tblAttend. However, restricting the classTypeID to a specific category is proving m…

Hey all.. experienced SQL/Web Dev here, with minimal FM experience I have the task of correcting data in a related table -- In Contacts, I have fields OldID and NewID. My Donations table is linked to the Contacts table via Donations:ContactID <--> Contacts::OldID. Now I want to achieve the following: For each record in Contacts, - Use oldID to find the related record in Donations, - Update Donations:ContactID to Contacts:NewID" In SQL i'd do an update query, but dont know how to do it in FMaker! Any help or direction would be appreciated! thanks -ekz [[email protected]]

Let me start by saying I have no expereince at all with FileMaker. I have this evaluation copy and need help in makeing it do what my cheap boss wants it to do. I work at a law firm and my boss has recently made the decision to move all of our abstract files from paper to pdf. We have approximately 4000 files that have 5-30 pages in them. Many of them have multiple documents in them. What I am trying to do is create a database that will allow me to attatch multiple documents to a record and be able to view a list of these documents so that the user can launch the document. I will need to add an unkown and random number of documents to each record so I can not …

I am trying to build a small FileMaker workflow solution. (FSA 7 /win 2003srv , FMP, Win 2000 & XP station) This solutions involves a main PICTURE Table with a certain number of Picture Container fields (Picture_N) and corresponding Picture Identifier fields - Picture_N_ID numeric indexed field . Users will annotate different text comments about each picture on the record . The comment history is important and need to be stored, including time, author etc. To hold this comments I set up a related COMMENT table. Each new comment, generates a new record on this table, with corresponding comment_text field., comment_time, comment_date & c…

For a hobby here at home, I have a database with three tables: people, items, and items owned. People and Items Owned are both related through a serial number and Items and Items Owned are related through a second serial number. In the layout for people I have a portal that shows items which that individual might purchase. Once a selection is made, I'm trying to write a script that will create a new record in the table of items owned with the serial number of the person and the serial number of the item purchased. Is there a script step that will enable me to create a related record directly? Alternatively, is there a way to store both serial numbers temporaril…

I am upgrading an exising DB to FM 7.03 from FM 6. I have found that some of my Go to Related Reocord (Show only related records) no longer work. In FM 6 if no related files were present I would have a found count of zero in the related file. In FM 7.03 (mac) I am finding that if the relationship does not have any related records, it is returning all records! I have checked to make sure the match fields are both indexed and of the same field type... any sugguestions?

i have a master file which is the main file i work with. It contains each person's contact info and a bunch of other info on them. whenever a new person is added to the file it generates an Auto-Enter ID number. I am trying to set up an easy way to track donations received from individuals and would love to have them all listed in a separate box with a scrollbar, rather than just having repeating fields. but it seems that the only way to do this is to create a portal with info from a related file. I have created a very basic 2nd file for entering donations, which i figure i can let the development department use and this way they won't be messing with the mast…

Gidday, I have set up a running total of a series of numbers in a simple list of records. When this is viewed through a portal linked by the relationship Week=Week the running total field is only counting the 7 records visible in the portal. Since this is a weekly report I would like the running total to show the value associated with the linked table which is dependant on records outside (preceeding) the viewed week. This worked fine in FMP v 6.0 how do I make it work with FMP V 7.3 Help appreciated.

Environment: Filemaker 5 Server Filemaker 5 Pro WIndows 2000 server Windows 98 & Windows 2000 I have a purchase order file and a sales order file. Purchase order field names Company Code - Text Company - text PONum - Text CodePOKey - calculated(text): Company Code & "-" & PONum Sales Order field Names Company Code - Text Company - text PONum - Text CodePOKey - calculated(text): Company Code & "-" & PONum Amount - Number Relationship: POdb:codePOkey = OrdDB:CodePOkey The Customer code is 10 Characters long. the POnum can be any number of characters long. Is there a limit on how long this compairison…

I have one table of EMPLOYEES... I have a script which shows a subset of the employees on a report. I would now like to also show Prospective Employees on that report. Could someone suggest a way that I can display a subset of the employees, plus allow for a way to show additional people on the report: Prospective Employees I'm thinking these Prospective Employees will live in another table and get created and deleted when need be. I am hoping to be able to have the report view as a table. Is something like this simple to achieve ? I was thinking that I could have a 3rd table in the middle, and then have some sort of "OR" relationship that would lo…

I've been using FMP since the beginning on a Mac, but never dealt much with relationships until now, and this one has me stumped. I'm on FMP6 now. Here's the issue: I have two travel show databases - The first "Addresses" database contains regular address and contact info (with a "Type" field identifying each record as "Airline" "Tourism Board" or "Hotel" and another identifying the country the contact is located in. The second "Locations" database contains the details of all the countries we'll be traveling to. It is in this database that I'd like to pull-up (i.e., relate) informaton from the address database. For example, when I select: "Morocco," from the locat…

I have two seperate tables I can't get one field in the first table to update properly based on a field in the second table here is what I'm trying to do exactly in the Table 1 I have hours and everyone starts out with an initial 250 hours now in Table 2 a ticket is made and if that ticket takes 5 hours then I want that amount subtracted from table one and I can't get Table one to update at all I have a relationship going from one table to the other the field are as follows in Table one it's a number and the one in Table two is a calculation any help at all would be appreciated.

I'm reading a book which recommends that the "relationship name" should be named in a certain way. I have no idea what is meant by the relationship name as I cannot see that as an option when I define or edit a relationship. I wondered if the author meant the related table occurrence, but in FM7 one table is not necessarily more significant than another. Does anyone know if "relationship name" is a hold-over term from an earlier version of FM? or otherwise explain it?

I see some FMP solutions that have many .fp7 files that are opened when the app first runs. I'm not clear on using this technique and what the concept is. Is this somehthing important that I should be doing? Is there an advantage to doing this instead of using numerous related tables?

I am creating a simple Invoice layout, step by step, which uses repeating fields and lookups. The first lookup field worked well, i.e. when I enter the data the related data is looked up and entered into the layout. I then added a second lookup field. What happens now is that when I enter the data for the first entry, the related data is looked up successfully in both lookup fields. When I enter the following items (the repeating fields are set to a maximum of 6 entries) the first lookup I built works each time, but in the second lookup field the related value overwrites the first item. In other words it looks as if the field is not repeating. Am I missing something in th…

Hello all, I've been running around in circles with this database for about a week now, and I thought I'd post it to see what you all make of it. I have attached a strip down version of my database here. The purpose is to keep track of student scholarships by semester. There are several tables: Accounts - each year we would input a list of accounts and the beginning balance Areas - each year we update the list of areas the scholarship money can go to Schol Logic - we then distribute the money from the accounts to the areas (this should be pretty straightforward from the layout) Schol Submit form - we distribute the scholarships The problem com…

PROBLEM? Ugo created excellent calendriers Frnas.fp7. I add few new fields ( calculate: Hebrew Date, Muslim Year, Week) and like to include new Table EVENT with some info about events which I like to have in Calendar layout in extra text format. Create relationship between Calendar and Event, bat that doesn't work Way, were is mistake, what I do wrong? : Calendar.fp7.zip

I am very new to FM & databases. I have been playing around with the Purchase Order Template that came with FM 7. There is only one table in this template. In the Supplier field after I enter the Supplier information once, How can I get the street address, city & state to populate the field the next time I enter the Suppliers Name (in a new record)? Do I need another table to do this or can it be done with only one table. Thanks, Phil

*Deleted by User*

It

Hello, I've been working on a database for a few months now (my first one with FM) and I'm starting to have trouble with something I thought I knew/understood. My confusion is with adding data to join tables. Since there seems to be so many ways to get data into a join I was wondering if someone could make a list of some of the different ways all while keeping a few things in mind. 1. Most of the "ones" in the relationship have a more than just 20 or 30 records. In an example of customers and equipment there might be hundreds of customers and thousands of equipment pieces. This makes me think that a value list is not always the best option. 2. For a n…

Hi Everyone, I am pulling in the last pieces of a conversion and have what may seem like a silly question ... Does the sequence of the data make any difference in speed of access? Example: I have Invoices table 230,000 records (dating back 5 years). Owner now presented older invoices (he wants to keep 7 years in all tables). I imported the records but they are out of logical sequence (both by Invoice Number and by creation date). So, in my wisdom (or lack of), I sorted them by CreationDate/InvoiceNumber and exported the entire table as .mer. My plan was to delete all existing invoices and import them (in logical creation sequence); thinking it would …

I have two files: File 1: id, first name, family name, various fields about student behaviour File 2: id, first name, family name, various fields about family If File 1 is the master file with id being the key field, is there a way of inputting new student id (+ names)in the master file (file 1)that automatically appears in file 2 that I can later go to complete all of the other family information. I suspect I am being complicated and should set up old file 2 as the master file (where information won't change regularly) with associated relationships to provide names etc for old file 1 where the information will be regularly added to...I know what I mean…

This may be a little hard to explain, but will try. I have 3 db's that in one form or another have relationships. A membership db, a convenor db and a registry of potential members and members. When a person joins the organization, it creates an entry in membership db from the registry. One of the fields is the region number (from 1-13). It gets this information from the convenor db based on the state. In the membership db, one of the layouts gives all the info that has been autoentered from the registry (no problem so far), all is working fine, including the region number. There is also a layout for membership information: Name, member number, region and the conve…

A parent table creates sequentially numbered sample names (e.g. Sample34, Sample35, ..., Sample42) that are stored in separate records along with other descriptive data (essentially duplicated data with the exception of the unique sample names). The user determine the number of sample names by entering the number of sample replicates. A daughter layout must show these sample names on one side of the screen so that a user can copy and paste these sample names from the list into a portal on the other side of the screen that is designed to simulate a 9x9 grid storage box (i.e. one sample name per position in box). This would allow us to keep track of where samples are locate…

The company I'm working with rents out equipment that they own (among other things). I have the company listed under the general customer list like everyone else for a number of reasons. Some of the pieces are CURRENTLY being rented. I have figured out how to display these in a portal dynamically. I'm trying to filter out these currently used equipment pieces from the total possible fleet. How can this be achieved? Some of my table names are CE (for customer equipment) and Rental Log. Greg

We have a rather complicated process for Posting our financial information to an Accounting System. The Accounting System is in FileMaker & we do our Posting work with Scripts. We have a FM 5.5 Server & FM 6 Clients. The process works quite well, except that some of our Relationships mysteriously become lost. They are still there, but the file does not recognize the other file it is supposed to connect to. Very irritating, in that it kills our entire Posting process. This morning, I had the same problem. Due to some Password changes, I closed down FM & restarted. When I switched Passwords - everything was okay. (The specific problem has been t…

Hello, New to FM Forums, glad to be here. :-) I'm having a little trouble determining the best way to display the "next task" on a contact view layout. I have three tables: Contacts, Tasks, Join Table The Join Table is for the many-to-many relationships created with multiple tasks and multiple contacts. On my contact layout, I have a "next step" calculation which is currently related through the Tasks table. (Tasks::next) My problem is finding a way so only the most important task shows up on the contact layout. The most important task ("next step") is the one that is still active and due before the others. Can anyone please help me w…

Dumb question but here goes want to show only the last time each product was bought by a customer on their form in portal. want to relate to each unique product from lineitems for that customer and only have that last purchase show in a portal. one of each product. possible? how? i have product table too. tried to use it related to lineitem then put that (product) in portal. got myself quite lost in it. can't relate product because it doesn't have customercode in it. searching here didnt show it was possible without export. that would take forever and wouldn't stay current.

Is it possible to create a button in a portal that links back (i.e., opens) the related database and record showing up in that portal?

Hi everyone, I'm trying to provide a match filtered on Product Description. I've implemented Mike Hackett's (Ender) ExplodedPermutations and the other Custom Functions from Attachment here posted 2/26/2005. It's the second attachment (with ExplodedPermutations). But I placed it in my LineItems and it's STILL updating after an hour and only a quarter updated!! Yikes!! If it takes this long to update does that mean it'll slow my solution down (or bloat the size)? I think there may be an easier way to get what I want (in this instance). Structure and pertinent fields: Pickup table: PickupID, ContactID, gProductName LineItems table: ContactID, …

Which is faster in 7...basing a relationship on a calculation field in two tables that concatenates two fields or basing a relationship on those two fields simultaneously between tables as you now can in 7? This will be for very large databases (many millions of records) so speed is very important.

I'm sure I'm making this harder than it needs to be... I have three files: Clients.fp5 (one record per customer), Invoices.fp5 (multiple records per customer) and Statements.fp5 (one record per customer). Each customer has a unique ClientCode. I would like to open a portal in Statements.fp5 to show ONLY the customer's unpaid invoices from the Invoices.fp5 file. The relationships I was hoping to use to accomplish this didn't work because the fields couldn't be indexed. It works if I base the relationship on a manually entered invoice open/closed status, but who wants to do that? Any thoughts on how best to tackle this problem would be appreciated.

Hi - I wonder if anyone knows how to disable the 'Apple N' function so that it does not create a new record in a file? I have two files - 'Customer Names' and 'Invoices'. I've set up a script in Customer Names which creates a new invoice for the record I'm loking at. So when Bloggs rings me to place an order, I find his record in the Customer Database, click the 'new Invoice' script, and his details flow into a newly-created invoices in Invoices. My problem is that I quite often hit Apple N in 'Invoices' and it creates a new empty invoice. What I'd like to be able to do is stipulate that new records cannot be created within 'Invoices' by any means. They can only be c…

Hi, Let me "try" to be clear on this one. May be best representation again is documents and folders. First Level (Parent Records - Only Folders - ) -> ID1, ID2, ID3 and ID4 Second Level (Folder Records from 5 to 9, documents from 10 to 16) Folders ->ID1-5, ID1-6, ID1-7, ID1-8, ID1-9 ->ID2-5, ID2-6, ID2-7, ID2-8, ID2-9 ->ID3-5, ID3-6, ID3-7, ID3-8, ID3-9 ->ID4-5, ID4-6, ID4-7, ID4-8, ID4-9 Documents ->ID1-10, ID1-11, ID1-12, ID1-13, ID1-14, ID1-15, ID1-16 ->ID2-10, ID2-11, ID2-12, ID2-13, ID2-14, ID2-15, ID2-16 ->ID3-10, ID3-11, ID3-12, ID3-13, ID3-14, ID3-15, ID3-16 ->ID4-10, ID4-11, ID4-12, ID4-13, ID4-…

We are a lab that tests compressed air for our customers. With FM 7 we want to be able to better track when each customers compressor system is due to be tested. One customer can have many systems to test and it is possible for each of their systems to be on a different testing schedule. I know we need at least 3 tables. A table for customers, a table for contacts, and a table for the compressor systems. One customer can have many contacts; and one customer can have many systems to track. Some of our customers also have customers of their own (they are distributors for us). In the past we did not keep track of their clients compressor systems (they handled …

Hi: Stupid Newbie Question, no doubt, but I give up.... We make custom products. We refer to what we produce by our P/N's, and our customers use their own P/N's which must appear on packing lists, invoices, barcode labels, carton labels, etc. Often, however, a customer assigns a subcontractor to make assemblies for them. So now instead of customer "A" we have customer "B" for the part. So now customer "B" purchases our part. Great! But customer B calls our part a different P/N. So we need to have our "orders" table access not only the customer's P/N to cross-reference my P/N, but we need a special lookup which returns customer "B's" part number when they …

I have posted this question in this forum as I think I had it in the wrong forum at first and it seeems my problem is with relationships. I am creating a series of text templates. A list with the names of these templates should show in a portal and when one is highlighted (selected) it will place the template into a global text field. I also want the user to be able to create their own text templates to be added to this list and to be able to edit the existing templates. Here's what I think I need so far: Table 1 - Templates Field: Template_ID Indexed, Autoenter, serial Field: Template_Name Text Field: Template_Text The text of the template …

(I LetterProblem.zip

I am fairly new to FM and all Database programs in general. I have been using FM7 for about 4 months and have been primarily self taught (so please forgive any ignorance). My company creates a monthly profitability report which I import into my database, sort, analyze and generate reports from. Each record has profitability information for a given customer for a given month. One of the reports I am trying to create will sort and total information for accounts based on the sales person responsible for the account. Everything comes out okay, except that for my ROA and ROE fields I have to use a calculator field instead of a summary field, which cannot be added as a subtotal…

Several times, I've created a relationship only to have it fail to operate properly. I double check all the Table Occurrence and Field names, and everything seems to be in order. Where I've gone wrong several time is that one of the fields is a Calculation that returns Text, and by accident I set the "Return type" to "Number" instead of "Text". FileMaker won't warn you that your text field is set to number, and your relationships will just fail silently. Hope this reminder can save someone some hassle!

Hi, Warning- Newbie background: I'm designing a database in FM7 to host my beer (bottle) collection. It is currently a ragged excel file. After reading the FM7 tutorials and stumbling along with the information on filemakermagazine.com, I am struggling with a database design. fact: As we beer chuggers all know, a single brewer comes out with multiple beers, and at times, the same beer gets a facelift and ends up with a new bottle design. Database: I have three separate tables each holding data about the "brewer", the "beer" and the "bottle", repectively. I have an id field (autoenter serial number) for each of the tables identify…

I am no expert here so please forgive me if there is an obvious solution to this problem. I am currently developing a solution for a friend and would like to have a find layout with a portal showing related records. There will be many fields on the layout which are not in the portal, that the user can input information into and the corresponding related records will appear in the portal. I know this can be done with a find request but I would like the portal records to simply update as the user alters the information in the find layout fields keeping in mind that not all field are required to contain data. I think this can only be achieved by an "and" "or" relationshi…

The cross-product operator (choosing the "X" instead of "=" or less than , greater than, etc.) maps all records of one table to all records of another, right? If so, why do we need to pick a field on each side of the relationship? Shouldn't this cross-product relation be from table-to-table? For clarity, I tend to just choose a serial number or primary key field, but wouldn't it be cleaner and clearer if the cross-product relationship didn't require you to specify key fields?

I have a shipping file that has a record for each customer purchase as follows: Record 1 Name: joe Smith Address: 45 J st Item purchased: widget 34 Weight 5oz Record 2 Name: joe Smith Address: 45 J st Item purchased: widget 57 Weight 10oz Record 3 Name: joe Smith Address: 45 J st Item purchased: widget 16 Weight 5oz How can I determine the total weight for this customer when all three of the widgets are in separate records? I think a self join relationship would work somehow, or creating a new record with globals for the weights?? Attached is my work on a script to find the duplicate names I am close but I have hit a…

Given the existence of table aliases (Table Occurrances) in FM7 and bidirectional realationships, it seems that naming your TOs clearly is of increased importance. Has anyone come up with a clear way to do this? So far, the best thing I've come up with is this method: "[TableX] to [TableY] by [MatchField(s)]" e.g. "Worker to Salary by WorkerID Date" However, I'm not totally satisfied with this for several reasons. First, since one table is listed before the other, it tends to imply a one-directional relationship. I suppose since the TO is linked to a specific table, this TO should be listed first. Also, if you use long table names, the match fi…

I have developed a brain fart on this one. I think it is a simple solution but I just can't think of it right now. Here is it: I am devoleping a Dispatch database for a small ambulette company. What I am trying to accomplish is this. When a person clicks on a the unit number to assign the call I want the driver of that unit to appear on the main screen. I.E. Click on unit field, select unit number and in a box next to the unit field the drivers name appears that is assigned to that unit. I have created a relational data base with the field unit 1...unit 2.. and so on, with the driver name in that field. Now as I have said in the main database when I click on the unit nu…

i have a problem with a very simple relationship lookup in filemaker 7. i have a client contact database and a billing database. i want the client info placed in the billing database when i enter the clients id number. i established the relationship between the 2 databases based on client id number and setup what info to be taken from client info database and placed in the billing database fields but it just does not work (mac platform). ...maybe i am missing something, i am beginner but something seems weird about it. any help greatly appreciated

When using a related field on a layout (without a portal) FMP shows data from the first related record right? This still seems to be true, but if you use a field that is more than one relationship away, (again not in a portal) FMP seems to be inconsistent with what record will be displayed. Has anyone else noticed this problem and understands it?

Hi All, I have a relationship which is based on a calculation field which combines a text and a global field in the parent, to a text field in the child file. This allows me to have my portal display different subsets of records for each parent as elected by the user. The relationship and portal work perfectly and all the valid records appear when the global is changed. What I can't do is a search on any of the fields in the portal for this relationship, it always says there are no matches. Can anyone explain this to me? Thanks... Sean!

I am building a training registration system made up of a Course Registration DB and a Course Scheduling DB. The Course scheduler also summarises the registration data (number of student confirmed, withdrawn, waiting list, transferred, substituted) and fees (paid by cheque/cash/credit card, paid by invoice, paid by voucher, paid by transfer, not paid, invoiced etc). There is a payment summary layout in the Registrations DB with a portal that shows the payment methods, amount owing etc. In the same layout there are pipe fields that show the amount summary data from the Course Scheduler DB. The payment status fields in the portal are calculations based on how much has …

I am having a hard time making a relationship between files that gives me just what I want in a portal window. This is a simplified example of my solution but one that gets to the heart of the matter. I have three tables, Computers, Software, and SoftwareInstalled with the following fields in each: Computers - compID, computer name Software - softID, software name SoftwareInstalled - compID, softID The Computers table contains 800 records, one for each computer on campus, compID being a unique serial number for each machine. The Software table contains 200 records, one for each piece of software we own, softID is a unique serial number for each pie…

I copied last year's complex relational db from FM6 into FM7. Once the conversion was complete, I made mostly superficial changes to the database, keeping the relationships to the other tables in tact. Things were looking good- all scripts/portals/relationships were working fine. Then...while converting all of our databases to FM7, I ran into a problem. The 05 database has all the same titles as the 04 database. This wasn't a problem when the 04db was in FM6. Now that they are both in FM7, when a script in the 05db tells it to open X, it opens 04's X. I have tried changing the titles of the tables to be X05, however that made the files 'unmodifiable'. Any…

Hi all, Another stupid, probably simple problem here- but I've been at it all afternoon! Basically, I have a couple of dozen databases, one for each 'group' of records. What I'd like to do is have all these databases in the background, linked to a 'master template' and have the data entered into this template be assigned to a particular database, via the contents of a field which would form, I assume, the primary link. ie. if you enter '2' into this field, the record is added to database number 2, as database number 2 has the same field related to the field in the master template. But I can't see any easy way of doing this. Am I better off consolidating it all into o…

I have 4 tables, names, productsales, productsalelineitems, contributionlineitems. names -> productsales on custID. ProductSales-> lineitem tables on Invoice#, name-> lineitems tables on custID. All sale items go into the productsalelineitems table. All donations go into the contributionlineitems. I want to set a flag that shows me whether a customer is a donor or not. I created a calculated field, DonorFlag, and used the following calculation: If(Not IsEmpty(contributionlineitems);"Yes";"No"). This seems to work for some but it is not working consistently. Should I be using another technique to get this info? Is it working inconsistently because I ha…

Just when I feel like I'm getting competent... I realize I am still quite a "newbie". I am trying to implement the freeware pop-up calendar "Minical" into my database. I can't figure out how to pass the date back to the field I want in the record I want in the original database. Can anyone give me the broad strokes of how this is done? (Through a script step "Set Field", through a related record...?--which I've tried and failed at getting to work.) I'm sure this is supposed to simple because I can find no documentation on how to do it. Thanks in advance.

Hi there, I'm sure there is a simple solution to this, so any thoughts would be much appreciated. I have a database with 3 fields, Title, Language and ID This is relating to another database, where these values would have already been defined. Users will not know the ID, but will know the title and the language. I have created a relationship between the databases, with Title being the key field When someone types in the Title, I have a pop up in the language box that lists the possible languages that the particular title could be. When someone chooses the language how do I get the unique ID that corresponds to that combination of t…

I am not sure what would be the best way to set this up. I have a relatively simple Oncall schedule database that has 5 fields: -Name -Specialty -Beeper -Start Date -End Date I'd like to be able to create a report that would show the dates inbetween the Start Date and End Dates. So if they enter 2/28/05 for start date and 3/4/05 for a end date, it shows them scheduled for 2/28, 3/1, 3/2, etc... This may not be the right forum for this and I apologize if it isn't but since this is strictly a report type thing, I though this might be the appropriate place to post. Thanks for your help! Chris

ok so here's what I have...i want to create two separate portals (cheques and invoices) to be displayed on one layout (profit overview). I had to create an aliased table for the relationship to work so that invoices and cheques are sorted out by month via a value list. however, the portal linked to the original table works, but the portal linked to the aliased table doesn't. I am so stuck, and a newbie - please be kind if i'm simply being thick!

I have two files, one where invoice information is enter and another where I format that info for the email to send to the customers, now in both fields I have a field called con it's global and set to auto enter on creation now what happens is when I create an invoice, in the invoice file the con field auto updates and it's suppose to autoupdate in the email file as well but it doesn't I have to manually enter in the con field the corresponding number then the data shows up correctly, how do I arrange or script this so that I don't have to manually enter the number, I'm going to try to attatch a screenshot of the code I'm using for my email button in the invoice file, it…

I have a solution where I may have to list all invoices for 10,000 companies. Currently I loop through all the customer records and collect their key field into a single global then use the global to relate to the foreign key field in the invoice table. This obviously is slow. Is there a better way of doing this?

Ender replied to this with " You should relate Contact directly to Company by CompanyID, keep the relationship between Company and Location, and add a second table occurrence of Location and relate that to Contact by Location Ref. What this gives you is: A Company can have many Locations. A Company has many Contacts. A Location can be used by many Contacts for a Company. On the Contact layout, you can choose the Location (use a conditional value list to show just the Locations for that Contact's Company.) Then using the Location by Contact TO, show the related Location fields on the Contact layout." And this is as close to wrapping my brain without us…

Databases being used. 1. Proposals.FP5 2. pInvoice.FP5 3. PartsList.FP5 Database Overview: I have many parts stored in the "PartsList" database. These are listed based on category in separated relationship layouts. I then click on a part and it is added to the 'pInovice" database where related items are displayed in the "Proposal" database. The Problem: If a part is selected say the item named "ClassSpeed" an item named "Sybase" must also be added automatically. Basically I need a trigger for an additional item. Thanks in advance. iRage

HELP!! I am trying to create a relationship from one file to another but for some reason, after I have created the Relationship Name and located the file and selected the match fields, the relationship is not confirmed and I get "File Missing" in both the Relationship & Related File columns in the Define Relationships box. The match fields are "Calc/Text" in the Parent File and "Text"in the Child File. I have 25 current relationships set up. Both files are on a network and I have created other relationships in other files as a test and these seem to work. Have I reached the maximum or is it something more sinister??

Hi all, I'm very new. I am using FM7 and the inventory template. I am putting items in the inventory. I have an add in that creates a barcode for whatever name is in the "item" box. It then puts it on a seperate layout page which is blank, except for the generated barcode. I print the barcode onto a label. What I need to add is the actual "item" name to the layout page, directly under the generated barcode. Then I could print out the label with the barcode and the item name directly under the barcode. I had someone else set up the barcode thing in this. I know nothing about this except how to add items, and print out the labels. Can someone explain to me (as a new person)…

I have a calendar solution that is related to a file that has events in it. The calendar relates to the file by the date feild The prolem llie when I have two events on the same date, the calendar solution only show the first one sort by aplphbetical. The calendar solution shows a calculation feild with multiple fields of infor from the events file. Any ideas ?

First let me say that I hope all have a fantastic day today. Now to the meat of the matter. I've got a Student table joined via an Enrollment table to a CourseSection table. Also, I've got a Course table which describes the course & possibly the assignments that the course should expect to be performed by each student, due dates, max grade, class attendance, etc. My goal is to generalize the recording of the Student's grades. I'm trying to figure out a construct that allows the Enrollment Table or another closely tied table to copy, use or reference the Course assignments as sort of a master that each student enrolled in a course can be tallied ag…

Hi, I've set up a Client database linked to the TimeBilling Template that comes with FM 6 templates. My problem is to create an invoicing layout that uses the repeated Billing ID of each "department"for every customer and give me a single result per department. Example: Customer "Frank" has 5 jobs billed on different dates by the Carpentry department,(Carpentry ID=12) 2 jobs by the paints department (Paints ID=5) 3 jobs by the engineering department ( eng. depart. ID=2) Is there a way to have an Invoice showing the "customer" each "Department's Total" and a summary of all the above "Departments" My Customer…

Ok this may have a really simple solution cause so far the only thing I've come up with involves a ton of coding. What I have is one database with a particular 6 fields. 3 of those fields are identifiers. and the other 3 are data. For example, let's assume it's a Quoting database. Product equals X amount of dollars. In my database I have field HH#, VS#, XR# which are all unique codes to this product but identifying different clients. They each then have a different cost related to this product marked as fields HH$, VS$, XR$. Now in a simple lookup I would just look up the HH# and get HH$. What I need to do is be able to lookup each of those different # …

I am in the process of updating our order system from a single file that has a field for each item that can be ordered to a new system that stores the items in a seperate Line Items file. For display and orginizational purposes I have a script that imports 1 of each particular item into the Line Items database which makes order entry much easier as opposed to selecting from a drop down list. My concerns with this system are: 1) Might I encounter problems when this system is deployed and more than 1 user tries to create a new order at the same time? Right now when a user clicks the New Order button a script is run in an Items file which finds all items marked for…

Does anyone know of a Best practice in relation to File References as far as using 'file:' vs 'fmnet:' and the practice of having multiple file references, and the order that they should be added Any info would be appreciated Thanks Halbj

I'm having trouble with database that I'm doing I have about 5 fields COMPANY, START TIME, END TIME, EVENT, and DATE. I want the same information that I enter to show up in another file that I have with the same fields, can someone help point me in the right direction?

I have two related tables -- 1. Invoice Header 2. Invoice Line Items. The key field connecting these two tables is Invoice Number. When I import invoice line items records into the Invoice Line Items table, how can I automatically create parent records (with just invoice number recorded) in the Invoice Header table? (Of course, there will be one record in the Invoice Header table for each invoice number). Thanks.

Example : Record 1 , Field A = 100 -- Record 2 , Field A = 80 How do I create a field which shows total of 180 adding the 2 fields which are the same (A) but in different records. thanks

I am running a complicated set of data tables; there is a data dump that needs to be performed fairly regularly. The only way to assemble the data dump involves joining data sets from two different (but related tables). Since Filemaker doesn't support the 'append' function when exporting data to a text file, I am forced to perform two separate searches in two different tables, and do an import from both to a _third_, temporary table, and then dump all the data from that third table to a text file. All three of these tables are in ONE Filemaker file. Is it possible, when adding a File Reference to my import scripts, to use some kind of shorthand notation th…

Greetings all, I've been charged with fixing some problems with my company's current FM solution. The crux of my dilemma is this: each record has up to 400 comments associated with it... and they need to be printed. This is currently done by way of (gasp!) Repeating Fields, but since they tend to be mighty unstable I was hoping to move away from them. I'm experimenting with using a Portal to replace the repeating fields, but unfortunatly, i'm having difficulty getting the portaled fields to print (yes, i realize portals were not meant to print, ugh). Yes, I also have performance fears for the portal solution, but I really can't think of anything else to do. suggetion…

I have a newbie relationship situation I need help on: I have a Patient Table/Layout