Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About MLink

  • Rank
  1. 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 atta
  2. 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 th
  3. 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,
  4. 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
  5. 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 col
  6. 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 crea
  7. 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.
  8. Søren! You're a lifesaver. I will try and apply this solution and see how it goes. Thankyou so very much.
  9. 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 projec
  10. 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.
  11. 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 t
  12. 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
  13. 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;
  14. 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
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.