Relationships

I think this is an easy one but i'm coming up short. I attached a copy of my relationship chart I have created a relationship for field "Lamp Type" between the "Audit" and "Lamps" table. I have a value list that to include all values from the "Lamp Type" I've created value lists for fields "Number of Lamps", "Lamp Wattage" and "Ballast factor (lamp table)". I've pulling those values from the "Lamps" table and have selected "related values only". What i'm looking to do is reduce the amount of values that are returned in those three fields to just what is selected from the previous. So for example - currently this works - First I would select…

Hey guys. I'm new to FM and while the database creation itself is extremely simplistic, having multiple tables talk to each other is just going way over my head. What I have: I'm using FM Pro 12. I started with the Contacts Starter Solution, and then created a new table - Events. I plan on using this table to create a database of client weddings. In Events I have a tabbed panel, one tab being "Client Info" in which I have two identical forms: Bride's info and Groom's info. Things like first name, last name, address, phone numbers, etc. What I want: When entering a name into the Events table's "Client Info" fields, if that client already exists in the Con…

Is it possible to assign 3 seperate companies to one invoice with each company providing possibly more than one service (each service has its own rate) and then show a sub summary report displaying values sorted by the company, then by the service and a total value of all services performed? l have tried many ways to no avail. We currently use excel...one spreadsheet for the bill of lading recaps and one spreadsheet for the paysheets. There is the possiblity of mistyping data when transferring totals so we want to put it in one database.  I and answer more questions if neccessary.  Dave

i am new at this. We have several different sales offices we do work for. I am trying to create a database that will allow me to see orders from all offices but the offices will only be able to see orders for their office. I do have some sales reps the work at multiple offices. Not sure how to do this. i tried to att. the database but it will not allow me.

Hello Guys, I am new to filemaker. I am making an inventory database. I have a problem in making relationship with three tables. Filemaker saying more than one relationship path is not allowed.  PO PO_ID PO_Date Vendor_ID Vendor  PO_Lines PO_ID Item_ID Item_Name Qty Price Amount  Vendor_Items Vendor_ID Item_ID Price   Circular_Test.zip    I want to to filter records in Vendor_Items by vendor_id from PO table and Item_ID from PO_Lines table. I have attached the database. Any help would be apprciated.  Thanks, Siva  Â

I'm not sure where to place this question as it seems to have overlapping content. But I'm trying to remember how to filter a relationship by way of a SQL list. I worked this out at the portal filtering level but it's too slow. But essentially I've created a conditional list based on selections made from four global fields on the display layout and table. The selection fields are not on equal footing to the extent that selecting "all" on one of the four fields might override the other three fields, subsequently setting them to "all" as well by way of a script...which is why I want to rely on a SQL list that I can control rather than just relying on the relationships. …

Hi All I have a database which tracks the Quality Control process at a camera rental facility. Currently I have four tables - Cameras, Filters, Lenses, & General Inventory - which I have called Modules, as each table has a separate layout which is only accessible by the respective staff that QC's those particular assets. The respective records are imported in to their tables from an exported Oracle Excel sheet (i.e. camera bodies are imported under the "Cameras" table, filters in to the "Filters" table, etc). The problem is that importing, filtering, and deleting records in to the appropriate tables is very frustrating and time consuming. I now want to have al…

Hi All, I have a layout where there is a "New" button to add a new record. When the new button is clicked, it performs various scripts (e.g. new record, set field, etc.) However, when the user uses the shortcut key CTRL+N to create a new record on this layout, it does not go through the script steps. Is there a way or is it even possible to tie the shortcut key to the "New" button? I have other layouts that has "New" button as well but performs a different "SetField" script, i would like CTRL+N to be dynamic depending on layout. Any help would be greatly appreciated! Thank you.

I know this is a terribly broad question, but I was wondering what types of factors slow portal filtering aside from sheer volume of records. Is it generally quicker to filter by relationships rather than the portal itself? Does filtering a relationship by way of a calc field slow the process? How about by way of using a SQL list? I have to punch in for work but if there was some best practices that were easy to fire off, I wanted to test the waters before I spend time going into the specific nature of my portal slow down. As always, thanks in advance.

Hi Everyone! I am fairly new to FM and so far I really enjoy working with it. But now I am stuck on something regarding relationsships. I would like to create a real estate database. I have set up three tables: - Customers - Buy requests - Sell request There is a primary key in the Customer table and there are foreign keys in the Buy, and Sell request databases. So the relationsship is One - to many from the Customer db to the Buy Request db and also from the Customer to Sell Request db. So I can create a portal on the Customer detail page that lists all the sell and buy requests that belong to that customer. My problem is, that I want to create a por…

I have a report that I need to create and I know that can script what I need. However there has to be a clever way via a self join or something to get what I need without some needless scripting. Here is a sample of the data and below how I need to report on it. Classes Table ClassID ClassName 7RJ Rejected OSU Signup Case Table CaseID Date Class 4000 2/5/2013 OSU 4001 2/5/2013 OSU 4002 2/5/2013 7RJ 4003 …

I am working on a scheduling module for our production shop. I want to display the Work Orders in a list and Schedule time to each of the Stations between the Work Order IssueDate and ShipDate. Secondly, I would like to report back the actual hours spent vs. the Scheduled time for progress reporting. Tables: Work Order (500/year) WorkOrder# Description IssueDate ShipDate Stations (7-10) StationID StationName Schedule (1 for each Work Order) WorkOrder# StationID TimeEstimated TimeTracking (1000s/year) EmployeeID EmployeeName WorkOrder# DateWorked TimeWorked Employees (20+) EmployeeID EmployeeName Thank you for yo…

I'm using FMP 11 Advanced: I have a a report using a list layout in which a sub-summary part displays donations for each individual. Donations fall into different giving categories (High, Medium, Low -- for easy discussion). If there are 50 overall gifts for this year, I'd like to tally how many "High," how many "Medium," and how many "Low," gifts there are. I am setting a High, Medium or Low flag for each gift. If ( GIFT = "1-99" ; 1; "0" ) NOTE: I can NOT store or index this field because it references a summary field / unstored / calc field… Now I want to tally up those Flags, but they unstirred, so I'm not sure how to do that in the report / "on the fly…

Hello everyone, it's been a while since I've been here and worked a solution in FM (last was FM7), so my limited skills are very rusty. Now that FM12 is here, I'm going to re-develop out entire system. So bear with me... I have an Orders table which looks up data from the Clients table based on client_ID. Each client has a different way of getting billed (some by rate, some by tariff, some by a per km charge, some by destination, etc.). These rates, charges, etc. also change very frequently, so I'd like to keep a record of those changes. What would be the most efficient way to a) determine the rates per customer (a bunch of fields or separate table in a portal?) a…

Good morning: I am trying to create a budget report: I have one table with year, category, subcategory and amount. I think I should be able to create a set of self join relationship structure to show this on one layout: 2010 2011 2012 2013 travel $10,000 (Sum of all travel in 2010) $6,000 (sum of all travel in 2011 Etc Etc Food $3,000 (sum of all food in 2010 $2534 (sum of all food in 2011)…

Attached is file I am having trouble with. Any help would be appreciated. The problem is with two tables: Projects and Samples in a database sitting on a remote filemaker server. I have a Layout "Projects" with records from table "Projects". Also that layout is a portal displaying related records from table Samples. People create related records in Samples by entering values in four fields displayed in the portal. They can press a button to go to a Layout "Samples" to show records from Table "Samples" and enter additional data in other fields. It's a routine setup. My problem? I have a related field (called ProjectName) from Projects displayed on Layout "Samples" that d…

I have a db with the following tables, with pertinent fields listed: Locations-pk_LocationID, Location, Bed, Service Observers- pk_ObserverID, ObserverName TimeClock- pk_ClockID, TimeIN, TimeOUT, Date Cases- pk_CaseID, Date, Start Time. Delays- pk_DelayID, DelayCode, Duration For a given record, a location can have one observer, each observer can have multiple cases, but each case has only one observer, and each case can have multiple delays (fixed list of delay codes). For the time clock, each observer can have multiple time clock entries, but each entry can have only one observer. Time clock entries do not need a relationship to/don't reference the oth…

Report relationship problem I have a difficulty in creating a relationship for a report and am thinking that a repeating field my be what I need. I've searched the forum but can't find a similar post but was wondering if a relationship can be based upon a repeating field? The problem I have is this We are trying to measure the value of sending out mailers to customer based upon them returning back to one of our stores. We have the three tables based on this relationship Mailings = group record that defines mailing activity i.e. letters posted in january Mailing records = a record for each individual that has been sent a mailing Invoices = invoices…

I'm working with a database that has a straightforward one to many relationship for customers, orders, line items and shipments. The structure in place is : Customers => Orders => Line item <= Shipments Shipments are created from the order records and tagged accordingly with the unique identifier for the order. Unshipped line items from that order are visible via portal on the shipment record. As they are added to that shipment they are in turn tagged with the unique identifier for the shipment. This works great and without issue. However, we have recently had several requests, from customers who have multiple open orders, to consolidate all the un…

I just started working on an FM database I'd like to use for managing the inventory of a restaurant. I want it to simultaneously keep track of inventory acquisitions and deductions (i.e. food orders) on an ongoing basis. Also it will track customer buying history, bids from distributors on various items, etc. Thus far I have these 12 tables and their associated TOs defined as follows: - Classes (StockClasses, MeasurementClasses) - Units (PurchaseUnits) - Inventory (InventoryItems, InventoryStock) - Recipes (Recipes) - Ingredients (Ingredients) - Orders|LineItems (Orders|LineItems) - Customers (Customers) - Distributors (Distributors) - Bids (Bids) -…

I have a script that checks for duplicates using a self-join method but appears not to compare the entire string. If I do a manual search for the offending items, they are not found. Sample: This is likely because the first line of each field is the same "http://chase.com.freedom.activate.jp.aid.a.cc.5pctcashback2013q1.int.cards.freedom" and is not comparing the full URL http://chase.com.freedom.activate.jp.aid.a.cc.5pctcashback2013q1.int.cards.freedom.5pctcashback2013q1.trip.a30356b.hp.trip.otherkin.com/f7af7e978f04ce6eb086f3dc046e9cc2/ http://chase.com.freedom.activate.jp.aid.a.cc.5pctcashback2013q1.int.cards.freedom.5pctcashback2013q1.trip.a30356b.hp.trip.ot…

Hi, I am new here and quit new in FM world but have a silly question regarding join tables for more than two tables. I have created three table join Table 1: id1 text Table 2: id2 text Table 3: id3 text Join table: id id1 id2 id2 I built a Layout for first table having two portals on it, showing records from table 2 and table 3. The relation is through a join table. Creating a first portal record in both portals creates only one record in join table that refers to both tables (2 and 3), a three way join. For all following portal records FM creates separate join records between table 1 and 2 as well as table 1 and 3. Does anyone know …

I have 3 tables with fields (amongst others) as follows - Jobs - PK_JobID Delivery Notes - PK_DelNoteID Delivery Note Line Items - PK_LineItemID, FK_JobID, FK_DelNoteID Delivery notes are compiled on a layout (L1) based on the Delivery Note table with a portal of related line items I also have a Delivery Note layout (L2) for printing based on the Line Items table. From L1 I can script GTRR and open L2 with the correct details. What I want to be able to do is open L2 from a layout based on the Jobs table. From a jobs table layout I can successfully script GTRR and open L1 with correct details - and can then GTRR and open L2. What would b…

I am using fM 12. 03 ON OSX  I have a global table that has no relationship to any other table.   95% of this table holds global fields.  (Lower left corner below)   I have a 'Setup' called LAYOUT that is based on Members.   (Basing it on other tables (including global) means that the portal values holding the global fields will not show anything.)   Below is the Setup Layout (based on Members)  THE PROBLEM: For some reason, if I am on the Members layout and I delete a Member record, it WIPES OUT THE GLOBAL TABLE! Huh? I can add member records without incident. But, I can not delete a member record withoout wiping out Global! …

I have been building/maintaining a stock control system for a number of years now. Part of the system allows hire stock to be booked to an order and calculating from inventory stock levels and other orders in the same time period, and reserve x amount of available items. I have a working version of this that I would like to improve with the forums help. The current version works as follows; Table "Orders" A table with orders in, and a daughter table of order items. Table "Order Items" Each order line has a qty field a start timestamp and an end timestamp Table "Dates" This table contains a record for every day in the last five years and next five y…

Hello.  I have a regular setup Items | Line Items | Invoices | Customers  Line Items and Invoices are related through an Invoice_ID field.  The database is a record of all school lessons for a particular teacher in any given month.  My Items table is actually a table of different Lesson Types. When I am entering lessons, each lesson has a start time and date.  I can see all lessons for any given teacher for the whole month on the invoice table in a portal [ portal is showing Line Items entries ].  The thing is that lessons are all in the portal [ as they should be, no issues ] one below the other, for example :  Lesson1 Date1 Lesson2 Date1 …

Hi All, I hope you all have a great year. I am trying to bring into a Job Card in a single record all the Service Parts that may be applied to a particular product and I don't have the faintest Idea how to do it. The tables and relationships are: Installation Items=>Job Card: Installation Number Job Card=>Service Items:Job Number and Product ID Service Items=> Service Parts: Svc ID Service Parts=> Product Parts:Svc ID Service Parts=>Products2;ID What is meant to happen is that when a job card is created via the Installation Items relationship, the job card is printed and passed to the service technician, the technician records on the job card a…

I am having trouble making two fields talk to one another. I am using the invoices sample form. When I press on the Item list, a drop down appears of all my products. When one is selected the Part Number field populates according to the product selected. My issue when I select the Part Number field and try to enter entry here it doesn't correlate to item field.

I need to summarize records, in both a report and browse view, that are of the form below. I need to basically do an Excel like Pivot Table of the data. Record Form: Article No Product Code Style Size UnpostedSales Yds Cost DM13012 DM13012-XS T Shirt XS 3 15 25 The Table view or List view would summarize the data and show the sizes as columns. The number beneath the XS column is the total number of DM13012-XS's in the data set, 23. The Article No consists of a family of Product Codes. Article No. DM13012, has the following family of Product Codes. DM13012-XS DM13012-S DM13012-M DM13012-L …

I think that what I am trying to do should be really easy, and I am missing something obvious. Any help or input is appreciated. I have a table of names and addresses, with an additional field for a code. The code is a always a single letter. I am trying to identify duplicate addresses, and all of their associated codes. The records may have from zero to 9 duplicates. So, if "John Doe 123 Main St" is in the file 4 times with codes A,F,J, and I. I want to set a field in each of those records with "AFJI" either through a calc field or a script. I created a concatenation of name and address to use as a key, then created a new TO and a self join based on that key.…

Hi, We are a non profit organisation and may accept money gifts. The main data that have to be collected: * information about he person who makes the gift(s): Name, first name, address * information about the gifts: date, amount, occasion (birth, mariage, death, undetermined,...), date of acknowledgment letter I also have to keep the sum of the gifts of former years. I have already a database, but it is not very sophisticated and requires regular interventions in some fields in the structure. The main problem is that I didn't find a good solution for the case that a person makes more than one gift during a year; for that purpose I have created 12 individual …

I have a record set like this Contacts - Join - group where contacts joint and group are tables with join related to the other via contact ID + group ID users currently login and I would like to add notes and information about specific groups in relation to the contact ID. I guess ideally this information would go in the JOin record ? or maybe the Contacts record... however for each bit iof data put in... ie a note regarding a contact in realtion to a campaign I want the user who entered that data to be identifiable… how can we do this ?

Hey, cant a relationship be built on the list function? if the value in the list function is exactly the same as the field its related to it should work right? I cant get it to work. Any suggestions?.. appreciate answers:)

I have 5 databases set up on my Filemaker program. I see how to connect tables and files but is there a way to connect the databases themselves? Each of my clients has a unique number assigned and I'd like to figure out how to have certain info auto populate between the databases. I'm currently running FM 9 and am about to upgrade to 12. Maybe this will be more apparent with the new software.... any pointers are appreciated !

Greetings to all  Reports of subcontractors ?  How to report the relationship Subcontractor

Ok, before I keep chasing my tail and without signing up for 4 years of academics on db's...here's what I want to achieve:  Food Ordering System ==================  1) Cashier/Order screen/layout  2) Food Prep screen/layout  3) Delivery screen/layout  Which would work like this: Order is taken, THEN said order is displayed/formatted for Prep, THEN/IF an Order is a Delivery it is displayed/formatted for that screen. (BTW, these 3 stations will be iPad-driven with, I believe at this point, an mac mini for the 'base')  So what's my question...?  I'd like seasoned views on how to breakdown the data into tables/relationships that 'make sense'.  I…

I have a database that is working. I type a course number in a field and the credit and class description pops in the correct related fields. I am not finished with the database, BUT when I try to add more classes to the COURSES table it tells me the TABLE MISSING. I have a lot to related data fields and everything is working, If the table is missing, where is it getting the data to fill the fields in the other tables? 10Grade, 11th and 12th grades. I have attached the file. User name: ld134545 PW:ld134545 Any help is appreciated. Thanks Using FMP 11 Advanced

I had assumed this would be easy, so I must be missing something. This is for a "birding" database (I'm a birder...make fun of me as you will). I have an existing one-to-many with a parent table ("dates") and a related table ("observations"). Each observation record tracks a sighting of a unique species at a specific date and location. So, for example, I'll have the related portal showing 63 species sighted at a single date/location. However, I'd like to also be able to show all the species seen at all the combined locations during a single day. I've created a simple relationship that will show me all the DUPLICATE species records for that date, but can…

Hello, I'm still a novice, but I am beginning to understand the merits and drawbacks of the Anchor-Buoy method. I've read Ray Cologon's "Approaches to Graph Modeling", and I've developed a modest database with the Anchor-Buoy method, but I don't think I have grasped yet the other methods in this paper to try them. I'm starting a second, more ambituous, database, and I am going to begin using the Anchor-Buoy method. I was wondering if there is any danger in using this method in terms of stability? Does the abundance of TOs stress the operation of the database? I know my fear might sound sophmoric... Once I get more familiar with the nuances of FM develop…

A typical set of invoice lines looks like this. Article No Product Code Style Size Qty Price Tot Price DM13031 DM13031-XS Hoodie XS 0 15 0 DM13031 DM13031-S Hoodie S 2 15 30 DM13031 DM13031-M Hoodie M 1 15 15 DM13031 DM13031-L Hoodie L 2 15 30 DM13031 DM13031-XL Hoodie XL 3 15 45 I would like to enter the ordering information in this format, there of course could be a second line in the same format with a different Article No, DM13172 etc. Pricing would be always be th…

Hello. It has been a while since I was here - good to be back. I have a following question about an invoice solution. Background: Creating an invoicing solution for myself to keep track and run statistics for English lessons, students and teachers. Before I start the following is not clear to me, I have the following items: Lessons | Students | Teachers | Schools The invoice will be monthly generated, what I am having trouble deciding on is how to relate the above... The Teacher teaches any number of lessons in a month. Each lesson is set in duration and cost so I am thinking that Lessons should be an Item table and the Teacher should be a Customer…

Hello Fm experts, I've been trying to import data from an excel file to a related table on my data base for days now. All of the imported data seems to work ok but I cant populate my foreign key field to match the related field on another table. I have a parent table where I keep Horse's general info like age, gender, color etc... The child table keeps reproduccion info for each horse. Every horse has a daily update of the reproductive status where I have info on ovaries, uterus etc. I'm trying to import 6,800 records where I have the daily info on each horse. I have related the Horse ID with a primary key to a Horse ID foreign key, whith a dropdown list that show…

I'm embarrassed to even ask this question but here goes. I'm sure I'm doing this the hard way. Simply put we have a parent table and a student table. The primary key (parent id) points to the foreign key (parent id) in the student record. I can now have a list by parent with all their children. Fine. Now I want a list of parents with any children they may have in 7th and 8th grade. What I did (please don't laugh) is create a primary key in parents with the parent id concatenated with a "Y". In the student record, if the student is in the 7th or 8th grade I create a foreign key with parent id concatenated with a "Y". I create an occurence in my relationships that have the …

I have two tables, a Products table and an Inventory table. I want to copy over the some of the records in the products table to the inventory table. There are product records that are already in the inventory table that I don't want to duplicate and there product records that are not ready to go to inventory. I was going to mark the products that were ready to go inventory with a checkbox or fill a date field. What would be some suggestions on a good way to go about the copy process? Still getting my arms around FM, it seems like it should be a fairly straight forward process. Thanks

Hi ; Im making accounting template for my company. We tracking all products on product number.  I create Customer, Order, Shipment, Orderdetail, Shipmentdetail and product table.  But I cant relation between product - orderdetail , product - shipment.  Because my customer table relation order and shipment table.  order relate to orderdetails shipment relate to shipmentdetails  How to do this relations ?  Please help me

I want to do a Delete All Selected records script by ssetting a Select checkbo, which I think should be easy enough, but before I do the Find and Delete I want to be sure there is a Select field selected in at least one record of the displayed view, if not I want to display a message "Please select records to delete", Want would be the logic to go through the displayed list to determine that at least one record is selected? Thanks

I built a summary report using the layout report wizard. I wanted to filter out records that were empty in a field called quantity. I did a simple Find script using "*" in the quantity field. When I ran the script it eliminated all of the records with empty quantity fields, but it also removed all of the sub parts from the report, so I just had a list of records without any of the banded categories I had set up. The Show All Records did not return the original format. I had to recreate the report. Any suggestions?

Is it possible in FM 12, to have a dropdown value list that I can add entries to on the fly?

How would I conditionally disable data entry into a cell base on the data in another cell? Initially it would be just a checkbox, would it be different if it were conditional on cell contents?

I'd be very grateful if someone could give me some advice on how to retrieve values from a related table where there is a match in the middle join table. I have 3 tables: Projects ContactLink Contacts Projects and Contacts are joined to each other thru the ContactLink table. In the Projects table I have a few fields for various contacts: Architect Contractor Project Manager I would like these fields to be populated with the values from the Contacts table where the Contact Role matches the field in the Projects table. So if I define 'Contact X' as an 'Architect' I want the Projects::Architect field to be populated with 'Contact X'…

I am trying to build a menu management system so i have created different tables : INGREDIENTS RECIPES MENU Th idee is that the person composes his weekly menu selecting dishes out of the recipe table The person defines his offer (category) which is the same every week. it is possible that an offer is present twice... for exemple 1. SOUP 2. SOUP 3. SANDWICHES 4. COLD DISHES 5. HOT DISHES 6. DESSERT Every outlet can have a different offer but once it is established it doesn't move anymore ... i have therefore programmed the categories in a global so that every time you create a new menu the selections are already present. When the person creates …

Hi all, I am trying to make a layout in my database that will help in preparing estimates. I want filemaker to return a price when I set two values. In order to do this I made a table (Price Schedule) with fields for Vendor, Item and Price. Then I created a self join relationship with this table using Vendor as the match field. In the layout there are two drop down menus, one for Vendor and one for Item. Now when I select a Vendor, the Item menu (correctly) displays only the Items that that Vendor offers. Since this worked I made a second self-join this time using Vendor and Item as match fields. This does not work, setting the Item menu has no effect at all. After t…

I am using FM 12 03 advanced.  Here is the TOC graph:  The relationships are pretty simple. There is a main Patient table that holds a tab structure. Each tab holds a portal that is the 'many' side of the relationship. On most portals I need to know the doctor who issued a prescription, performed a procedure etc... Hence, the duplication of the Doctor table (in red). This works.  On the RX input portal I have this:  I produce a report based on the RX table that shows the active prescriptions for a selected member.  This works.  The problem: (It seems like this should be a no brainer but I just can't 'get it' grrrr). I want to design…

Hi, I have invoice table with line items (related table). When I change quantity or price in line items the summary field changes on invoice table but I can not track this change on invoice table (modification timestamp does not change). Do you have any idea how can I solve this? This issue is also present in FileMaker starter solution Invoices.fmp12. Regards

Hello all. Working on my first serious FM project for my current company. I've been looking around these forums and others to get some ideas. One thing I've noted is that most people prefer creating related records in another table through a script. Allowing them to be created through a portal row seems to be frowned upon as not a good practice. So, is that my impression correct that this is generally not considered good practice? Why not? Appreciate any responses!

I have two tables a Note Header table and related Note Lines table. I would like to see all of the Note Header records in a portal (or List or Table view). And on the same layout, see all of the related Note Lines in a second portal below the Note Header portal. I would also want to be able to create records in both portals.

I am trying to set up a time book database in FMP. It will replace the manual time book entries for payroll purposes. It will be fairly basic - ie start time, finish time, time for lunch. But I want to also group and be able to view records according to their Week Number (WeekOfYear). So I created three tables: Employees, TimeCardWeek and TimeCardDays I created a unique ID for the TimeCardWeek Table called __pkEmpIDYearWeek (which pulls the employee ID, the Year and Week Number: eg EmployeeID_2012_46). I am having trouble getting my head around the relationship set up because I only want records from TimeCardDays to be related to the appropriate TimeCardWee…

I've got this database where I have a relationship between an Employee, a Project and Tasks. With the Employee table I've created two different association. An Employee as a Chief and an Employee as a Unit Head An employee can have one and only one chief An employee can have one and only one unit head A chief can have more than one employee A unit head can have more than one employee The problem that have at the moment is that the relationship between the employee with the unit head and the relationship between the employee and the chief is in the wrong order. What is the easiest way to change the order of the relationship? Thanks! Company B.pd…

Hi all I have an Orders table. This is the first thing that is populated in a typical 'session'. Orders can be, in any combination or quantity, assigned to Acknowledgements, Invoices or Delivery Notes (all separate tables). Therefore each Order record includes the fields: Acknowledgement_IDF, Invoice_IDF and Delivery_IDF. Now I also have a Customers table, my question is, would it be OK to just have the Customer_IDF field on the Orders table, or should I have that field on all the other tables as well (Acknowledgements, Invoices and Delivery Notes), making each Customer directly associated with all 4 tables, instead of just 1. Any advice appreciated. Th…

Surprise, surprise. I am also a newbie but hopefully I have a softball question for someone just lurking around. Okay, so I originally built a solution for our church directory with the following schema: FAMILY _kp_family_id _kf_member_id last name MEMBER _kp_member_id _kf_family_id first name It was structured like this so that we could do a printed directory page and make use of the portal to just list the first names and their personal cells, email, etc. Now, I'd like to take that solution and also integrate an Attendance chart so that we can take attendance using FileMaker Go and an iPad. This is where the extent of my knowledge begi…

Hello everybody, I am very new to FM and am exercising myself with the Estimates startup solution included in FM Pro 12. So far, so good....except that I really wonder how I can insert a new row (not at the end, as default) but between existing rows of a portal. Is there be a "simple" way of doing that in FM Pro 12 ? If not, must I dive into coding by adding a Relative Record Number to each record, leaving "holes" between the RRN's and by coding a button "insert below" that would then find the RRN of the record I am positioned on and add 1 to the RRN to create the new record and insert it ? If I need to code, could someone help with a practical example or ex…

I am using FM 12 Adv v 3   There are 2 tables:   Members Form Ltr  and  Committees FormLtr They are related Members Form Ltr::KP_MemberID to Committees FormLtr::MemberID.fk  I produce a report based on Committees FormLtr and it works. On that report I have a field called Committees FormLtr::LtrSelected. It is a checkbox and when clicked gets the value 'Yes'.  I want to 'narrow down the list of members' to only those who have checked 'Yes'. So, I open a window and put in a layout based on Committees FormLtr and when I do a 'find' I get this:     huh? I checked the relationship. Isn't it ok? Here is another weirdness,…

Tables are as follows: Owners Horse Details Visit Notes Billing (date, item, fee, tax, total, paid, balance) I'm trying to get my head around how to set up the 'Billing' table (if that's in fact the right way to begin!). Basically I will generate an invoice at the end of a visit. There's a small number of items that could be charged for, in two groups (Services, Products). The concept I am having trouble with is the idea that an invoice can have multiple line items, however, I don't see how I can simply account for that in a table's design. There must be a trick with portals or something that I am not clued in on yet.

Hi,  I have to look at my Invoice Table, from my Project Table.  How can I write a calculation in my Invoice Table that will only calculate my Project Sequence once, see pic.  I have a relationship on my Invoice Table back to my Project Table to show the Project Sequence by Invoice ID to Project Invoice ID.  I need this, each invoice that is assigned to a project I can see the Project Sequence along with the Invoice ID see first .pic. ( though I don't look at this relationship this way )  Now, I need another calculation on my Invoice Table see second .pic that will only enter the Project Sequence ONCE no matter how many invoice are assigned to that parti…

I am trying to come up with a set of calculations to give me the inventory value of our raw materials, based on the most recent purchases. I have 2 tables here, Purchases Line Items and Supply Inventory. Supply Inventory contains the fields Item_Name, Quantity_Remaining, and what will hopefully be a calculation for value of our remaining supplies. Purchases Line Items contains the fields Item_Name, Quantity_Purchased, Purchase_Date, Item_Price. The tables are related by Item_Name, sorted by Purchase_Date, because I want my inventory value calculation to assume that our remaining inventory items are the ones we purchased most recently (i.e. FIFO). So for example,…

I have, Table A: __pk_TableAID cToday Calculation (Date) Unsorted, from TableA, = Get ( CurrentDate ) Table B: _fk_TableAID StartDate Date Indexed EndDate Date Indexed TO Relationship: __pk_TableAID = _fk_TableAID cToday ≤ EndDate cToday ≥ StartDate What I try to accomplish is to see in Table A, through a portal of Table B, the actual active records of Table B. Let’s say customers' contracts. An active contract has a start date but not an end date. This relationship only works if EndDate has a date into it, but I need to leave it empty until I have an ending date. Any advise?

Hi all, Thank you in advance for any help. I'm creating a database for my school's business department. I have a many to many relationship set up like this: Students - (Internship) - Internship Sites Student can have multiple internships, and internship sites can have multiple students. When a current student starts a new internship it might be with a company not listed in the "Internship Sites" table. Is it possible to make it so data entry in the "nternship" jointable subsequently creates a record in the "Internship Site" table ? IE. John Doe has an internship with TARGET. I go to John Doe's record in the "Students" table layout, enter …

Hi, .:Should we have separated tables to control account receivables and payables? If so, how can we use the information from this tables to create a "cashflow" report? What is the ERD that does this? Why? Well, my wife and I own a beauty salon (small business) and we had been using excel to calculate the commissions, to keep track of payments made to the employes (other than commissions), to calculate the gross profit and also to manage the cashflow (present and future payables/receivables). In order to be able to generate reports (eg. the clients that come more often and the ones that haven`t come to the salon recently) we decided to switch to a database.…

Not sure if I should make another topic related to another post I recently made? but it is kind of a different topic... related to. This is related to Filemaker 11. I have a VPN (virtual private network) which connects me to an FMserver which is located on a virtual server in a different location. I have a database on the fmserver which is the master database. I have a database on my PC that has a table (design) that connects to a sister table (design) on the fmserver. I have an identical table/fields/layout in both databases..... I find it absolutely amazing/astonishing that with the database on my PC it will show me all the records from the file …

Here is hoping that the forum has fixed their attachment problem:   In the Members Layout I have a field called (field--- yes, I know but I can't change it now. It will screw up my imports). Members::field collects information from a popup. It works and the Members::field information shows on the Members layout. ------------------------------------ On a second layout, based on Meetings I have 2 portals:    Portal 1 l is based on VisitorMeetingJoin. It works. I can pull information from Visitors as expected.    Portal 2 is l based on MemberMeetingJoin. It works EXCEPT that I can not get the MembersTOC::Field to show data???  Why can'…

I have 2 databases... one looks at the other through various relationships... I can't seem to goto related record if its in another database... the function does'nt seem to work... what can I do about this? or, really... how can I go to a related record in a "different" database? tia

Hi there... I wonder if some kind soul could have a look at the attached file.... http://www.sendspace.com/file/7o6ed8 You will see some records representing Suppliers. There is a yes/no to denote if that Suppier is an alternate supplier for the goods. I am trying to make a relationship that will let me say for example that The goods from Supplier Franke are also available from supplier waterline So I have a portal that shows a list..for each supplier, of all the possible alternate suppliers and I want to have a tick box that will let me say YES this alternate supplier can also supply the goods. But at the moment the tick box is not working as intended because whe…

Hi! I'd like to know if there is a fast and simple solution for refactoring Layout/Table link. I recently started to separated my data to the interface, so in that migration I have to refactor all those fields in layouts (changing the data base - as it is now in another file/relation : got those <unlinked table>) If you got clues go ahead I'm learning Use mac and fmpro 11 tx

Well I am as entry level as you can get using Filemaker, I have done some online training for the program and it has been helpful, but I am stuck. I think I understand the parent to children relationship, and its importance but I have run into a bind. The database I am trying to put together is for Fantasy Basketball. My goal is to import the available players for the day, get their stats, and provide a report on the available players for the day. I have one table of all the active NBA players. The field are: Player Names, Team, Web address to their Stat Page. The second table is a list of all players that are playing today, with the fields: Player Name,…

I am using FM 12 03 Adv in OSX and WIndows I have this setup: Members TOC:- MemberMeetingJoin Meetings VisitorMeetingJoin Visitors KP_MemberID -----[ KF_Member KF_VisitorID ----------[PK_VisitorID KF_Meeting--------------[KP_MeetingID----[KF_MeetingID Sorry I can't upload a capture of the TO's but the forum is generating this message when I try "No file selected for upload"??? I have a layout based on MEETINGS I have 2 portals on the meetings layout: #1 ) the left portal is based on MemberMeetingJoin ... it works great. #2) the right …

Hi I've a table with a date field and records 1/11/12 1/11/12 2/11/12 7/11/12 7/11/12 7/11/12 7/11/12 12/11/12 .... and have been trying to work out a self join relationship where the record set would be 1/11/12 2/11/12 7/11/12 12/11/12 .... anyone help ? thanks Cate

How does one write a SELECT statement to find records with id that match any one id listed in a multi-key field? The following does not work because the contents of the multikeyfield will not entirely equal any individual record id. ExecuteSQL ( "SELECT field FROM table WHERE id=?" ; "" ; "¶" ; multikeyfield )

Hello all, I have a bit of a silly question... I was pondering the name of the table that hold all the addresses, phone numbers, emails, urls, etc... And realized I could not think of a perfect name, names used in the past have been: - book (a little too general) - data (too general) - line (way too general) - address book (more then just address) - phone book (more then just phone numbers) So like like the word "Rolodex", it is perfect! Except that it is a BRAND! But none the less, it works perfect implies the collection of all info such as phone, address, emaill, etc and is a small, single word that would neatly fit in the naming of databas…

I'm just learning FM. I'm very confuse in last 4 days, i have no solutions to my problem. I have a database (i attach it) I want to add my record to my table (Order_products), it worked - it stored records to my Order_products table) but i don't know how to show it on my portal (Invoices copy layout) and add new row to add another selected items in my invoices copy's layout. 1. Is there any wrong with my relation's table? because when i add product's record 11, it wont add to Order_products table. 2. How to put record data on table to portal? Need suggestions please.. regards, Eik Z Invoices_order_modify.zip

Hi, Can someone help me out if they know a way around creating a relationship on an unstored field, problem is i am scanning barcodes onto a table i have 2 types for barcode GS1 and HBIC, gs1 is cross referenced against another table which matched the number scanned and returns the product code, the hbic is extracted by a calculation i have a filed with an if statement which joins both the gs1 and hbic as i need a one field which contains the product code so i can create a relationship but its unstored and i cannot click on storage because it references a related field? (thats the error i get) anyone have any advise?, many thanks

hey guys, im pretty new to FM and i've been working with it tirelessly for the past month learning to use it. i love this program but there were some things that i cant seem to get done, i was hoping someone could help me out or at least point me in the right direction. my company hosts a fair every year, and we rent out booth spots for vendors to come and sell their products and advertise and such. we started out with MS access early on but for the past few years our secretary and treasurers have been tracking our vendors and booths by hand with MS excel. its extremely error-prone and time consuming. im the only "general" IT guy here so they've tasked me with creati…

I'm very new to FileMaker, but I've been making my way through the Lynda tutorials and I'm completely stuck at trying to resolve a few many-to-many relationships. The tutorial uses the example of Actors and Movies to explain the join table (with the join table in that example being Roles). I think I understand the concept of the join table, but I cannot figure out what data to build my join tables around. I am an actor and am trying to build a database that will organize information about auditions, workshops, industry contacts, productions, and gigs. At this point, I imagine 5 tables: Workshops Auditions Gigs People Productions The many-to-many rel…

OK, I'm working on my food pantry project and I have a general question about FM 11 Pro Adv and the relationship diagram. I've got two tables called Members and Households. Table-Members holds the names, and DOBs of individuals. It also has a primary key, autoentered serial for each person. In table-Members I've also established a Household_id field which is not a key but a numeric text field denoting what Household the individual belongs in. I do it this way because sometimes people (Members) move in and out of households in our "business". In table-Household I have the household's address fields and other fields I need as attributes for households (ex: Does this h…

The other day I turned on the "delete related records in this table when record is deleted in other table" setting on a relationship on the graph. Very rarely do I use this feature but it would prove to come in useful for a particular script. So I turned the option on a single relationship on the graph, expecting that when a record is deleted from the context of the parent, that the child records are deleted, and it worked fine. Now today I put a portal on a layout based on an entirely different table occurrence of the parent, and run a script in that portal that does a "delete portal row" - however this is still deleting the child record! In other words …

Thanks in advance for taking my question. I hope I'm not being too novice about this. I also hope I can explain my problem clearly. So I have two tables whose records want to relate to each other. Specifically, any number of invoices can relate to any number of shipment invoices and vice versa. Having this work in both ways took a repeating field and some scripting. This is not my problem. My dilema is then creating unique information for that relationship. I can't simply reference information in a record in Table A from record in Table B because that record in Table A might be related to multiple records in from Table B, all which require a unique value. …

Boy, I'm making a lot of rookie mistakes with this project... I have several tables - joined with relationships - mostly one-to-many. Somehow I am unable to "break" any of my relationships! On the rel diag, I double click on the "=" sign that dwells on the line between the related fields. The Edit Relationship dialog box appears, shows the two tables and the associated fields left and right in each, I hit "Delete", the line showing the two fields erases but the box stays up. "OK" never lights up and the only way out is "Cancel". Double-click again and the relationship that disappeared has reappeared. I brought the Edit panel up again and unticked "Create r…

I am very new to databases and am trying to develop a system for a company we are launching in Brazil. I am having trouble with understanding how to use a jointable in filepro 12. The main gist of the the database is to do the following. We are a 3rd party rep to sell services for a partner and we receive commissions on those sales. We have a sales team making sales and the partner company pays our company for each sale and then we pay our reps a portion of that commission. The clients sign a contract and receive equipment that has a monthly fee along with services that also have a monthly fee. The database is to store pdf copies of all the contracts, track sales f…

I have a bit of a strange case here. I have a basic portal that displays what jobs a candidate has applied to. This connects the candidate table and jobs table via a join table that represents each time applied. The portal is for the join table. On the portal I have a button that uses the Go To Related Record command to jump to the related job. This has always worked normally until I converted to FM 12. Now it doesn't take you to the correct job. It usually takes you to one of the jobs in the portal, and that is typically the oldest of the related records. There are no sorting options selected on the relationships. The portal itself is sorted reverse chronolocigally. I tr…

hello everyone. I am a newbie and somehow managed to make a simple database for a clinic but I don't know how to create a new file for each patient visit. What I did was to create numbered tabs (20 tabs) representing the number of visits of the patient with date. You can just imagine the numbered tabs would run the entire length of my monitor if I make 50 tabs (please see attached file). My problem is that if a patient visits the clinic twice a year, the records will only be good for 25 years. I tried to search for an answer online but can't figure this out. I tried to check the table in the layout mode and I found out that I only have 1 table with over 2,000 fields.…

For the last three days I have been trying to understand the Contact -> Invoice starter solution, I have been viewing videos on creating relationships and tables, I have been looking at alternatives such as set field, or lookups... and I have become a bit overwhelmed and confused. I basically want to create a Contact Layout Push a button that says create Invoice and a new record is automatically created in the INvoice Layout with all the choosen Name, address, phone fields etc. The starter Invoice solution in Filemaker 11 has a drop down menu in invoices that has you pick the contacts name.... This I would prefer not to have. If anyone can even po…

I have 2 tables, 1 that stores reports and another that lists shows to relate to that report. I have a drop down list in the reports layout that is populated from a value list created from the "showid" in the shows table. After selecting a show from the drop down list, the text in the area of the drop-down is populated by only the showID (I must have both, as there may be more than one show with the same name. I have a field right next to the drop down that should lookup the show name associated with the id # chosen from the drop down. It is not being populated by anything for some reason. I have attached 2 screenshots that can hopefully help. Thanks!

I have been reading so much and not sure where I read this but it has to do with SQL I think, probably the Execute SQL function. Anyway now in version 12, I read that underscore should not be used as first character in field names. But how is everyone making their keys pop to the top of the list if not underscore? I tried space but it would not take it, nor dash. Period won't work as first either. It would help because FM always resorts the relationship into alpha when pulling up imports. And in general it is easier if I can sort by name and have the keys at the top. What is everyone using now please?

I have a repeating field in Table A, which is also the matching field for a related field in Table B. Both fields need to be displayed in one Table A layout in columns next to each other, so that any matches show up next to the appropriate repetition. In many cases, not all of the repetitions for a given record have a match. It is vital that when there is no match for a given repetition, the related field must show either a blank or "no match" in the same row. Also the matches must show up in the correct order in the column. The only way I've been able to pull this off so far is to use a lookup in a repeating field, with a "if no exact match" value specif…

I am using FM 12 Adv on OSX and Windows First, I KNOW there is something I don't get about TOC. Perhaps you can elighten me? Below is my project. It is a medical records database with these tables: Patients (PKPatientID, LastName, FirstName etc... Doctors, (PKDoctorID, LastName, FirstName etc... Procedures (FKPatieID, FKDoctorID, Name, etc.... RX (FKPatientID, FKDoctorID, Name, Date etc... Lab Tests (FKDoctor ID, FKPatientID, Test Name ... Vital Statistics (FKPatientID, Patient Systalic , Diastalic, Pulse etc by date) On the PATIENT Layout, (see below) I have a tab object. Each tab has a portal that holds Procedures, RX, Lab Tests, …

My main table is a read-only external data source so I don't think I should be adding fields to that but I want to generate a report with equipment in three groups based on entry date. So my new related table only uses the date field "entered" from the main table and generates a string field "Period" as follows with "Entered" Case ( Entered < Date ( 12 ; 31 ; 2009 ); "Before" ; Entered > Date ( 6 ; 30 ; 2011 ) ; "Post" ; "During") So the new related table should end up with every entry date from the external data source and one of the three strings but I end up with zero records. Dani Roloson University of Waterloo

Newbie here, I'll try and explain my problem best I can. I have a table with my products: Product - _kp_product_id - style - color - size - price - sale_date I want to genereate a report that shows sales with a row for each distinct product style, color, and size with a qty rather than having a row for each sale. I have been trying a self-join relationship but am still unable to "group" the rows properly. Is there a way to generate this type of table relationship?

Hello, Is there a way to have my Inventory stock keep track of orders and purchase orders for up to 1 year before not being in the on Purchase Order and Qty Sold displayed quantity. Not sure how others deal with having to large of a quantity displayed after a year worth of orders and purchase orders reflecting the Qty on Hand on the Inventory module but I would like to have the transactions dissapear from the Inventory Info after a year worth of Orders and Purchase Orders that reflect the Qty on Hand. Please let me know what is the best way to deal with keeping track of inventory for sold orders and purchase orders after a long period of time? Thanks.

Hello FM pro users, I"m using FM 12 pro advanced and I really need to find out how to display all the child products and child products of child products in BOM module. https://www.dropbox.com/s/7vrk1ik3e276mkx/Inventory%20-Group%20Test2.fmp12 Right now in the database, the particular item can be displayed by parent products and chiild products. but it can't be seen all the child products of child products. I'm not sure how it gonna work. I believe the new relationship btw products table and assembles are needed here. it will be great help for me if anybody have been through that portal tool where you need to display how to pull out all the child products on spe…

A bit over a year ago I had a piece of programming done for a solution of mine. The objective was to have conditional value category lists for each of 8 tables where I could input all values without leaving the table's layout. I was told by the programmer he provided the only option available - I'm looking to find out if there's a simpler alternative more suited to my skill level. These are the tables & fields that were created: These are the relationships created: Here's the first script: Here's a script (I assume was to be deleted as it's missing table & field info?) Here's the layout created - one for all 8 tables Here's what it looks like i…

I'm sorry for my English. Thanks to google I am planning a database. scenario When the tour company I'm working. 1. My agreed the company s staff working in factories or businesses URLs, or midibuses bus transport business. 2. Means the company can carry in your own property, can be tools suppliers. 3. In addition, the accounting of suppliers keep URLs. 4. I'm charging on the number of time. (Morning, afternoon, evening) 5.Ödemeleri would be charged on a monthly basis by check or in cash. Each piece is not only payment per invoice, may be parts. 6. Suppliers also have to pay monthly. The related assets as follows: …