What are your 'best practice' recommendations for this?

In the past, I have one table for entering income and expense amounts, with sub-totals by category (ie, each record has fields for id, date, category, amount). The expenses were entered as negatives.

I'm in the process of building a new database, with a similar situation (income types, less various adjustments = net income) and I'm second-guessing how it should be structured.

Do you recommend:

a. one table with income and adjustments (as I've done before)

b. one table with separate fields for income and adjustments (I don't think so)

c. one table for income and another table for adjustments

d. other?

I feel as if it should be two tables, but I'm having trouble wrapping my head around it that way. Because I think of both tables as being the 'body' or detail of a report, and can't figure out how they'd co-habitat.

Thank you -

Marion

There are great inspiration to get in in this:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

...so a)

--sd

It just so happens I'm in the middle of developing a solution that had the same problem - but in my instance there are around 8 tables to consider...

Basically my client runs a business that he operates as multiple departments - these are the 8 tables. However, for the purposes of reporting, particularly quotations, all the data will need to exist in the same table. Reason? When FM reports data and you use the BODY layout part then that data must come from the TABLE OCCURRENCE that the layout is based upon.

My solution was to create the fields necessary for each department into a single TABLE, but then I used different TABLE OCCURRENCES to represent the departments.

This is, in effect, a series of SELF JOIN relationships, and I admit it is not 'normalised' as it would be with separate tables, but I had to achieve the goal of the reporting requirements.

.... all the data will need to exist in the same table. Reason? When FM reports data and you use the BODY layout part then that data must come from the TABLE OCCURRENCE that the layout is based upon.

That's exactly what I thought.... But my husband, who works with Approach, says that I should be able to join the tables (let's say by date) and have them all appear in the body of my report.

I don't think it's possible that way - for FM or any db application - because you'll only get the 1st record from each table.....

I think I will pursue one table for the records, and see if I can enter all the data as positive numbers, but create formulas to handle the subtracting of sub-totals for specific categories....

Thank you -

Try to read this thread with (yikes) all the brances and nestings:

http://fmforums.com/forum/showtopic.php?tid/196508/post/296095/#296095

--sd

If (as it seems) all transaction types require the same fields - what possible advantage would be gained by keeping them in separate tables?

I agree - but I'm trying to reconcile with how I think I should set up my database and how my husband thinks I should set up my database!

That seems to be more of a relationship issue...

So it looks like there are no advantages, and there is at least one big disadvantage. Tough choice. BTW, in an SQL database you can do a UNION join, which effectively produces a combined report from two tables. However, that doesn't mean that a database that splits transactions of the same "account" into separate tables by types is correctly designed. The concept of 'entity' remains the same no matter which RDBMS you use.

@IdealData

Help has very little to say about Table Occurrences....

I interpreted it to mean that I'm placing the same detail table on my relationship window twice. But, when I tried to do that, the linking field is no longer available....

Can you please elaborate, or post a mini-example of how to do this for me to mimic?

Thank you -

Marion

Help has very little to say about Table Occurrences....

Perhaps, but this does:

http://www.fmp.it/download/files/FM7_key_concepts.pdf

--sd

Ok - the article about Table Occurrences was helpful, thank you. If I understand correctly, it's basically an Alias table, so that you can use the data twice. But I'm not sure how that's helpful for me - or perhaps, it just isn't the right solution.

Let's say I have a table of records:

id

category (values of: Advertising, labor, rent, etc)

amount

I would like a list report, with advertising in one column, labor in another column... and perhaps I don't even want rent to show.

I know I can export to Excel and have it via a pivot.

I know I can create empty records, and create a faux pivot in Filemaker

Is there a way to achieve this with Table Occurrences? Or some other technique?

Thank you -

Marion

EDIT: In Excel, I have access to a SUMIF Formula which adds the cells specified by a given criteria. Does Filemaker have an equivalent?

If so, I could place the field on my list layout multiple times... one time specifying the sum of advertising, and then the sum of labor.

and place the fields in the same horizontal row of my layout.

Edited May 4, 200916 yr by Guest

I have access to a SUMIF Formula which adds the cells specified by a given criteria. Does Filemaker have an equivalent?

You would be hard stressed to find a database tool which doesn't provide a subsummary function - and neither does filemaker fail in that respect:

...but it carries evidence that my previous linking didn't make much sense. Edoshins Crosstab reporting, could be changed to usher figures into the appropriate columns. A simple case( statement can turn account number/description into a repetitionnumber pretty simple ... however can it pay off to make this calc as snappy as possible:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000087

--sd

Hello -

This is semi-related to the above thread, but expands the original question now that that has been resolved in my mind.

I am re-building my database for tracking information related to performances (ie, events).

As described earlier in the thread, some of the tidbits of info have date and monetary information, such as:

- EventID, date, time, amount, category

- an eg would be: Event03, May 5, 2009, 2p, 5000, Performances

Some of the tidbits of info is focused on date and a comment:

- Event ID, Date, time, category, comment

- eg, Event03, May 3, 2009, 1pm, Production, 1st rehearsal

- eg, Event03, May 5, 200, 2pm, Performances, 1st public performance

If I keep my non-money tidbits separate from my money-tidbits, then I get into trouble when I'm trying to make body-sections in reports. And I wind up with double data entry (ugh) for something like the May 5th performance, which needs to be recorded in both tables.

I'm tempted to make one big tidbit table, like this: EventID, date, time, amount, category, comment

but some records will not have an amount, and some records will not have a comment.

Is that sloppy database design?

What do you think or advise?

I omitted other fields for simplicity of conversation (such as currency, exchange rate, amount in US$, etc).

Thank you -

Marion

Edited May 6, 200916 yr by Guest
cleanup