CurlyD777

Hopefully this is a simple question... I have a main sales database full of accounts and sales. This was designed to simply keep track of sales based on who/when, etc. Now I've added an external file that is a database that handles invoices / payments. In the main database, I reference this file as an external source, allowing me to show transactional data for each account. In the invoice / payment database, I reference the main accounts / sales file as an external file. I need this to lookup sales details based on sales for invoice creation. I know this isn't much different than having 2 or more unique table occurrences in the same file, but I'm not so familiar with external sources. All of this is on a FileMaker server, and I'm concerned that I might be causing a circular reference since each file references each other. Am I ok to do this? Any feedback is much appreciated.

I have a question also in the "dependency" talk...whenever I make an update to our increasingly complex database, i get a pop up screen saying "rebuilding dependencies..." It takes only 3-5 seconds to complete, but as we plan on taking our database offsite, I'm concerned too many dependencies are built to make it unusable remotely. In a database design report, I have 741 relationships (which i know are not the same as dependencies, but I don't know how to quantify how many I have) As I continue to build the database, I'm starting to creating future modules as related files. I still am making the relationships in the main database, which is creating more dependencies. Anyone have input if it's best to have the relationships in a single file, or does it make a difference in making these relationships in the related files, and displaying layouts from those files when needed. During startup, does it still check over all related files for dependencies as well? or will it pause when first accessing these related files to determine the dependencies? Any feedback or thoughts much appreciated.

Thanks for the update, we'll stick with the reference only method. Luckily, since it already was a challenge to sort and find files, we've made a very strick naiming policy to store these. In other words, each file is stored and named in the same file / folder structure. You've mentioned I should create a database with meta-data to help find these files, would you mind elaborating on this concept? thanks

I am also starting to look into a image sharing feature for our database. We're an advertising company and need to store our client's many PDF's for particilar sales in this Currently, we have 10000+ pdf's that are typcially 3-10 mb each. (Our practice is to never delete a revised PDF for each client so our PDF collection grows very fast) I would estimate our PDF archive is currenlty about 1 tb. Each file is stored by client name and then by their sale/job. You can imagine how time consuming it is to find a particular client's pdf using our current file sharing volume. I would like to store these PDF's in the database not as references but as actual embedded files, since I could imagine how hard it would be to rebuild referenced paths if a volume or path name gets changed. Long story short, prior to starting this project, I'm concerned about overloading filemaker with a libary of 1TB, and trying to navigate around files 3-10 mb each. I would appreciate it if someone could let me know if I'm overloading filemaker with storing files this large into a single database? (We are using fileMaker server 9.0 advanced on an intel based xServe with 10.5, so I think we have the hardware side covered.)

Well, here's the bigger issue. We recently moved our files off of an older server onto a newer server, and, the new server is running FileMaker Server 9 on intel 64 bit xServe, vs Server 8 on the old intel iMac. This is what I've found to both cause and fix the issue...and maybe someone can shed some light if they've seen this before. In the portal, I'm relating invoices to accounts using a numeric account ID. In the invoice table, I also have a boolean "quote" field, allowing you to create quotes in the same table, but not have them show up in this particular portal. These quotes are omitted by a match criteria rule that invoice's record's quote boolean field ≠ the global "1" field in the account's table. In other words, if the quote field is 1, it's not shown, and it is' 0 or blank, it is shown. Up until 2 weeks ago, the boolean field didn't exist, so all of those previous records had an empty field. Blank fields in server 8w as not a problem as this allowed me to display and search those related records that had empty "quote" boolean fields What I discovered in server 9, you can relate and display in a portal records with a blank match field using the ≠ symbol, but you can not search those records. So in other words, the portal was displaying the records, but not allowing me to search. Whenever I develop boolean fields, I always set them auto enter 0 when a record is created, and go back and replace all records with a 0 if the database is already in use. In this case, I forgot. Normally I would have noticed a problem earlier if the records weren't showing up in the portal, but what confused me was that I actually saw them listed, but I could not find pull up the same related records in a search. Anyway, I replaced all the empty fields with 0's, and now it works flawlessly again.

Just today I ran into a problem searching a date field. In a invoice portal, that links records from an external filemaker table by account ID, I used to be able to search in that portal for a date range. In other words, I could search for all invoices older than 60 days by just entering a date range. Recently, I cannot find invoices older than a month. I can search and pull up invoices within 2 weeks, but any date greater than that , it returns no records. I even manually looked for a record that had related records with a specific date from 60 days ago, it's listed in the portal, and when I searched for that specific date, it came back with no found records. In the actual table, I can search and find these same records. Anyone have any ideas why the portal search is not working anymore? I'm using server 9 advanced on a mac OS server 10.5.

Just recently I set up the same scenario in an invoicing database. I set up a script to take invoice information (client, date, item, cost, etc) and display it on layout with our invoice graphic. The script then saves this record as a PDF to a folder on the user's desktop. (using applescript to generate this folder if it doesn't exist) Then it creates an email using client's information for account name, contact and email address and generates an email. The email first copies text from a generic text I preset in a global field, inserts text from the invoice (amount, due date, etc) and then attaches the PDF that was stored earlier the desktop. It does put the text first, followed by the attachment. I don't recall having to designate any settings in Mail or in filemaker to display the PDF on top or below the message text. If you want, I can list script steps in more detail. I'm using filemaker 9.0, and apple 10.4. Hope this helps.

I think I found a way, but if someone could advise me if this is the best way or preffered way... When creating the new invoice records in a new table, I relate the entire table of jobs to the jobID from the new invoice record to the jobID in the jobs table. I then use a lookup field to copy the related text description.

I am trying to use a value list to display a job selection field that is easier for a user to view. I am using related value lists to display and use JobID numbers associated with a spefic job name. For instance, job_001 is a unique record with a decrption field of "Uptown". In the selection field, the user selects a drop down list of a value list of all related jobID's, but it only shows text from the second field, the job description, i.e. "uptown", "downtown", etc. That part works great. The problem is that when we create an invoice. When the user selects the "invoice" button to run a script, it creates a new record in my invoice table, and pulls info from the record they are modifiying. The only problem is that the job selection field that is showing "uptown" in a pop up field is really "job_001". The job discription is important to show up on the invoice record for the customer to see. I've been searching for a while, and I can't seem to find how to get the text from that field to copy into a description field in the invoice record. Any help would be much appricated. p.s. I have already tried leaving the jobID as an ID number in the invoice table, and displaying it as a pop up list using the same value list, but since the value list only shows related records (jobs that associated with the division) I can't recreate that same value list on the invoice side

I am recreating a database to replace a series of excel spreadsheets. The visual views available in excel that we use numerous times to look at our tables is to have customer’s listed vertically, and jobs listed horizontally, and then show their sales numbers listed in the grid area. We can have up to 30 or so jobs in a given layout, and excel allows you to split the screen to “freeze” the customers column while you scroll horizontally and view the numerous jobs. Does any one know how to recreate this freeze ability in filemaker? If it’s not possible, does anyone have any good examples or solutions where you can show a customer list vertically, and then list horizontally more columns, allowing you to “freeze” the customer column while scrolling through the numerous job columns? Any suggestions or comments would be much appreciated. Thanks

That's good to know it can handle millions of records. We currently host filemaker on a dedicated 10.4 server box, but I'm still concerned about having the ability to finding & summarizing just the related records to a unique job or a unique customer. In other words, can it quickly summarize just 1 invoice record through a portal to 1 unique customer when there is over 10000 clients and 10000+ invoices? Or how about summarizing 4 unique invoice records spread sporadically through 10000+ invoices? And finally, what about listing all 100 unique jobs for the year, can those records all show the summary for all 10000 sale/invoice records related to each job record, and show them to them in a table all at once? Am I to be concerned about any other performance implications? Thanks

I am in the beginning of designing a fairly complex system for our business. We’re in the typical 3-ring circus of having a database for our clients, excel spreadsheets for our sales numbers, and then an accounting program to track invoices/receivables. I am trying to consolidate this process to one central database, and I plan on using the many-to-many model. In other words, invoices would be related to clients, and each invoice will have related line items, and then each sale/invoice will be summarized to it’s respective job (each sale is part of a unique job that we set goals/expecations, and monitor if it’s being exceeded or underperformed) My concern is the volume of records I would be generating. We average around 8000+ invoices per year, and if each invoice would have 2-3 additional related records to display line items via portel I’m concerned about bogging down the program. Also, each invoice will be associated with around 100+ unique jobs per year, which would be summarize related records associated with that job. On top of this, we have around 12,000+ records in our current database of clients, which I also want to summarize with their related invoices/sales/jobs. How I am currently planning on setting up this database is to have a table for clients, a table for sales and a table for jobs. I would set up unique records for jobs, and those records would relate summarized data from the sales records (to track performance) and relate records from the client table to track customers Any advice would be greatly appreciated on design considerations with the volume I’ll be generating. The last thing I want to happen is invest all this time making the relationships and records talk to each other, just to find out it takes too long to summarize any records. I would also appreciate it if anyone has had prior experience with relating large amount of records to mention what kind of performance problems you’ve encountered. Thanks