"Spreadsheet" within a record??

Posted on 2005-05-08
Medium Priority
Last Modified: 2010-04-27
I am not sure of the proper terminology, but here goes:

I am using FMP 7 in Windows to track what I do when I visit a client. It is simple enough to create a text field that tracks all the items I did during a visit, but within that same layout I would like to have a way to track purchases that I made for that visit....and make sure it stays tied to that record of that visit.

One way I could think of doing it would be to create a two column "spreadsheet" with a field. Column 1 would be the name of the purchased item and column 2 would be the cost. The total of column two would then need to calulate to a sum of all purchased items.

I bet I could do this by creating a relationship to another table using serial numbers, etc, but I am hoping there is something simpler.

Question by:Issaquah
  • 4
  • 2
LVL 28

Assisted Solution

lesouef earned 400 total points
ID: 13961465
well, it depends!
1, the best is to use a table for visits and another one for, items/prices, especially, if you use a price list. It will fit any evolution to a more complex stuff.
Simpler ways:
use multivalued fields: with 10 instances it will allow to store up to 10 items per visit, then create another field for prices, put it side by side, it wil simulate yr excel grid in a way.

LVL 19

Expert Comment

ID: 13963081
The simplest approach would be to use a simple text file for each customer, but if you do that, then you're wasting most
of the value of FileMaker, and could just as easily use a word processor to store your information.

The best approach is to use multiple tables and build relationships. It may seem complicated at first, but
it's really not that hard, and the flexibility will be much greater. Also, some things just don't work well
when you combine values in a single field. For example, if you build a list of values in a single field,
how would you sort the elements? Finding and editing is also going to be more trouble.


Author Comment

ID: 13968278
I tried building a new table/releaitonship. But could not input into the fields. Here's what I did.

I have a table called "Project" which lists the client name, date of visit, what I did, etc
I created a new table called "Purchases"The new table has three fields: ClientID, Item, Cost
I drew a relationship between the ClientID field in the Project and Purchases tables. Then I placed the Item and Cost fields from the Pruchases table into the layout that contains lots of fields from the Project table.

When I make a new record I can enter data into the Project fields, but not into the Purchases field. Why?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 19

Accepted Solution

billmercer earned 1600 total points
ID: 13968811
When you define the relationship, there's a check box labeled "Allow creation of records in this table by this relationship"
You have to check this box in order to be able to create records in the related table.

Also, instead of just adding a single field to your layout, you really should create a portal. Adding a single field only allows you to display one purchase record per project. With a portal, you can add multiple purchases to a single project.
LVL 19

Assisted Solution

billmercer earned 1600 total points
ID: 13969058
Here's a simple example file showing how you can use a portal this way.

Author Comment

ID: 13986041

Your answer was right on!!! Thanks so much. I do have other questions...do you consult?
LVL 19

Expert Comment

ID: 13987496
We're really not supposed to solicit business through EE messages, and they are pretty strict on the rules, so I'm not allowed to post my email address here. However you can read my EE profile if you want to get in touch with me.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question