Show Records as Columns in a Layout

[CatLady03]

I've searched for this topic in the forum and nothing came up. It may be a simple answer, but I'm a novice user and can't figure it out.

Here's my problem.

I'm collecting financial information on a monthly basis, where each month has two records. All I want to do is display the 12 month-end records for a given year in separate columns. The data is located in a table called Net Worth Statement. I'm creating a report based on this table called Balance Sheet Summary, which is where I want to display the columns.

I discovered while searching the Knowledge Base that I can create a horizontal portal by selecting only one row per portal and then duplicating the portal across for each column and setting the initial row as 1, then 2 in the next, etc., displaying only one row in each portal. I did this on the Balance Sheet Summary, selecting only the even numbered records to represent the month-end numbers (the odd records are mid-month totals), and the same month still appears in each column. I haven't populated all of the data yet in the Net Worth Statement table (I have two actual records completed), but I have created all of the records with just the relavant date in each record (24 records total), and I'm using that date as my indicator as to whether this is working or not. It's not so far.

I have created a self-join on the Net Worth Statement table for the purposes of the portal and I'm selecting the fields from the Net Worth Statement2 table in the portal. As I stated, I selected the even numbered records for the portals, which gives me the month-end totals I need for each month on the Balance Sheet Summary, which is where the portal is located.

I'm not even getting the date to display properly. I hope I have described this well enough for someone to understand and help me.

Can someone please help me?

Thanks in advance for your time!

[bruceR]

It would be helpful to have more detail on the tables. It isn't apparent why you need a summary table when summary fields can be used within the existing table.

Also - let's be sure not to overlook basic, existing features. In layout mode, layout setup, printing, there is a choice to print in multiple columns. Seems like that should work for you.

[CatLady03]

I'm sorry I wasn't more clear in my post...I was afraid of that.

I'm not trying to build a "table" of the summary fields...I'm trying to build a report from a table that contains summary fields. The Net Worth Statement table contains summary fields for assets and liabilities. I'm designing a report that shows just these summary numbers for each month for a given year.

I did try the printing in columns option, but it doesn't work for this. I have a bunch of labels on the left side for each line item of data that I don’t want duplicated in the columns. Only data goes in the columns to the right of the labels.

On the first screenshot of the layout, the fields are actually inside a portal. I did make sure that all fields were inside the boundaries of the portal. The portal is setup as Initial Row 2, number of rows 1, which should give me just the month-end numbers for January.

The second picture is of Browse mode...nothing displays in Preview mode either. I just don't know what I'm doing wrong.

Thanks for your time in helping me with this. It really has me stumped.

Net_Worth_Statement_Table_-_Redacted.pdf

Edited September 5, 2010 by Guest
I added a PDF of the fields in the Net Worth Statement table and highlighted the fields I use in the report I'm trying to create.

[Vaughan]

I'd say you want a cross-tab report. Look this up in google and on the forums. They are easy to do in Excel but FileMaker Pro natively cannot create them: they can be done but they are a lot of work.

Natively FileMaker's summary reports display results down the page, not across. I'd suggest you get a basic summary report working, then work out how to convert this to a cross-tab report.

[bruceR]

Down/across is a checkbox option.

[bcooney]

What you are attempting is an advanced technique. But, this wonderful post from M. Edoshin should guide you:

M. Edoshin's Cross-tab Made Easy

[CatLady03]

Thank you for directing me to the article, BCooney. I'm kind of glad to hear it's a complicated thing I'm trying to do. I almost prefer that over just missing something easy.

I reviewed the article and I'm afraid I can't figure out how to relate my data to Mikhail's example. My data is already summarized in the table I'm pulling from. I just need to display those fields by month across the page.

Trying to do it in the printing options failed miserably. All I get are my leftmost row labels in each column, whether I go down or across first.

Any other ideas out there...? :qwery:

[comment]

I'm kind of glad to hear it's a complicated thing I'm trying to do.

I am not at all sure that is the case. But your description is not quite clear:

each month has two records. All I want to do is display the 12 month-end records for a given year in separate columns.

How many people will be included in this report? I am looking at your list of fields, and I don't see any indication of who the record belongs to. I also don't see how you can tell if the record is a "month-end record" - unless it's by the date.

[CatLady03]

There are no "people" in this report. I really don't understand your question. I'm the only one who will use the database, if that's what you're asking. These are the financial records of a family I work for. Maybe that answers your question. I just don't know.

I also don't see how you can tell if the record is a "month-end record" - unless it's by the date.

As I stated in my original post, there are only 24 records in the file, two for each month (i.e., for January, there are records for 01/15/10 and 01/31/10). I need to display just the second record for each month (the "even" records, if you will) across the page in columns as shown in my response where I provide the screenshots of the report in both Layout and Browse modes.

I hope this helps. I'm not really sure what you're asking, though...sorry... :P

[comment]

Would something like this work for you?

MonthlyColumns.zip

[CatLady03]

This is exactly what I need and is exactly what I attempted to do with the portals. There is a nuance I'm missing about what you did, though.

I have created the self-join correctly, I have selected the fields correctly in each portal, and I have sorted each portal by date as you have. I have added to the Net Worth Statement the cCondYear, cMonth and gYear fields as you have them setup in your example. I then sorted by cCondYear and Date as you have. I also did a find on cCondYear to select 2010. Everything seems to be correct.

Yet when I go into Browse mode, I only see the first month...all the others are blank. What am I doing wrong? The picture shows you my updated layout based on your sample.

This is not the first time FileMaker has made me crazy!

P.S. The attached file is slightly redacted because the data is confidential.

Balance_Sheet_Summary_-_Redacted.pdf

[comment]

It's hard to tell what the trouble is without seeing your file. This part, however, does not make sense:

I then sorted by cCondYear and Date as you have. I also did a find on cCondYear to select 2010.

Sorting (other than sorting the portals or the underlying relationship) has no effect here. Neither does finding. The portals ALWAYS show the related records, regardless of any find. In my file, all you need to do is enter the year to show into gYear. This will cause the related records to display even if the found set happens to be empty.

[CatLady03]

Well, the find may not be useful, but everything else was as you had it in your sample database, including the sort, whether it was necessary or not. I wanted it to look just like yours.

I'm sorry I can't send you the file...it contains confidential data.

Let me ask you this...does it matter that the fields I'm including in the portals on the Balance Sheet Summary are calculation fields in the Net Worth Statement table? I know that in your sample database, the "value" is clearly just a normal number field. Would this somehow make a difference in the portal?

In my file, all you need to do is enter the year to show into gYear. This will cause the related records to display even if the found set happens to be empty.

I did this, too. I just didn't realize exactly what it did. Although I can see what you did in the Options for the field, I don't necessarily understand what I see. Remember...I'm a novice at this.

It's hard to tell what the trouble is without seeing your file. This part, however, does not make sense:

Quote:I then sorted by cCondYear and Date as you have. I also did a find on cCondYear to select 2010.

You say this part doesn't make sense, and yet that's how your Data table is sorted. I just duplicated what you had is all.

I hope I can get this figured out. It seems like I'm so close to the answer...

Thanks for all your help...it is greatly appreciated.

:thankyou:

[comment]

You say this part doesn't make sense, and yet that's how your Data table is sorted.

No, it isn't. My Data table is unsorted, and it shows records in creation order. But you can sort it any way you like and it won't make any difference to the portals.

I suggest you check that your layout is set to show records from the data table, and the portals show records from the other occurrence of the table.

If you still can't make it work, why don't you attach a copy of your file with fake data.

[Lee Smith]

Dog Girl,

I'm sorry I can't send you the file...it contains confidential data.

You should be working on a copy of your file, not the original. It is not the data that we care about, it is your file structure.

To protect your data, you can do like other have done in the past;

Attach a

1. Clone of the files with no record.

2. Create a mockup of your files covering the area that you are needing help with.

3. Duplicate your file, and delete any sensitive information.

Lee

[CatLady03]

I'm sorry...I saw the sort fields and made an assumption. I didn't notice that it was "unsorted." I just upgraded to V11 from V9 and I'm still getting used to the obvious. My apologies.

I suggest you check that your layout is set to show records from the data table, and the portals show records from the other occurrence of the table.

I have already checked this and the layout points to the Net Worth Statement and the report portals point to Net Worth Statement2. That was one of the items I compared to see that I had it set up just like your sample data.

(Some time passes...)

So, I'm in the middle of creating the "fake" database and I discovered that the relationship between the two Net Worth Statement tables are not the same as your sample database. That's where the problem was. I changed it to match yours and voila...my report is working.

I knew it was something easy that I was missing.

Thank you so much for all your time and effort. I couldn't have gotten this done without your help.

:yourock:

[comment]

Good. BTW, the cMonth field is not being used and can be deleted. And since you are using version 11, you could also do it this way:

MonthlyColumns11.zip

[CatLady03]

I thought my version of FM showed up somewhere. I'm sorry...I should have mentioned it in the beginning then. Actually, I did and then saw it in the profile and deleted the reference. It's just my inexperience with the forum and the time between postings. It has been a couple of years since I posted anything, so I guess I just forgot. I do see now that I can check to show my profile. I guess that's where I messed up.

I appreciate the new approach, but I think I will stick with what's working right now. I just don't want to mess with it. I don't understand the relationship between the two tables, though...gYear to unknown. I thought there had to be a relationship to an actual field. Again...my inexperience.

Thank you so much for all your help!

[comment]

Your version does show. And you don't have to use the portal filtering approach - I posted it only for completeness sake.

The relationship uses the x relational operator, and as such can use any two fields for the match. The relationship will keep on working even if one or both matchfields are deleted (that's what you see on the portal side).

[CatLady03]

I don't know if it matters, but you may as well be speaking Greek to me. I don't understand anything you said...sorry... I should probably buy a book on V11 so I have something to reference.

The bottom line is that my report is working great, so I'm happy and very appreciative. Again...

:thankyou:

[CatLady03]

This is a response to post #364936 made by "comment".

BTW, the cMonth field is not being used and can be deleted.

I think I may see a need for this field, but I need to understand how it works. Here's what I'm trying to do in a different report...please don't try to relate this question to the report described previously in this thread. It relates to a completely different report now.

My new report is Budget vs. Actual and requires a year-to-date (YTD) budget column, as well as an annual budget column. The budget numbers are entered for the year, so clicking the "running" box on a summary of the field isn't working. I'm wondering if this cMonth field that you have defined in your example could help me get to where I need to go. I'm just not sure how to use it without limiting my "annual" totals as well.

I have attached a .pdf file that shows all the fields I have created thus far for this one row. All I need is to get one right and the rest of the rows will fall into place.

I realize this is a monstrous database and I really don't know the limitations regarding number of fields in a table, or if I should be creating separate tables for these different sets of fields or what.

Anyway, if you can tell me whether the cMonth field would help me in this scenario, I would appreciate it.

As always...thanks for your time! B)

Salary_Fields.pdf

[comment]

IIUC, each record represents figures for one month. To produce a YTD report, you only need to find records for the elapsed months of the current year. However, I don't see a field that indicates which month/year the record represents.

[CatLady03]

I only showed you the fields that related to one specific line item. I didn't include any of the extraneous/functional fields. I also have the fields Date, cCondYear (a calculation that finds the month-end date), and gYear (finds data by the year typed into the field on any given report where it is used).

I hope this is what you're looking for.

What does IIUC mean???

Edited September 10, 2010 by Guest

[comment]

I also have the fields Date

So how many dates per year are there?

What does IIUC mean???

"If I understand correctly".

[CatLady03]

The same date situation exists from my first posts...it's just a different report. There are some data that I need a mid-month fix on for specific reports, and others that I require on a monthly basis.

So how many dates per year are there?

The direct answer is 24...2 per month.

[CatLady03]

I believe it figure it out. The cMonth field did the trick. I added it to the report and the data lined up perfectly. I guess just "talking it out" here got me where I needed to be. I just experimented with each thought that was triggered until something worked.

My report is working!

Thanks again for your efforts. Sorry I wasn't able to make myself clear sooner. B)

[comment]

Yes, you could use the cMonth field to find records where cMonth < Month ( Get (CurrentDate) ).

On the other hand, you could also do this without any calculation fields - just by searching the Date field for records where year = Year ( Get (CurrentDate) ) and month < Month ( Get (CurrentDate) ) and day is in the range {28..31}. See here how such request needs to be formulated:

http://www.filemaker.com/11help/html/find_sort.5.8.html#1028153

[bruceR]

Here are a couple more variations. (Try the scripts).

One uses a standard multi-column summary report. The other uses a auto-create relationship to write report records for each year and write values into the repeating fields by month. Year and column totals also provided.

MonthlyColumns11mod.fp7.zip

[CatLady03]

Thanks for this! I'll look into it more closely and see if I can make it work with what I'm doing.

I appreciate the suggestion. B)

[CatLady03]

I reviewed the sample you gave me and I think what I'm going to need is a bit more complicated.

Here's the structure of the report. Each page is a quarter of the year and there are three columns per month labeled Budget, Actual, and $ Over/(Under), so a total of 10 columns across if you include the column of static labels on the left.

The Budget, Actual, and $ Over/(Under) fields are defined already and data has been entered. All I'm looking to do is present the data in a report. I don't know how to get three different months to display in their respective columns on a single page. Ultimately, I need four pages...one quarter per page and then year-end data on the last page along with 4th quarter data. I have attached a sample as it is done in Excel. This is what I'm attempting to duplicate.

I hope there is a relatively simple solution to this but I just don't know.

As always...thanks for your time and efforts to assist.

Test_Budget_Detail_Report.pdf

[bruceR]

You've got plenty of examples to work with; it is now up to you to do the work of applying them. Here is one change to the report-table method that indicates how to create category records.

MonthlyColumns11mod.fp7.zip

[CatLady03]

Although I very much appreciate the information you have passed on, I don't understand the examples you have provided and how they answer my question regarding multiple columns for a single month, with multiple months on a page. None of the examples show this scenario.

Am I supposed to assume from your post that I've been given all the help I'm going to get on this? That's what it sounded like to me and I don't understand. I have explained that I'm a novice at this, so I don't always understand the information I am provided, which requires me to ask more questions.

I guess I didn't realize there's a limit to what one could ask...

[bruceR]

It isn't clear what your expectations are; but you are asking for free help from busy people and it is reasonable to expect you will have to put in substantial effort of your own to learn and apply what you get here. Different people will have differing levels of ability or willingness to participate.

[CatLady03]

You have no idea how much "effort" I have already put forth on this project. It is substantial. I'm merely asking questions about the areas I can't figure out.

I'm sorry if I have somehow stepped on your toes and done something I wasn't supposed to. The obvious way to handle this, however, would be to simply not respond to the post if you don't want to, rather than chastise me for asking because I don't understand.

This is just not what I expected from this forum and I am disappointed at best. B)

[bruceR]

Although I very much appreciate the information you have passed on, I don't understand the examples you have provided and how they answer my question regarding multiple columns for a single month, with multiple months on a page. None of the examples show this scenario.

On the contrary, they show exactly this scenario.

They show how to create grouped records and how to write data to individual columns that can be selected by script.