MLink

Hi Soren, First of all, thanks for the replies! I really appreciate it, and the time you've taken to respond. Addressing some of your points: Expenses and Invoices have more attributes than I established up there - they are not identical and really do need to be separated. What I was trying to show was that for the purposes of this collection of data they both share some fundamental similarities. I'm also afraid I don't see how measuring things by month is daft. This is the way financial systems have to work for the books to be accurate. As Expenses and Invoices have a date attached, this is the date that they occurred and regardless of how many days are in a month or how you measure a month, that month that that date belongs to is where they must be sorted. Unrecognised revenue does exist and is a real concept that needs to be addressed in my solution. At the end of each year it is easy enough to store the unrecognised revenue because a simple calculation on the total for the year will be enough to generate the same result as if calculated individually for each month. The problem is that each month needs to be addressed individually throughout the year. At the end of each month, the accountant needs to be able to view this information to know how to adjust the bank ledgers appropriately. At the moment it is all done using Excel which is horribly clunky : I'll attach two screenshots so you can see what I mean about recognised and unrecognised revenue. The first screenshot, correct.jpg, shows how it should be. You can see that as revenue comes in in March through Invoices, and there are no Expenses, it goes straight into unrecognised revenue, and it carries on through to July, when an expense comes in. This allows us to recognise a portion of revenue, which is almost all that is available, leaving $0.04 unrecognised to the end of the year. The second screenshot, incorrect.jpg, shows where my system collapses. In this screenshot we are carrying over unrecognised revenue from the previous year. As you can see in February, as soon as a large expense comes in, the formula recognised more revenue than is available, and puts us $81.75 into the negative, where it *should* cap out the recognised revenue at $12,453.25 as that is all that is available for the previous month. This later swings back into the positives, then the negatives again. Ideally, unrecognised revenue can *never* be negative as it should only be possible to recognise as much revenue as you have available. These screenshots show the recognised revenue column without the capability to check against the amount of revenue currently available to recognise. As soon as I add that function, the Min() function described above, to make sure recognised revenue caps out when needed, the whole thing collapses. Realistically I think I'm just going to have to bite the bullet and do it as a report. The only problem I see with this is how to have a field which, when viewed as a report, calculates recognised/unrecognised revenue for each of the 12 months. I guess I'll figure out a way!

Hi Vaughan, Thanks for the comment, and the advice. I am pretty much willing to try anything at this point to get it working. The one problem is that it is really desirable to have this information available instantly and dynamically for any project that is viewed. If the users need to print out a report to see this information every time then they will get pretty frustrated. It is not enough to find records for a desired period and then sort them, it would be ideal for the user to be able to simply go to the Finances tab in the layout and have this information presented to them straight away. This would be the way I would really like to do it, but I also completely understand if it turns out it has to be done with reports instead of in a regular layout. I also can't see any reason for Expenses and Invoices to be linked, and I don't really understand how this would help. Can you please explain a bit further? The real problem I have, the only problem that is holding me back, is that the calculation to determine how much revenue can be recognised seems to wig out the whole database. I can only assume this is because I am going at the whole thing the wrong way. Even if I were to make this into a report that was printed out, it would still wig out completely while calculating these amounts. The worst part is I'm not even sure if changing the database model would help with this. Sorry if that didn't help very much, let me know and I'll elaborate more. Thanks again.

Here is a short description of the problem and my model. I'd upload a file but there's too much data and I don't have time to remove stuff that I shouldn't be putting in the public domain. There are three tables that are relevant here. - Invoices, and - Expenses, both of which link to the main table: - Projects A Project can have many Invoices and many Expenses, but each Invoice and Expense only related to one Project. All of what I am going to discuss takes place in the Projects table. There are four groups of fields in the Projects table that I am working with, and there are twelve instances (one for each month) of each field in these groups. So there are 48 fields in total. The first two groups of fields relate to Invoices and Expenses. Each Invoice, and each Expense, has a Date, and an Amount. Using this date, I have set up summary fields that use this Date to collect the total monetary amounts of both Invoices and Expenses for each month in the current calendar year. So, there are 24 fields to play with there. - Expenses for Jan in Current Year - Expenses for Feb in Current Year [..etc..] - Expenses for Nov in Current Year - Expenses for Dec in Current Year - Invoices for Jan in Current Year - Invoices for Feb in Current Year [..etc..] - Invoices for Nov in Current Year - Invoices for Dec in Current Year You get the idea. The third field is Recognised Revenue. Each Invoice generates Revenue for the Project, and Revenue must be Recognised by Expenses. I need to know how much Revenue is recognised in each month. The catch is that you can never recognise more Revenue than you currently have unrecognised. The formula for this is: Min ( (Expenses for any Month / (1 - Expected Profit for the Project)); Amount of Unrecognised Revenue from Previous Month) This means that it will always recognise only as much as is available. If the amount to be recognised is ever greater than the amount available to recognise, it will default to the cap. So we have 12 more fields: - Revenue to Recognise for Jan in Current Year - Revenue to Recognise for Feb in Current Year - ... etc to Dec The fourth and final field is Unrecognised Revenue. Once you have Recognised Revenue, you need to know how much is left. The formula for this is: (Unrecognised Revenue for Previous Month + Revenue for this Month) - Revenue to be Recognised for this Month Which gives us another 12 fields: - Unrecognised Revenue for Jan in Current Year - Unrecognised Revenue for Feb in Current Year - ...etc to Dec This seems simple enough. And it works. Everything EXCEPT the Revenue to Recognise fields. Whenever I ask FileMaker to use the Min function (or any other function, I've rewritten the formula a few times) to calculate the amount of revenue to Recognise, the whole database crashes and wigs out, and I am forced to end the process. I can only assume I am somehow doing a circular reference that is making the whole thing loop on itself until death. Each month's Revenue to Recognise uses the Previous Month's Unrecognised Revenue, and so on, so theoretically the whole calculations should cascade down from January until all the fields have been completed. But instead I get the whole database hanging. I am sure my calculation logic is correct, so I can only assume the error lies with the way I am implementing the solution. As it stands, these 48 fields are all present inside the Projects table, and are live and calculating for every record in that table. I have tried making a new model where there were 12 new tables, one for each month, and inside each of these tables there were 5 fields: Project ID, Invoices for this Month, Expenses for this Month, Revenue to Recognise for this Month, and Unrecognised Revenue for this Month. This STILL collapsed on me as soon as I added the logic for the Min function to recognise the correct amount of revenue. I am utterly and completely stumped, after throwing myself at this for the last two weeks. There must be a way to handle this, the calculations involved are not that taxing. Please, if any of you took the time to read this, let me know if I'm going about this completely* the wrong way. All thoughts and comments deeply appreciated.

Well, I'm only going with this "spreadsheetish" approach because that's all I know how to do! See earlier where I wax lyrical about how I'm entirely self-taught. But your advice is well noted and I thank you. How would I go about doing it with records? I would either need a) To somehow create 12 entries in the month breakdown table, each with the same project id but for a different month, every time a new project was made, or; Have 12 different monthly breakdown tables, and create a new entry in each one with the correct project id, every time a new project was created. I wonder if there is a script to do this? I will have a play around but would appreciate any suggestions.

Hi DJ, thanks for the reply! Sorry, I would have said something sooner but I didn't get an email notification about it. Odd. It is all locally hosted though, so there's no net connection slowing it down. Anyway I've made a few changes to the way it works to hack out a lot of dead wood, and I'm down to less than 50 fields. The reason there are so many is because it does a month by month breakdown. So for each month (12 months in total) there must be a field for: - Revenue for that month (a summary field collated from another table) - Expenses for that month (also a summary field collated from another table) - Recognised Revenue for that month (this is the field that is causing problems, see below) - Unrecognised Revenue for that month (the difference between revenue recognised to that point, and total revenue to that point) The problem with Recognised Revenue is that it needs to be capped at a maximum equal to the unrecognised revenue for the previous month. So for this, I've been using a Min() function, specifically Revenue Recognised for any given month = Min ( (Potential amount to recognise based on revenue and expected profit); (Unrecognised revenue for previous month)). However as soon as I add this Min checking, the whole thing wigs out. I can only assume this is because it is somehow doing a circular calculation with the Unrecognised Revenue amounts for each month. It's a very strange situation. I'll post up a more detailed explanation soon, but I have a few more possibilities to try before screeching at the moon.

Hi everyone, I've been working on this database for a few months (never used FM before, all self-taught) and now I'm putting in all the functionality to provide a month-by-month breakdown of the financial side of things. To do this I am using summary fields that read through entries in two other tables (Invoices and Expenses) to determine how much of each relates to each month. I then have set up a whole series of stacking calculations which determine how much of each was present at each month, taking into account the previous months. And so on and so forth. All in all I've created about 70 calculation fields to handle all of those. There's probably an easier way, I'm sure. It all works pretty fine, except when I try to add the calculation fields for recognised revenue. I can add a few of them, but when I put in the formulas for every single month, the whole database lags out. So badly in fact, that I have to end the process in the Task Manager. I can post it up if I clean it up enough first, but the basic premise I want to know is: is there a point at which too many calculation fields will kill the database, and have I reached it? Do any of you have experience with this sort of thing, and how did you solve it? Even hints as to how to debug it would be greatly appreciated, as would all replies. Thanks in advance.

Hi everyone, Before I ask this please bear in mind I am so far completely self-taught in FM and so there is a very good chance this is a very stupid question. I have a table called Projects. I have a related table with invoices in it. I need to make a total of all the invoices from a project, in each of the 12 months. So one for February, one for March, etc. However I can't figure out an easy way to do this. It's just a matter of something that goes through the table of invoices, checks the month, and if it's "1" adds it into the "Total Invoices for Jan" field, for example. I'm sure there must be an incredibly easy way to do this, but the only way I can think of is filtering all the invoice data by creating 12 new instances of the Invoices table on the relationship graph and then matching them. Can anybody help me out, or at least point me in the direction of the fundamental resources I am missing? Thanks guys and gals, all replies welcome.

Søren! You're a lifesaver. I will try and apply this solution and see how it goes. Thankyou so very much.

Hello all. I have a table called EXPENSES. Each entry in this table has a Project Code, a date, and an amount. I'm really stuck. Is there a way for each entry in this table to have access to a summary of the amounts for all the other entries in that table that: a) Have the same Project Code, and; : Have a date BEFORE the date of that entry in the table. In other words each expense should be able to see the total of all the expenses relating to that project code that have come before it. Effectively, each expense has a running total of previous expenses against that project code. Is this possible? If so, how does one acheive it? If I could somehow use a while/for loop I'm sure I could do it, but I'm hoping there's a function to acheive my aim. All responses appreciated. Cheers.

Hmm, thanks for that link Søren - I understand a bit better now and have got it working though I am still having issues with the value lists. Cheers. EDIT: Actually, I'm not having that problem anymore. Awesome! Thanks all.

Huh. I just played around with it then by connecting the EXPENSES table directly to the PROJECTS table, and connecting the ALLOCATIONS table to the cloned PROJECTS table. Now it finds all the Expenses relating to a Project regardless of if they're allocated or not, but it only finds Allocations that related to a Project if they have an Expense against them... Why is it doing this? Why is the table that links to the cloned version of the PROJECTS table in the graph somehow crippled? And why can't I do a circular relationship like in every other database system. Confusing to say the least.

Hello all - had a look through the last few pages of this subsection and couldn't find anybody else with a similar problem. This relates somewhat to this thread. Let us imagine that we have a table called PROJECTS, uniquely identified by the Project Code. There are two other tables - EXPENSES and ALLOCATIONS. An Allocation represents a collection of similar expenses against a Project. eg, $400 is allocated to Fuel Costs. All Allocations MUST have a Project Code and a unique name. An Expense is an expense against a Project. This may relate to a previous allocation or it may not. In other words, if a new Expense was created and tagged as "Fuel Costs" it would go against that Allocation. If it was not tagged with an Allocation it would just go against the Project as normal. All Expenses MUST have a Project Code and a date and amount and are uniquely identified by an Expense Number. So basically: An Allocation is a sum of related Expenses, but; An Expense is not always related to an Allocation. So to get around this many-to-many relationship I made a normalising table called ALLOCATEDEXPENSES. This matches an Expense Number, Allocation Name and Project Code together as a way of tracking what Expenses are part of Allocations. I'm using two Portals on the PROJECTS form to show Allocations and Expenses against that Project. The Allocations show up fine. I can see every Allocation which matches that Project Code and I can see that the Allocations are gathering information from the ALLOCATEDEXPENSES table and using that to check off expenses against that allocation. The problem is that in the Expenses portal it is only showing Expenses that have been flagged in the ALLOCATEDEXPENSES table as being part of an allocation. Unallocated Expenses are not showing up even though they have been clearly given a Project Code and there is a direct relationship between the PROJECTS table and the EXPENSES table. Somewhere behind the scenes it is obviously making a strange AND'ing query and only returning expenses that match the Project Code AND are in the Allocated Expenses table. But I don't want that, I want to show all Expenses related to a Project, regardless of if its been allocated or not. Can anyone help me with this? I'm sure it's as simple as ticking a box or two on the relationship graph. I just don't know of any other examples of this to reference. I've attached the .jpg relationship diagram and the database as a .zip archive. Thanks for reading that long description everyone, all comments and thoughts are appreciated. Project_Database_Prototype_Public.zip

As long as that table and any other table you're relating to it both share the same field - ie Postal Code - and you relate the two Postal Code fields together you should be able to set the fields that you want to autocomplete to be Lookup fields that will automatically grab the County and City that match that Postal Code. I don't know if you could make it more flexible than that though - you could definitely make it search by one key value ie Postal Code, but if you wanted to be able to have the City field be both a) automatically completed if the user enters a Postal Code, and; if the user puts in a City then the County field will automatically get filled out; You're looking at more tables and such, and I'm not knowledgeable enough to help you there I'm afraid. I'd suggest sticking to just the user putting in their Postal Code and the form filling in the rest - much easier.

Thanks comment - worked a charm.

Thanks to the both of you. I think the solution is a combination of both suggestions. I have created a new Allocations table (analagous to Debts), which is linked to the Projects table by the Project Code. I've created a field called Allocations List. This is a Calculated Field which uses the List function to make a list of all Allocations matching that Project Code. I then created a field called Allocations. This is supposed to be validated against a value list made up of the values from the Allocations List field. But it keeps giving me an error message saying "This value list will not work because the field 'Allocations List' cannot be indexed. Proceed anyway?". If I click OK it gives me an empty value list, despite the fact that I can see in the Allocations List field that it is collecting all the related allocations and concatenating them with carriage returns between them. Why does this approach not work and what can I do to turn this collected Allocations List into a value list? Thanks for all your help.

Sorry if this has been asked before.. I've been through the FM knowledge base and looked at Conditional Value Lists and such and couldn't figure out a way to do this. Would dearly appreciate any help. I'm using FileMaker Pro Advanced 8.5 on Windows XP. I have one table called Projects. Its unique identifier is the Project Code. Inside that table are eight different fields in which the user can enter text. These are used to label different debts relating to that Project. I have another table called Disbursements. It also has a field called Project Code which is linked to the Projects table. I want each Disbursement to relate to a specific debt on that project, and I want to have a drop-down list called "Narrative" which allows users to pick from a list of values that are taken from those eight different labels in the Projects table. I can see a way to include values from a single field in another table in a drop-down list - is there a way to include values from multiple fields in a single record? All answers appreciated. Thanks very much.