jtice

Since one invoice can be paid by many payments, and one payment can be applied to many invoices, it will require a join table to match them up. Working out the practicalities.

Thanks- yes, I saw that thread before I posted. I was looking for something more conceptual- data modeling. I found these resources that might be helpful to others. The first link, if you back out to the index, will show ERDs for a large number of database patterns. http://www.databaseanswers.org/data_models/customers_and_invoices/index.htm http://www.amazon.com/exec/obidos/tg/detail/-/032111230X/qid=1125540378/sr=8-1/ref=pd_bbs_1/002-8156232-3043234?v=glance&s=books&n=507846

I have an Client | Estimate | Job | Invoice solution that I'm redesigning from scratch. I now need to incorporate the functionality to record payments. Ordinarily each payment is for one invoice paid in full. But when I receive an advance or partial payment, or if the amount of payment for some reason is not identical to the invoice amount things get more complicated and I want the flexibility to handle this gracefully. The Invoice structure is simply an Invoice table with a child for the line items. I don't really want to separate payments from the individual invoices––iow, I want to be able to credit a payment to a specific invoice––but I also want to have the ability to credit payment against the overall account when appropriate because of exceptions. I'm sure there must be a tried and true structure that works well, so I'm asking for the best practice for structuring the table and relationships, whether to export (or script new record creation) to a separate payments table, do it within the invoices table, create a second child of Invoices to record payments and relate them to invoices... how is this typically handled in robust solutions? Thanks!

Yes, I see that. I'm thinking that addresses would then be attributes of Location (contained directly in rather than a child of). I don't know of any instances where I'd need more than a billing and shipping address. I supposed theoretically you could say that it's better to normalize this as far as it can go, but I don't see it practically. Location would then be only two fields- Name and ID. If that is so, then it would be logical to simply make the Address into Location and change it's place in the hierarchy (by rearranging relationships). Which brings up another question... if I change the name of a table will it be changed throughout the solution or am I just asking for trouble? Would it be better to duplicate, rename and manually change everything? I think this may be the revelation I was looking for- seems to fit. Still interested in suggestions on interface if you have ideas on that. Thanks!

Thanks for the replies and links to the PDFs. I had a few of them and added the rest to my resource folder. I do have a separate table for Addresses, which I believe would be equivalent to the Locations table you all suggested. The sticky part lies in how to make it interact with the Company and Contact tables. I have two instances in the graph, one connected directly to Company by CompanyID and another connected through Contact as such... Company>Address and Company>Contact>Address. Company is the anchor in the anchor-bouy. So when adding a new address it ends up being a child of Contact by ContactID. This presents a situation where multiples of the same address end up in the Address table, similar to the way it would happen in the Phone table if phone numbers were not inherently unique. If the data is going to be duplicated there is no advantage over keeping it in Contacts as an attribute (which is not what I want). So I think I need to flip it around so that each address or location is unique and the AddressID is located in Contacts (which should have only one address). Perhaps I should delete the Company>Contacts>Address instance and script the interaction so that the user can set an existing AddressID in Contacts, or create a new record, setting both CompanyID in Address and AddressID in Contact. I think I see what you all were thinking in suggesting Company>Location>Contact as that switches the child/parent relationship, but... well, actually that's looking pretty smooth. That may be it- just switch the order. Any suggestions or ideas on how to set up the selection and new record interaction... a highlighted portal for selection with a new record button below it?

Thanks for the tips mfero- I have a usable solution as it is and just looking for some fresh insight. I would simply like to deconstruct a solid solution and learn from it rather than trying to reinvent it from scratch. I do have those entities in separate tables so it's mostly a matter of how to set up the relationships and create a smooth user interface.

I have a Contact Estimate Invoice solution that I began years ago with the earliest versions of Filemaker. I evolved it along with Filemaker very well up to a point, but now I need to back up, rebuild, and get it right. Fortunately, I have a solid set of data files to work with. So what I am looking for is some discussion or recommendations on how best to set up the structure in a systematic way, powerful and flexible but no more complex than need be. Since this is probably the most common type of solution I'm sure there must be some recognized best practices for organizing and creating the ERD. If there is a great open solution I can use as an example, please point me to it. I realize there isn't much detail in this description but that's really where I am right now- backing away from a solution that has grown too complex and disorganized and looking for a model that will give me a fresh start on a solid foundation. One of my main frustrations is how to structure Company and Contact relationships. Right now I have Company as the parent of Contact (because the norm is a single office with multiple contacts), but then I run into situations where a Company has offices in several cities. So in that case addresses need to associated with Contact rather than Company. Company is an account so it would not be appropriate to demote it to an attribute of a contact... Anybody have any wisdom as to how to make this all work? Thanks.

I am rewriting a database in v.8 that has evolved through the years up to v.6. I made extensive use of contatenated keys to store similar data in one file, relate it back to the parent in a number of ways while keeping the number of files reasonable. For example, one file called Action was the child of Client and contained faxes, letters and phone call records. All had the foreign key "ClientID" plus a "type" field which was set by script to one of three values... fax, letter, or call. This was combined by a calc with the foreign key and made the one file do the work of three. I used the same tactic in invoice line items to distinguish service from expense line items, etc., etc. Now, as I try to organize relationships and recreate scripts in v.8 I am finding it cumbersome. My current issue is that the mulit-criteria relationships that replace concatenated keys generate lots of TO's. Then, each TO must have it's own layout before I can create a script step from that context. Then, the script ends up with so many context sensitive steps that it's hard to just change the keys and make it work for another relationship. So my question is, do I have a solution where the concatenated keys are simply more efficient than the new tools in v.8, or am I failing to properly get my head around the advantages offered by the new model? Is there any point in even trying to be more efficient by combining tables now that one file contains multiple tables? Any thoughts, general or specific, are greatly appreciated. John

Got it! Thanks. It was moving the global ID to the related table that made the difference. Of course this looses the stickiness of the selection in each record. The conclusion is, the last line highlights when the calc references a standard text field in the parent, but does not highlight when the calc references a global in the related (portal) record. Still looking for a tighter implementation, but there is another gotcha... The highlight doesn't work if the cursor goes into an editable field. So I think the best implementation is to use the permanent key in the parent, turn off the ability to add records through the portal, and set the portal fields to not be accessible in browse. This will require another layout to add contact names, but that seems smoother than having a glitchy portal implementation. Thanks a bunch for the insights Queue.

I'm using a calc field with a container result to highlight the current portal row. When the user selects a different row a script sets the ID from the related portal record to a field in the current record. The calc simply uses an IF and when the ID's match the result equals a global container that holds the hilite color. The problem is that the last portal row highlights in addition to the current row. I understand that if I uncheck the box that allows record creation it doesn't do it anymore, but I want to be able to create records from the portal. Is there a workaround that will keep the last row looking normal without disabling record creation through the portal? I've tried making the calc field not a button and even reversing the calc to display white when true and highlight when not true, but that last row highlights no matter what. I think it's similar to a button always showing up in the last row. Any help much appreciated. Thanks.