Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

This topic is 7181 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
Posted

Hi Eveyone,

It's my first time posting here, hopefully one of you kind people will be able to help me. I'm trying to set up two related databases. The first has contact details, the second has purchases. I would like to display the number of purchases that a contact has made, defined by type. The products are selected from a value list. In other words, if the choicees are bananas, apples, or pears, then I would like to display in the contacts database, how many of each item each contact has purchases in the past. I would like this field to be a running total. I have tried using a calculation field but I can't work out which formula to use.

Can anyone help?

Thanks in advance!

Greg

Posted

The calculation function to use is Count() To count specific types of fruit, you need a relationship that specifies which type of fruit you want to count. So if you want to simultaneously count 5 types of fruit, you need a relationship for each one. I've attached an example that uses a global field in the contacts file to choose which fruit to count.

If you wanted a more detailed subsummary report of numbers of different fruits for all contacts, you would build that report in the fruits file, using a relationship back to the contacts file to display the contact names etc.

Dana

contact-fruit.zip

Posted

Let me be so presumptuous as to offer a little advice:

It appears you may be starting off on the wrong foot. When designing a DB, it helps to have an "object oriented" approach. FM is not an object oriented development system, but the advice is still sound.

Think of your DB as a group of real-world objects.

Your question lists two objects; clients and purchases. But you actually have four:clients, purchases, items purchased(assuming clients can purchase multiple items at once) and items to purchase(bananas, apples and pears).

You need a separate table for each object; one for clients, one for purchases, one for items in a purchase and one for items.

In more traditional terms, these tables would be:

clients

invoices

invoiceLineItems

inventory

This way, when you change bananas at 12 cents each to plantains at 43 cents each, all your old purchase records won't suddenly show erroneous information.

It will also make your "number of items purchased field" easy. Create a value list that references the lineItems table, not the inventory table, because you only want to see items that have been purchased, not ones that "could be" purchased. Create a global field that has a popup list with that value list. Create a global calc field in your clients table that concatenates the client ID field and the contents of the global field (the one with the popup list) eg.,"1234Banana". Add another (not global) calc field (stored and indexed) in the lineItems table that concatenates the foreign key (same as client ID from clients) from the client file with the item purchased (banana, etc.), again, "1234Banana". Create a relationship between the two tables with these fields, then a calc field in the client file that is sum(relationship::quantityPurchased). Done.

Posted

Addendum:

If you are using FMP's built-in navigation features to change records, then don't make the fields in the client table global. Otherwise, whenever you change records, you'll see the previous client's purchase history instead of the client you're currently viewing.

Posted

I didn't want to add any more complexity for Greg at first by adding products/line items/invoices right away. I was just showing him how to use a calculated key (now unnecessary in FM7) to get specific information from a related file. Also, the use of a global in this case was only for a portal filter. One could just as easily store the portal filter in a normal field, but remembering unique portal filtering info for each contact probably isn't necessary.

Dana

Posted

is this 2 seperate files?

If I have 2 files, one file with projects, that get numbers, and this number I want to show up in my other file, that is an entry per person of hours used on the project, i want to be able to choose the project number for adding hours on that project.

I tried to relate and link the fields, but it wont work. Any suggested books or definition to read?

Is a global field the solution ? (use FM7)

This topic is 7181 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.