Reporting one to many & one to none

[emjay]

Greeting, perhaps I can get some help.

I am trying to create a Budget/Chart of accounts solution.

I have 2 tables -1, Chart of Account which includes a unique 4 number account code (eg 3012) and a budget amount

The second table is Line Items which contains an account code and an amount. The 2 files are linked via the 4 number account code

I have a layout where I can go to a Chart of Account record and enter line items via a portal. So far so good.

The problem I'm having is this - I have many more records in my Chart of Accounts than I do line items. I need to create a report that shows every record in the Chart of Accounts and all the detail, even if there isn't any detail. I'm close - I can get a summary of the line items but I can't seem to get the individual line items to show up.

What am I missing?

Thanks for any help you can give me.

[comment]

Filemaker does not support this kind of functionality (left outer join) natively, so you'll need to use a workaround - e.g. print the items in a portal set to slide up, or use a calculation field in the Accounts table to list the items.

[emjay]

Thank you for your reply. I understand filemaker doesn't support this natively but I don't understand the solutions you've offered. Could you either give me an example or refer me to some guide.

thanks for taking the time to respond.

[David McQueen]

Thank you for your reply. I understand filemaker doesn't support this natively but I don't understand the solutions you've offered. Could you either give me an example or refer me to some guide.

thanks for taking the time to respond.

Because the relationship is not supported, you will have to do the report from the Chart Of Accounts table, one record per account.

The report will have the Chart of Accounts information at the top in a header . Below that in the body will be a portal to the line items for that Chart of Accounts showing the line items info that you want. Make that portal have as many lines as you would ever expect there to be line items for any account. So it could stretch pages.

You have the provision in printing a report (and viewing in preview mode) of having objects slide up if they are blank and of having the enclosing part size reduced if there is no data. These are attributes set to an object or set of objects in layout mode while developing. It is automatic in operation after it is done. In FMP11 these functions are found in the Inspector. In previous versions they are under Format -> Set Sliding/Printing.

The idea is that in a multipage report, the Account info shows in the header for that particular account and the line item data shows below. Should the data cross many pages, the header will always show which account it belongs to.

HTH

Dave

[emjay]

Thank you very much - this was very helpful.