Relationship issue

[David-66]

First, my background. I am using Filemaker Pro 10 Advanced on Windows Vista and Snow Leopard. I am fairly new to Filemaker in that I have created some simple databases and I have worked with Access in the past.

Now for my issue. I am trying to create a database to include the following: clients, orders and expenses along with a few other things but I have that part figured out. The clients table is linked with the orders table, since every order will have a client. The expenses table is not linked to a client because the expense may occur with or without a client. Therefore, I would like to create a financial summary of both orders (which would be income), along with the expenses. I have figured out the summary fields and have gotten those to work. The problem is in displaying the data on a layout, I cannot get the data from both the orders and expenses tables to show and I believe it is a relationship problem.

Any help would be appreciated.

Dave

[bcooney]

Can you combine order line items and expenses into one table and have a field for type that would allow you to differentiate one from the other? Then your report is back to one table.

[David-66]

Thank you for the reply, I ended up doing something similar. I created a table for all the finance data (expenses, order costs, etc) and just link the amounts into layouts for each purpose. It seems a bit clumsy, but I think once I get used to it, the flow will get better.

David

[David-66]

I had already asked this earlier and received a good response on how to fix it. However, after working a little more with it, I have run into other issues so I am going to try asking again and this time see if I can add more details.

I am trying to create a solution for a photographer that tracks clients, orders, sessions and expenses. The tables for Clients, Orders and Sessions are all related since each client will have an order and easch client will have a session. The expenses however will not relate to always relate to a client, session or order because there could be an expense that is all on its own.

After creating all of this, I want to include a financial summary page that will show the total of the orders, the total of the session fees and the total of the expenses. As you can already see, since the expenses are not related, I cannot incorporate those into any layout with the others.

Here is what I have tried so far, I created a Financial table that stores all of the information related to costs and then created reports using those numbers. This works to a point but sometimes leads to messy reports with data missing from certains rows. (Since one financial record may be for just an expense, the other two fields are empty). I also tried to create a client just for expenses, so that client will always be used for each expense. This did not work out so well either.

Does anyone else have any other suggestions on how to accomplish this, or how to improve on something I have already tried?

I appreciate any help and imput I can get.

Thanks.

Dave

[Lee Smith]

I merged your two threads. You do not need to start a new tread when you have more questions on the same problem.

Lee

[bcooney]

It might look better to create what I call a display field for your report. It is a calc of concatenated data from other fields. You can use a case statement. So the unrelated expense might have in the dsp field, "Expense: " & expenseAmt.

[David-66]

Thank you for the help, I am pretty new to this but I believe I have read somewhere on how to do that. I will give that a shot and see what happens.

David

[David-66]

Ok, so I have decided to stick with the Fincance table and just pull the numbers from there. I found a way to get the results I was trying for by using a Find script that will only show a record that has the Amounts that I am looking for, I used a script that looks for a number in the Expense Amount field. This filters out the records and works pretty good, but leaves me with yet another question. Once the script runs and I go to a new layout, the Found set is still showing, what is the best way to remove the Found Set when switching to another layout? I plan on using a menu system to go from one layout to another, can I script a button to remove the Found set?

Thanks.

Dave