Relational Database Theory

Hello, The Problem: My team has a large spreadsheet used track the disposition of property assets, and has grown too large to use and maintain. A little background, when an asset is unusable, expired, lost, etc; it will get disposed via a form called Disposition Form (DF). That form might have more than one asset on it (DF Line Item). If the asset was purchased against a particular contract, the Disposition Form will be assigned a "Case" number and will go to a Contracting Officer for instructions on what to do with that asset. Additional data like Status, or Open/Close Dates are in the tracker, but for this illustration in not important. Assuming the plan is bui…

Hi I have 4 tables, 1 WorkOrder, 2 Shipper 3. receiver and 4 dispatch. "workorder" is related with "shipper" and "receiver" both as "one to many " relationship and both have field "Name" which is text field how when I commit record in shipper or receiver in "name" field , same record commit in "dispatch" table in "name" field.

I have my one layout invoices , 2nd is customers 1 portal I want the i make a portal on invoice where i have data of my customers . if already have customer it populate automatically on invoice if not a new window opens and i add new customer and goes back to same invoice 2nd portal on same lay out make a side panel and put customer portal in list view where my all customers shows and when on my 1st portal i write customer name it sort out on 2nd portal and it is there it i click and in populate in my 1 portal ( 2nd portal it is name only and 1st portal it is all details. test file.fmp12

I am a novice. I have a File Maker project I want to develop, but am struggling with the initial schema design/setup. I have weekly cost reporting data in excel format. The weekly excel file is made up of 11 columns and between 500 to 3000 lines of data, the first two columns are 1) unique account code and 2) descriptive name. Four of the columns contain updated key numerical data each week, the other 5 columns can be derived from the data in the 4 key columns. I want to do various row calculations based on the average of the specific key column. I want to compare the average of the specific column/row (updated each week) to a matching columns/row budget num…

I have a (Main Database) that that uses relationships to fill in a Portal that is embedded in the Main Database The portal (Dance Products) contains the products in the order. The relationship link between the two databases is the Form ID Number. The Form ID Number is a number generated by our online ordering system. (In my screen shots the form ID is 557) The portal idea work well with one exception. When there are multiple products entered that have the same Line Number the Portal only displays the first product ordered and does not display the rest of the products ordered Question: How do you get the portal to display multiple products that ha…

Hello, I am building a small app my wife can utilize to manage her hair and nails business. She is self employed and she has a lot of clients, some are regular, some are infrequent, and others are new walk-ins. She wants to have a way to see/schedule appointments in a calendar view, but I am a little unsure how to design a calendar that would work with a simple contacts and appointments tables. Can anyone point me in the right direction? Please and thank you

Apologies for this very simple question but I cannot find any explanation of this concept. In a situation where there are 2 related tables, am I correct in assuming that if a new record is created in the parent table then a record is automatically created in the related child table? I'm trying to understand how creating relationships between tables actually works when records are created. Apologies once again for my ignorance.

Hello, I need help please as I am new to the Anchor-Buoy using the following concept: I have a base table [PATIENTS] and would like to connect it to [STATUS], [DOCTORS], [TESTS], [TREATMENTS]...etc I looking to track the BOUYS Tables changes with time e.g. (A) Track the start/end dates/times of any [STATUS] changes of the patient --> becoming INPATIENT or OUTPATIENT or DISCHARGED (B) Track the start/end dates/times of [DOCTORS] interactions with [PATIENTS] ...etc 1) I figured a join-table is the approach to capture this data, is this correct? or is there a better way? 2) Using the anchor-Buoy method, how do I use a join table? …

Hello, I need help please with the best relationship design. I'm designing a database for patients and their encounters/visits, and I would like to like to view their information related to each visit (Current and old). I have the following tables: tbl_Patient ---> tbl_Encounter (inc date/time) ---> many tables capturing patient data What is the best way to view the old patient data based on the past date and time? Thank you

I need to copy a table from one file to another. I inadvertently deleted the table from my working file, but it still exists in a back up that I have. Is it possible to copy/export from the old file and insert/import into the working file? I've looked everywhere I can think of. Please help! I don't want to have reconstruct this table. Thank you in advance for your assistance!

Hello! I apologize in advance for having this nonsense written. I am just lost and need help! I am trying to develop a 'small software' that can handle my lighting design information. I am trying to get relationships between multiple layers to correspond to each other. For example. I want to label Scene Titles in proper places for my Spot and Lighting Cues. I have a scene management layout where I can designate where these scenes go however! I don't understand how to link it. Example. Scene 1 is a Pre-Show Label on Light Cue 5 and Spot Cue 1. (Example Record for Cue is Record7 LIGHT CUE 5, and Spot Cue 1Record2 SPOT CUE 1 So I tell that rec…

Hi guys! would anyone please give me some tips one how to create a solution that export all tables in one XML file that can be properly ready by power BI. .. please help

I find myself writing more and more code to interface with 3rd party websites (databases) via RESTful type APIs and importing and exporting data using XML and JSON. It is a Filemaker application written for the health industry and contains patient information, medical records, insurance records and billing and accounting data. Before I continue further down this road I am wondering if anyone has any ideas regarding whether I should create a single table with one record for each 3rd party application or a single table for each 3rd party application. Or does it matter in the long run? I can see that there are pros and cons to each. The single table would require fi…

Hey, I have the following problem. I'm working on a solution for the owner of several restaurants. Each restaurant has meetings once or twice a month. How would I structure the tables so that I could have a layout on which I select a restaurant and then inside a portal I get a list of all employees. On the portal for each row I want to have a button or checkbox to mark if an employee is present or not. I already have the following tables: Restaurants, Employees and Meetings So far I have a relationship between the Restaurants and Employees table which I use to assign employees to a restaurant, and a relationship between the Meeting and Restaurant table, w…

Dear FM Forums Users I’m looking for some assistance to filter records based on a 2 table relationship. My solution has the following tables: Users (contains fields - ID, Name, account creation date, etc) Activities (contains fields - Activity ID, User ID, start date, start time, etc) Real life example: Lets say i have 10'000 registered users in the Users table, with registration dates ranging from the 1st Jan 2018 to 11th May 2019. Desired output: I would like to know how many users in the registration timeframe above performed an activity from the 1st Oct 2018 onwards. I assume I need to join the two tables via the common us…

Long winded but hopefully descriptive enough to describe my problem. I have the following Tables: Students Ledger Ledger_Chgs Ledger_Pay Ledger_Self They are connected as such Students table to Ledger table - StudentIdPk > StudentIdFk Students table to Ledger_Chgs table - StudentIdChgs > StudentIdChgs Students table to Ledger_Pay table - StudentIdPay > StudentIdPay Ledger_Self table to Ledger_Chgs table - StudentIdFk > StudentIdFk These are all used with a layout called Students. In my students layout I have a portal to Ledger_Pay (and Ledger_Chgs but that's not an issue) In this portal I can create a new record and record…

Hi gurus, Need some advice, I am putting together a job tracking system for a print-shop and I am running into a dilemma where I need some advice. Our ERP is Sage 100, but unfortunately I have not been able to retrieve data from it using ODBC, it appears File Maker ODBC is not compatible with SAGE as I keep getting an error (it works fine from excel but not filemaker). I am currently exporting all the needed data to various CSV files and importing them into a single filmmaker database with a table for each CSV. some of this tables contain as much as 30K records. my issue is with speed. every time there is a lookup it sometime takes a few seconds. My questio…

I am stuck on how to move forward and feel I might be taking the wrong approach. I am creating a database for my one-man business - service engineer. I have created a customers table and a jobs table, linked the two via id and foreign_id with a one-to-many relationship and all works well, the id’s are created using the UUID method. Every time I create a new job, a new serial number is created using the auto-enter serial number in the field called ‘jod_no’ in the jobs table. This is all ok if its just one call out and the jobs fixed and dealt with on the same day. But sometimes I need to create two or more jobs (and I refer to them as ‘instances’ o…

I am trying to decide on a schema approach to a small add-on to a vendor/supplier evaluation process, we will have this 'survey' done at intervals, and I am deciding to either put data as a fields or as a table with a key value pair which would require a scripted creation of the data set. Or storing the data as a JSON array and pivot it out to a virtual list. The jury is still out if we need to have aggregate data across all vendors to get a tally if say one of these items is a no. If data is stored in an array computing the aggregate across dozens of records can present its own challenge. Curious to entertain some concepts and suggestions.. During t…

Hey, I started working with FileMaker a while ago and so far it's been pretty fun and fairly easy to learn because I worked with MySQL in the past. However, there are still a few questions I have. I'm used to having complete freedom when it comes to databases, because in Python or C++ for instance I would simply write my own queries and get whatever data I need, no matter if a database is related to another or not. Anyway, the flexibility is still what causes me some problems. I have the following problem. I'm working on an ERP (Enterprise Resource Planning) software for the owner of several restaurants. I need to have the following tables to start with: …

Hi Folks, On my ToDo list is the need to catalogue music resources in my department. My initial thought was that it would be quite straightforward, but after a morning pondering the factors, I wanted some advice on the structure before I spent time running around in circles! Here's what I need to consider: Resources could be in a variety of media: Orchestra parts (for individual players - but they'd always(?) be a full set or not worth keeping) Orchestral scores Choir scores CDs pdfs of parts or scores. They would always consist of at least one movement, but a work will often have more than one movement. A symphony…

Good evening people. As the title states, I am a complete newbie to FileMaker Pro. I've done some fairly extensive work with Excel, but this is my first experience with databases. For now, I'm looking for advice and suggestions. I promise I will have plenty of "how do I... " questions later, but for now I need to pick a direction to go in with my new project. I've spent a bunch of hours viewing Intro to FileMaker tutorials on YouTube and I've experimented with creating a couple test databases. If this is not the correct forum for this question, please let me know. So, my wife and daughter are heavily involved with riding horses. I've been drafted into bein…

I have some 36,000 CDs in a flat database (FM Pro 11). I am considering building a new one, where the different data elements have their own table and bring it all together with relationships. It would be a great help to analyze a sample database, but Google has not given a useful result. The example database need not necessarily be tailored to classical music, but the principle should resemble my needs, so I can learn about the structure. Where can I find such a download? I realize that the following is rather loose and that it will show retail issues, but here are my initial thoughts: Tables: COMPOSER(s) - First name - Surname - The year o…

Hello friends! So I'm working on this report on optimizing sequence queries and I'm specifically studying SQL TS (SQL for Time Series). It's basically an extension for SQL for finding patterns in sequences. So I need help to translate this phrase into English, because I really have no idea what it means: What is a predicate? What is a fully instantiated predicate? Thank you in advance!

Could anyone with a Filemaker brain please advise on how i can "mark" a found set to show they are related to each other? I have a single table database which contains 1000 records, I search the database and find 10 records that meet the search criteria. I want to "mark" all 10 records to show a relationship to the other members of that found set without going into each record separately, similar to a snapshot but not creating a snapshot file. My desired result would be if ANY member of that 10 found records is viewed the user can see it as 9 other records which are related. Many thanks for any advice on the best way to do this.

Hello and thank you all, This is my second question in this forum. I am novice in FM and I work for a medium-sized aviation organisation. (my job there hasn't anything to do with FM). I have a vision of creating a database to hold all data used by this organisation. Our organisation has many specialised departments. For example: a. Personell dpt b. Training dpt c. Pilots dpt d. Technician dpt e. Logistics dpt and so on..... So far I have build two databases to keep track of Aircraft spare parts and Flight records. How does one starts designing such a large database, so that I wont have shortcomings…

I could use some advice on the best way to do a join. I've got one table all setup which is a collection of yeast strains which have a unique Strain ID. The other table contains 96 well plates, we currently have ~10 plates. Each plate has unique info, but each of the 96 wells can contain a yeast strain. A given yeast strain may exist in many wells across many plates, but each well only contains a single strain. My first pass was to just create 96 "location" fields right in the Plate table, limit it to a "StrainID" value list, but then I realized that to setup a relationship there I'd need to create 96 relationships which doesn't seem right. I think I can do ever…

This is the database structure as I have it. Account ---< People There could be several people related to an account originally i had their anniversary date stored in each persons record however I have seen incomplete information or mismatched information, plus it required a duplicate entry on two records. So then I figured we only need ONE instance of this anniversary date so i could store that in the main account table, and also a flag if they wish to receive an anniversary card. But now i need to link two individuals together so that I can derive the names to put on the card & envelope. And also break the link when…

I've currently developed a database using the data separation model whereby I have an interface file, and a database file. The interface file is held locally on each computer using the database, and the database file is held on a server. There are going to be 3 - 5 different organisations all contributing data to the main database, but ordinary users should only have access to the data created by their organisation. Some "super-users" will have access to the combined data from all organisations. I currently have lots of reports which cycle through the database, analysing specific parts of the data and returning found sets. What is the best way to segregate out my dat…

Hi, I'm developing for a service company that has a telesales and a field sales team. They then have a service team that visits customers and services products on site. Question: Both prospects and customers are currently in a table called 'companies' and have a simple flag (dropdown list) to indicate the difference. Challenge is that the table has grown to 124 fields with some of the fields only relevant to either Prospects or Customers. Remarkably I have never needed to create 1:1 relationships in such a scenario but I'm thinking of doing so. Would it be advisable? I'm thinking specifically it may lead to performance improvements on list layouts with some 20,000…

working on a database that manages shows the contract is the main table. Currently the contract Event Date and Time are just two fields - and the time was something like 4pm - 9pm in a text field. On occasion the contract was for a multi day event and that was recored in just a notes field. Additionally there is a Setup time - usually the same day as the event a few hours prior to the start time but many times setup is the night or day before the event. So I am structuring an 'event' table that is a Date / Start / Stop / Duration / and Event type [Show / Setup] some shows span the midnight hour and start at 7pm and then go till 2AM the next day so i am thinking that …

I would appreciate any suggestions for a design that supports Change Orders. Client has Orders>Order_Lines. PDF of Order is signed and accepted by client and is then locked from edit. However, an item needs to be cancelled, replaced, qty edited. I need to maintain integrity of original Order. Orders, btw, can have 1 or more Invoices (items are invoiced as they ship). I have come across this post: http://stackoverflow.com/questions/11963386/table-design-to-keep-track-of-change-orders-in-a-customer-order-system?rq=1 which is the closest post I can find on the topic. tia, Barbara

Hello, I want to write the script that print the same structure report from different tables(Not combined tables to 1 report). Like Invoice, Proforma invoice to same report layout. Just press the button then directly print the certain record. Thanks!

Hi, I have used filemaker a few times but don't claim to be an expert, I have also used and developed web database applications. I am currently creating a company workflow system in FileMaker 13 which will run off filemaker server. A colleague of mine who happens to be a friend of the boss has taken it upon himself to design and build the initial database table structure, this admittedly has saved me a lot of time!, however I do not agree with certain decisions he has made, the main one being the table containing the customers, he has said all the customers, suppliers and staff should be on the same table as a 'contact table' which I see could be of benefit espec…

Hello every one i am very new to file maker i want understand the architecture of filemaker.... It will be helplfull if somebody have documents or references Thanks Girish

Hello, I am trying to think through a relational database involving medieval maps as linked to manuscripts. I have set up the manuscript end of the database. What I am trying to figure out is whether or not I should make the maps in each manuscript relational or not. Each manuscripts comes with on average 21 maps but these can sometimes be missing, so the numbers can fall. Some contain only 1-2 maps and others no maps at all. On the other end there are a few manuscripts that contain 100+ maps and images. There is in other words, no steady amount. If I had to pick a fixed number I would opt for 21 since those are the map manuscripts that I work with the most. I…

Trying to properly setup a price list for a project - just thinking out loud... Product table has 200 items. no prices only descriptions & specifications The price table has the id of the product and the price currently there is also a distributor price and a volume price these are calculations that do a percentage discount off the main price. However this will change and each item will be allowed to have a different percentage for the distributor / volume. When a client who is flagged as a distributor or a volume sales client they would get these prices looked in to the invoice. So now I am considering adding an effective date so that we could ad…

I've been working on a database for my father's small apparel business and have run into an issue I am having trouble solving so I'm wondering if possibly my approach is wrong and what is the best way to fix it. The database is meant for inventory tracking and invoicing but more importantly is where the inventory resides. The business imports different styles of clothing from overseas and receives a packing list which lists every item that is in every box. With my database, I input that list into a layout called Container through a portal on a table called Container Data. Each line item corresponds to an individual box with a specific style color and size and the amo…

Rethinking a bit about how I want to structure this module in a new system and would welcome any "been there, done that" words of advice. LaRetta has kindly helped me and shared her experience, but I don't want to burden her completely! Considerations: 1. What tables do I need? One for each entity? Share the line items table? What would Len Silverston do? 2. What action changes the quote to a sales order? (Biz rule? In this case, when the 50% deposit is received). 3. How and where do I store discounts? With each line item or as a related line item? 4. Are payments another line item on an order? 5. If each item may have a unique shipping cost...is that a separ…

My first post here from a relative novice, so I apologize is this is a dumb question or one that has been covered many times. I'm starting to build a database for an academic department, to keep track of contact information, as well as publications, grants, applications, and other items associated with the different department members. Department members can be one of several different positions or "ranks," such as faculty (professors, associate professors, etc.), post-docs, grad students, staff etc. I also want to keep track of the relationships between these various department members, for example, which grad students "belong to" which faculty member. Ok, now my que…

Not sure if this the right place but was curious about people's thoughts on specialized tables versus multi-purpose tables. For example, let's say you have a database with a number of different tables (Table_A, Table_B, Table_C, Table_D) Table_A needs to have a many-to-many relationship with Table_B, as well as with Table_C, and with Table_D so it needs a join table between each of them. In the past, I would create three different join tables, each for the specific join relationship (Join_AB, Join_AC, Join_AD). The only fields in the Join tables would be its own primary ID, Table_A's foreign ID and the foreign ID of the table being joined. What if instead of…

Hello everybody. I need to create a notes field, and I would like to know the best way to approach this. (The intent is to have a time stamped record of each interaction with our patients.) Should I have one big field, and rely on the users to timestamp their entries? Or should I have a portal relationship with a related "notes" table, with each new note being a new record? Is that best? Or is there some better solution? I imagine this is well trodden ground around here. Any advice will be appreciated!

First of all, the actual task is for the employees to go around the campus and hold down the water fountain for 30 seconds first thing in the morning.  I created a database and a table has all the water fountains, with fields, location, fountain number, and description. I created a separate table called tasks, that has fields flushed(drop down menu with yes or no, and a notes section). The third table has fields including, employee first and last name, and their employee number.  The idea is to match all of these together in order to know which employee did the task and where they completed it or not. Also to somehow search it by date period. So maybe from 2/2/2…

I'm revamping a fairly complex database (anchor-buoy, separated-data-model, 300+ table occurrences). There are a number of root table occurrences that refer to the same table but are distinguished by access level. In other words, based on the Students table, there is a root table occurrence Students_SV (for a student's view of their record) and a root table occurrence Students_AV (admin view of a student's record) . Then there are separate layouts for Student View and Student Admin view which each refer to their respective table occurrence. This seems unnecessarily complex (there is a lot of redundancy between the Students_SV and Students_AV table occurrence chain…

Hi Hope this is the correct section. I was going to try and create a system for the scout group that I run tp try and cut costs dow. I was going to have a contact information section and all badges the badges have different elements to them which when completed show badge completed. Also I was going to have a paid subs section and petty cash section. I am not sure how to go about the tables etc as tp what section to have tables for etc. Any advice on the above would be brilliant thanks.

Monthly i receive by a third party an excel file of "statements" This file is FLAT and a record is a monthly billing statement Name, Rent, Gas, Electric, Storage, etc... In total there are about 2 dozen columns of items that are essentially "line items" to the statement. And every so often there are "adjustments" that need to be made but attributed to a specific item such as "rent" or "gas" that would mean that in a flat structure another field for each item. My instinct is to actually take the flat file and process it to generate actual line items in a separate table for each statement. Then any adjustments it would just be a new record for any give…

Hello, I'm fairly new to FM and I am using Pro Advanced 12... I have a contacts table and an events table, with PK's for records in each... i want to be able to assign multiple event records to any one contact. How do I do this? I think i require a cross referencing table to store records of PK's in each table, but I'm not quite sure how the relationship should look. I'd appreciate any help you can give, PS. Sincerely apologies if I have posted this in the wrong place!! Steven

Hello, I'm a FileMaker newbie, attempting to build a database to track invoices & payments for a food broker. I can find plenty of information on straight invoicing, but here's my issue - because this is a brokerage, I have both prinicipals (the suppliers) and customers - we are the middle men. (We work with roughly a dozen principals and over a hundred customers) The principals invoice the customers directly, and we are paid a commission (usually monthly but this varies from principal to principal). What I am trying to create is a database where we can generate POs (pulling from both a Principals and a Customers table), pull from Items table (description, cost, …

Ok, BIG newbie. I'm trying to wrap my mind around how to have an item made up of a combination of items, ie: (hot dog + drink + chips; chips + drink; breakfast burrito + drink; etc...or even Hot Dog + Hot Dog + Chips + Drink) What I have are tables with the item categories (easy to keep straight "to me"), such as Hot Dogs, Toppings, Drinks, etc...so I could have a table Specials that would have a line item made up of the combinations. Hope this is making some sense. This is what I have so far (just making tables that make sense "to me" at this point...any advice is appreciated) I'll just attach my starting point db. (EDIT: says I'm not permitted to upload…

I am running FM 12 v1 Advanced in OSX.... My app has these tables: Members: PK_MemberID FK_TypeID LastName FirstName ... etc MemberType: PK_TypeID FK_MemberID MembershipType (holds text as in 'Regular', 'Honorary' etc...) YNMMDues (User selects 'Yes' or 'No') YNPMDues etc.. Each member can only have 1 membership TYPE at a time. Each membership type is held by many members. \(Sounds like 1 to many to me) I have a layout based on Members. I have a portal on that layout based on MemberType Problem: If I have a cartesian (X) join between the portal and the underlying table, the p…

I'm on FM Pro Adv v11. I have Table1. It has a field called "_id" which already contains variable length text data: example - 1234. I'd like to convert this data to text in the form: HID00001234. That's fixed 11 positions. I've tried Replace Field contents using a Calc value stipulating: Table1_id = Table1:_id + "HID00000000" thinking that it would "add" whatever is in _id to the string "HID00000000". But I realized I cannot "add" what isn't a number. And I don't want to concatenate. Is this a script function? Or would there be something built in to FM to accomplish this?

Hi, I am trying to develop a solution to sell customized furniture. I have successfully developed a simple invoicing solution using the following tables Customers Invoices InvoiceLines Items Vendors. The graph looks something like this Customer -< Invoices -< InvoiceLines Items >- Vendors. This very simplified invoicing solution however didn't work for our application. What I really need is to be able to customize the items, and this is where I am right now. My items (furniture) are generally priced by the grade of fabric and any options and or finishes that the customer wants. I cam up with the following tables Vendors, Items, Grades, Fabrics, Finishes, …

Hi. I'm back on my Food Pantry project after taking a long break. I really need to complete this project. I unfortunately tend to get frustrated easily. I'm trying to simply understand why I can't make this thing work and I think it's because I don't have my relationships properly identified and defined. Can someone please look over this scenario and tell me if I'm even on the right track. Table structure (simplified) Members - individuals with names, DOBs etc. Households - many individuals from one person to several persons including children, live-ins, etc. (Pantry) Sessions - daily freq, many households Pantry Sessions: Individuals come vis…

Never really ran into the following problem - I think it is my lack of understanding of users/record and FM Server or the fields I am adding to my layout. I tried to create a solution to checkout equipment to users (There are approximately 150 unique users in my solution) - all with the same privilege set) Part of the solution was to have the ability to reserve items. Tables: Transactions, Users, Items, TransactionItems. Relationships: Users:userID::Transactions:userID, Transactions:TransID::TransactionItems:TransID I have two users who log in at the same time with their username. I have a global Field Users:UserAccount (an auto enter calc Get(AccountName…

I am reworking a database in FM12 that was originally designed in FM3. I'm going to start from scratch. Part of what I plan to change is to use a table as a reference to add data to two fields on my main table through value lists. Main table = Matters 1 Matter has 1 insurance company assigned to it 1 Matter has 1 adjuster assigned to it (The adjuster works for the insurance company at one of it's field offices) 1 insurance company has many field offices 1 field office has many adjusters working there, but the adjuster only works at one field office The fields on the Matter I want to enter info are: Insurance Company, address, phone fax Adjuster …

hello, I hope this is in the right section. I`ve been reading pretty much about data modeling, entity tables, attributes, keys and such things.. But when Im gonna create a database for something Im still not sure what tables I should create. Is there any right or wrong table in a solution? Or can I choose by myself what tables I want.

Hello everyone; i have been searching this forum and have come across a few of what i am facing now Issues! They have to do with manufacturing and database design. Heard a few talks about recursive structure, but i have been unable to figure out what that means in its entirety via Google and books that i know where to look. Can someone please describe or provide links where to find more information/articles in relation to recursive data structures and possible manufacturing db models? TIA: -i

Hey there, for my new cocktail database I would like to link the ingredients of a cocktail to their respective SPIRITS table entry. my first thought was to create a new table for ingredients with the ingredient name, amount and cocktail (linked to the cocktails id). this however would give me tons of entrys that I don't really need. Is there another quick and easy way to complish that?

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

I need some theory help. Not sure if it is bad structure or finds. sorry for the long post I have a solution for checking out equipment to students - with tables; Students, Equipment, Transactions, Transaction Lineitems. This is where I may have gone wrong.... There are three types of transactions (I think): reserve, checkout, checkin. My solution only creates one transaction and one lineitem with a status field; reserve, open, complete. As the student moves through the process, the status only changes... is this correct? Or should there be seperate transactions for each? Should the status flag be placed on the equipment, not the transaction lineitem?....so conf…

I have an application of Membership. The tables are: Members Meetings Visitors etcc... I have a TOC GROUP that consists of: (see pic) Members TOC Member Meeting Join Meetings First, EVERYTHING WORKS. I just don't understand 'why'?? I have a report based on MEMBERMEETINGJOIN. In this report I have a subsummary based on MemberMeetingJoin::KF_Member Item #1: The Members TOC KP_MemberID is a SERIAL field with autoupdate. Item #2: MemberMeetingJoin::KF_Member is a plain, text field that when placed in the report sub summary SHOWS THE CORRECT FULL NAME; ie, John Jones? Huh? Shouldn't the KF show the KP??? It's late s…

Hi, I have a table where I need to view the same table, only each record needs to view the previous record. Apart from the 1st one (obvilously) How can I do this? I set 2 calc fields. But the relationship model wont let you view records for 2 fields that are based on calculations. So, how can this be achieved? For example. I have a unique ID calc field (ID)set to "get (record number)" I have another field (ID_VIEW) as a calc field set to ID-1 therefore ID_view will always have the same number as the previous record, except the first record which see nothing. I can't see a way of having a field, other than a calc field, that will reference …

Hi guys.I have a room allocation database. I have made a global number field, and when a room is allocated, it reduces the available number of rooms by one. Very simple and It works fine, but the number does not match for other shared users. I want the number to match for everyone. I thought global would do it. Any suggestions? Thanks

Hey Everyone; JUst wanted a chime in on this. I have just started to add a new way of creating records and wanted to know if you all thought / think this in necessary. eg. tbl_PRODUCTS LAYOUT { X } tbl_Products»attributes (Cartesian join) tbl_PRODUCTS LAYOUT { ID = ID & attributes_ID (ALLOW CREATE) } tbl_Products»attributes»create -i

I've got a database to keep track of classes teachers are taking. Some classes are taken by individuals and some are taken by groups. I have a field in the classes table that gets tagged "GA" to indicate its a group activity. There is a separate committee table used mostly to store globals for the committee's interface (those people responsible for entering which classes are taken). I'm trying to create a portal or drop down menu that shows all group activity titles, but I don't want to see 6 if 6 teachers took the class. I tried to set up a portal, but all the duplicates show. Then I tried to set up relational value list so that only single classes show. When the c…

Hi, I have two tables, one is warehouse with all items I have and other one is called bill of lading. I am dealing with sellable items and demonstration items. It means, I send out and after week or two receive same items back in warehouse. I do not create invoices. So far I did as follows: in table warehouse I have fields catalog number, serial number, description, incoming date, and outgoing date. Based on incoming and outgoing date I have calculation field with simple result in or out. in table bill of lading I have user information fields, product informations fields and select items field. This field is a value list filed with outgoing date. It me…

I am working on an Event management database. Each event can span multiple days. I need to keep track of people who attend the event and how much they get paid for attending the event. Each person can attend any number of days of the event, although it is most common for people to attend the entire event. An example of the scenarios I need to handle: if a particular event lasts 5 days, Adam will attend all days of the event, Benjamin will attend the first 3 days, and Caleb will attend the first and last day. From this data, I need to be able to create a report with totals for each day of the event (who attended the event that day, and how much everyone was paid on that da…

I have a database that keeps growing larger and larger (the number of fields, not the number of records). I want to reorganize before things get out of hand, but I don't know if I should create related tables for different categories of information or if there is a better solution. The main content of my records comes from an outside source, which sends me an excel sheet with applicants for the program where I work. This is all demographic information and contact information. Next, I begin analyzing applicants, and marking the applicants in various ways, creating new fields based on their qualities, status, etc. The issue is that it is becoming in…

Hello. I inherited a database of several thousand records that are used for a tour. However, whenever I try a search or sort of the current database it keeps asking me to open or find an older version of itself. Since that old file was in the same folder I inherited with the new one, I do open it and then it works fine. I'm wondering, however, why this is happening and if there is some change I can make to the current database to stop it. Hope I explained this well enough for someone to understand. Thanks in advance for your advice!

I'm trying to understand the proper use of optionality on potentially both sides of a relationship. Example: Entity A has zero, one, or many entity Bs. I'm thinking this would be drawn as: A --------o< B ... rather than: A -o-|----o< B ... because the "o" on the right side fully explains that there may be no related items. My hunch is also that the "|" on the left side, alone, would not be necessary, since an unadorned line on the left means 1: A -|------o< B I thought I'd ask here. Bob

Hello - I am very new (totally new) to building databases and am trying to build a database for a nonprofit group. I have four tables: Contacts, Donations, Accession Lots and Artifact Catalogue (see attached). I have had pretty good success on the 'individual side' - contacts and donors as these tables are about individuals. I have used several layouts from the same table and this has worked beautifully. I even added a button for a popup with contact info for a donor coming into the Donor Table from the Contact Table. Tah dah! Now my problem is that the other 2 tables, Accession Lots and Artifact Catalogue are about items donated to a museum. I need to tie in the…

Figured it out.

I hope this is in the right forum. I'm working from the Contact Management template (FM 11 pro adv). I'm new to FM and I'm unsure as to how many tables I need. Reading "The missing manual" and the actual manual hasn't helped either... This is what I need: Contacts, searchable by, for example: - expertise - type (ex: scientist, funder etc.) - task force membership - publications - funding area - type of service provided - speaker at a certain conference etc. For example, someone might want to find all contacts who are hydrogeologists in Spain and spoke at a specific conference. Or, all contacts who provide printing services in L…

I know some may be frustrated by even reading the topic's titlte, but WAIT! I just want to ask something simple (I think...) I've browsed through all (at least most of them) the posts and topics regarding the subject. I've also extensively reverse-engineered many examples in order to understand them. Since I am an enthusiast (avatar confirms) I can't afford investing to commercial solutions (BH-A, CNS, DataFileGuard etc). So trying to see if I can make something as close as possible to the commercial ones, I was wondering: Can an audit Logging system (with changes tracked to a separate log table - not like NightWing's Ultralog) be implemented in a way oth…

I have a script that I need to run after the first record in a portal, leaving the first record alone. Is it possible? Any help would be appreciated. Thanks in advance.

I have a data base that has different tables in it. Table A is linked to Table B and works just fine. Table B is linked to Table C and works just fine. Now I am copying info from Table C to Table A and I am experiencing problems. It's weird because I was just using a set field through a script and received the following; "This operation cannot be performed because one or more of the relationships between these tables are invalid" Can someone explain this to me please. I then tried just grabbing a portal and populating the information that way. The constant fields transfer over no problem. But the number fields or date fields (seeming anything that has v…

I keep running into some roadblocks, so I'm trying to take a step back and make sure my structure is making sense. I have a community tennis club that I want to update our database form the flat structure I inherited. We have households (Families) which may have any number of Members. Each Family will have a number of Transactions throughout the year. Most transactions are in regard to a single member, but we also have what is called a Family Membership which covers all the members of the family. So a Family can have many Members and Transactions, a Member can have many Transactions and in the case of the Family Membership a transaction can apply to more tha…

Hello, this is the diagram of an online bookstore I made. There are some simple business rules applied: Customer can order many products(Items). An Item is considered book, cd, dvd, e-book,... Customer can have many addresses. Item can have one author ( for now, i will maybe expand this to many authors, which is logical i think), one publisher and many translators. Also, the item can be in many categories. I think it's all in 3NF, maybe there could be problem in the Customer table with the email and username attribute. The real problem is in the entities and their relations. I have two problems. I dont know if the ordering process is…

Hi, to no avail I have spent all day trying to figure out how with 2 already existing tables to get an already existing value from a field in one table to automatically fill into a field in a 2nd already existing table. Basically I don't want to manually fill in a unique # associated with a unique name 900 times and want FileMaker to find the empty variable, go to a 2nd table, look up the # assigned that variable name, then fill that # in. I have a script that creates a new record in a 2nd table and autofills in territory name and territory number (unique serial # generated upon creation of new territory). Both name and # are in each table and linked with a relations…

Ok, I am not sure how to do this. I have a database that I have started developing. It is for a school. What we need is a way to log in tardies. So I have two tables. Student: StuID LastName FirstName Gender Grade DOB Class Periods: Period1 Period2 Period3 Period4 Period5 Period6 Period7 Now my question is this. How do I store the information for each student. In other words Student A may have a tardy one day for period 1, then another day for period 1. I need the Field boxes to clear everyday but save the information. I know I probably need another table. Please advise.

What's the best way to deal with data that is really only relevant to specific subgroups of an entity? I'm working on a directory of ORGANIZATIONS and PEOPLE. Organizations all have contact info (address, phone, website) and orgtype (e.g., church, school, charity). But some information is only relevant to certain kinds of organizations (e.g., service times for churches, grade levels for schools). I can see three options (TABLES in caps, fields in lowercase). Option 1: ORGANIZATIONS _kp_organization address phone website orgtype grades (blank for non-schools) service times (blank for non-churches) Option 2: CHURC…

Have a solution i am working on that the client has attributes values for equipment items. At present there are about a 22 or more attribute items for each equipment each field can have it’s own value list such as: Power Requirements Power Cord Receptacle Dedicated Circuit Emergency Power Dedicated Circuit and Emergency Power or Data Network Cat 5e Cat 5a Cat 7 Wireless WiFi Required Single Data Outlet Required Multiple Data Outlets Required VoIP Device To Central Monitoring Station Some fields request user entered value like: Height [cm], or Weight [kg] I will have some other technique using field trigger…

Hi all, I am working on quite a complex invoicing solution. There are 3 departments that issue invoices - each need a little bit different invoice layout and some different fields and different related tables. For example one department deals only with products so it will pull products from the separate products table. Second department deals only with services. So it will not use any products, but will write their own description of service into invoice. There is third department similar to second but also needs a little bit different layout/fields. They all should follow same Invoice Serial number though. The question I have is: 1. should I tr…

Hi All, I'm trying to think about the development of a survey database. I need to create surveys from different perspectives, Admins to people into the nitty gritty. I tried to search the forum for some intro/samples already posted etc, with little success. Any links to some examples would help in the planning part. Thanks Mike

I have a basic understanding, but confuse myself every time I begin to create a relational database. Please tell me if my thinking is correct here. I need to track employees in positions for each shift per day. What I currently have is this, but I'm questioning one table: The 'shifts' table now seems redundant. While each day has 2 (and sometimes 3) shifts and each shift has multiple shift-positions, there's only one attribute for each shift (day, night, or detail). In shift-positions I need to reference which shift it belongs to, using a foreign key to do so. I could just move that shift attribute to the shift-position table, eliminating the shift table …

Imagine a DB with 1000 records for people. In this DB there are some people with the exact same name. How do you build the table that holds these people and ensure that they are not the same person - with the restriction that you can not ask them for their social security number? Some may suggest that when entering the person's record and another record of the exact same name appears - that you just add the record and it does not matter because of the serial ID placed in the DB. I agree that the DB knows they are different - however, how does the person entering the data realize that it is or is not the same person? This may sound simple - but I think not. It re…

I don't even know where to start. I created my 'big three' databases when Filemaker first came on the market YEARS ago. Through all the Filemaker PRO changes, all was well. FMP 10 ate my lunch. My complex relationships between my databases went to hell. I can NOT find a definition of the word "table" that I can understand! "A file contains the tables". What? I thought a database has fields & layouts. I know what a "table view" is. So I gave up trying to understand the words. I opened the 2 databases I want to 'have a relationship'. Went to Manage database > Relationship tab. There is a 'picture' of the database - is that a "file" or a "table"? So…

Alright a little confused. Here is what I have. I am setting a key database. It is for a school. Right now I have one table that has everything so that is not a true database. 1. Teachers 2. Keys 3. Rooms Should I break it up to these three tables. I have over 260 rooms, so obviously a key for each room, but then I have cabinet keys and desk keys. I was thinking about making a table listing all the keys but that would be over several hundred. Would it be better with a value list? I want to be able to click a room and it displays what key belongs to it, i.e. room 206 KC1. Also want to be able to link the key to the teacher and the teacher to the room. Where …

I am new at large databases, but familiar with record counts below 20,000. The FM site states that 64 quadrillion is the max number of records, but importing 4 million does not work. So I imported 41 smaller files. Now I can not sort those without the program quitting and recovering is taking a real long time. I am beginning to think that this project is not going to work using FMP. Is it my ignorance or reality? If FMP states that a file size of 8 terabytes is possible then it must be me. Mine is 4 gigs. I could use the 41 files individually. Would building relationships with 41 file tables be faster? Will I have to build 41 different web pages for users to …

I want to "try" this table structure: 1. Supertype Table: Parties 2. Subtype Table of Parties: People 3. Subtype Table of Parties: Organizations Sample data entry flow would be: Data is entered only in the Subtype tables (People or Organizations) which creates a one-to-one relationship automatically to the Supertype table "Parties". I can get the tables to connect automatically using the method Fenton had provided some time ago, at this link: http://fmforums.com/forum/showtopic.php?tid/194695/tp/1/ but that method requires a field from the Supertype table to be on the layout of the Subtype tables that the User would need to enter some data or…

Hello, I just started working on a real state database that somebody else started (yai ...). It has the following tables (simplified): - Buildings - Apartments - Contacts Now the customer needs to add two new entities, related to "buildings": - Broker companies - Sales agents (1 broker company can have multiple sales agents) So I created two separate tables, one for "broker companies", and another one for "sales agents", thinking that in the future they might ask, for instance, for specific broker's or sales agents' reports (e.g., broker fees by period, etc.). (I also created a join table so they can have a sales agents portal for each b…

Hi, I`m stuck with a very simple problem so ill just be breif. Thank you for your consideration! I have two tables: Quotes Invoices Inside quotes and invoices i have a portal row relating data from their List items. I need to transfer all the fields "products, qnt, value, etc..." From quotes to invoices, without deleting the data from the quotes table. so basicly i need to copy all my quotes to an invoice table. seem rather easy but i dont have any experience with variables, and i cant seem to find any good tutorials. If anyone could help i would be very greatful. Thanks again Murillo

Hi, I have a problem that i havent been able to solve for a long time. I`ll get straight to the point: I have a purchases and sales table linked to a items list wich is pulling data from the products table. I need the full quantity of the products. So, if i purchase 4 and sell 2 then i have 2 left. very simple but i cant get it to work. Let me simulate my problem: lets say i purchase 20 pieces of gum. In the items list it creates a new row of a record saying i have bought 20 pieces in a quantity field. Now, i have sold 10 pieces of gum. In the items list it shows i have Sold 10 pieces on an exclusive quantity field. Finaly, I go in the prod…

Outside of this forum is there a master guide as to how relationships should be drawn out. I have multiple layouts and tables all interlinking back and forth with random info and conditions and calculations. I want to see if i can simplify my chart and find out if im doing anything wrong. Forgive me as I cant post my DB due to privacy issues but looks more intricate than a spider web with all the different calcs and layouts.

I've been thinking through the logic of this issue for a few months and I haven't been able to figure out a solution. I hope someone has run across this and has some advice. I have two tables (at least). Let's call one Students and the other Classes. It is a one-to-many relationship, so that for every Student record there are numerous Classes records. I want to do some analysis on this data to predict Student success in their Classes, so I'd like to export the data into a statistical analysis software (SPSS). I think the best way to do that is have a flat file combining demographic (Students) data with grades (Classes). Assuming I start with a Student re…

Oh where did my tables go? I tried to set up a relationship. I've had these done for YEARS - since FM 4! So none of my records show up & I see Is it because I saved a copy of a database with no records then changed/ renamed it for another, but similar use? Am I ruined? HELP! please

Hi folks, A sticky three table data/relationship issue. I am trying to figure out how to establish a relationship between two tables that, by logic, are two table relationships apart, hopefully without duplicating data in multiple tables. I would appreciate people insight on my data structure and if there is a way to accomplish what I want to do. I've always seen people ask for the real world issue rather than an abstraction, so here goes: I manage a database for a Botanical Garden that tracks their plant collections. The database tracks, among other things, about 23,000+ accessions of 14,000+ species, in 18,000+ locations of hundreds of thousands of ind…

2 DB's (Customer List and Active Projects). I have set a relationship to Company-Company and Project Manager-Project Manager. In the Active Project DB, I wish to view the name of the company and specific project manager's name. Not a problem with the company. I set a Value List and it looks up all of the Companies back in "Customers". Same with the Project Managers, however, it shows everyone's name. How can I set this up, that once I set the Company, the only persons that appear in my Project Manager field are one specific just to that company, and not every person?

Hi everyone. I'm new here, so I hope this posting is in the right place. I have been using FileMaker Pro for a little while now. We use it here at a more basic level to keep a customer database. We have two data files that contain customer records, divided by state. Every time we make a layout change on one database, we have to go in and make the same change on the other, and so on and so on until we duplicate our work. We've thought about merging the two together but we wanted to keep the records separated because they are two different markets we keep separate. Our concern is that if we merge the two together, then when we do a search for an address, …

I have become the defacto IT guy at our non-profit organization, so I am learning FM as I go. Forgive me if my problem is rudimentary, it has caused me a lot of stress before I remembered how great the internet can be. Background: I have a database with a table with detailed demographics on about 6500 people. I also have a table where an intern will be typing in attendance data that we need to collect. The forms in the attendance table are organized by date and group (we have several groups on some nights). Within each form are about 10-20 entries consisting of last name/first name, and then some relevant information from their demographics (same type of info…