Question on creating rows and columns in FM7

[voltrate]

Hi,

I am a newbie to this forum and FM. I am trying to create a db with 3 columns of fields, namely, 'Date Received', 'Amount' and 'Balance amt'. With these 3 columns, user will enter the info with specific to these 3 columns everytime there is a receipt. The 'balance amt' will always be updated based on previous balance and new amount received.

Can anyone help to explain how to create such a layout. I am looking at unlimited rows. Thanks.

[aaa]

What is the Balance_amt? If this is summary of all amount before Date_Received, you can use Summary field with "runing total" option

Or you can create self_relation AAA by Date_Received<=Date_Received then Sum(AAA::Amount).

If your Balance_amt has other calculation you must to tell about it.

[voltrate]

Thanks. Each new Balance amt is always derived when deducted with 'Amount' received and previous 'balance amt'. I am using repetition for the field.

[aaa]

Can you post any example?

[voltrate]

Here's the eg.

Total Owed - $1000.00

Date Received Amt Received Balance

1-5-2005 100.00 900

5-5-2005 100.00 800

10-5-2005 100.00 700

I am using repetition for the 3 fields above but is stuck on how to formulate the balance field. Thanks for your great help in advance.

eg.txt

[RalphL]

It looks like you are just getting started. The first thing you must do is forget that there is such a thing as repeating fields. Learn how to design a relational database. Take full advanatage of the power of FMP7.

[voltrate]

Supposing I am to build a db based on the 3 fields above + the customer info. How should I design the relation? Thanks.

[RalphL]

A customer table with CusomerID an auto entered serial number and fields for name, address, city, state, postal code, etc.

A payment table with Payment ID an auto entered serial number, CustomerID, Invoice Number, Date Paid, Amount Paid. You may need a Prior Balance and Balance. Balance = Prior Balance - Amount Paid. Prior Balance is a little tricky. It is look up from the last payment. Not the previous record. I haven't done this for a while, but I may have an old sample file.

Troi has a Number plug-in which may work in FMP7 that would display a running balance in a portal. They are phasing this plug-in out.

[comment]

I am a bit surprised by this, but it seems like placing a summary field in a portal will work - see attached.

runningBalance.fp7.zip

[RalphL]

It seems to work, I added some more related records and it worked. I then added a record for a date that had passed and set the portal to sort by date. Worked again. The number you see for the balance in the portal is different from the number you see for the balance in the related table.

Does this work in pre 7 versions?

[comment]

In doesn't in FMP 4. I made the test with a single file, using a global CustomerID for the portal. I don't think creating a separate file for Customers would matter.

[aaa]

I am a bit surprised by this, but it seems like placing a summary field in a portal will work - see attached.

Bravo comment! I knew about "running total" in one table without portal. The think that you show is very usable when the portal is sorted by date. At this case it works great. See attacment and add in portal any record with last date.

runningBalance.zip

[voltrate]

Thank you guys....appreciate your contributions. It really helps.