Jump to content

Finding products sold in database


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

Recommended Posts

Hi folks!

This is my first post!

I have a database I use to print invoices and controling my sales.

In the repeating field where I describe the products I have a total of 17 repetitions.

For example:

10 apples

20 bananas

30 pears


I want to build a script to figure out how many bananas, pears, apples I have sold in the current month.

Filtering the sales in the month is quite easy. But my dificulty is to search for the specific item. If I search for "banana", I get the total records that contain banana, including all the pears and apples sold in the same record.

How can I filter an specific item inside a repeating field?


Bernardo H

Link to comment
Share on other sites

Hi Bernardo ...

Welcome to the Forums!

There are many threads here dealing with the issue of repeating fields. Some stress why repeating fields shouldn't be used in your type of situation and recommend creating Line Items and Payments files related to your Invoice records via the invoiceID. Others contain clever work-arounds for some of the problems inherent in repeating fields, including calculations.

Click on the "Search" link at the top of any page here, enter the word "repeating", check the "In Subject and Body" option, and highlight the following topic areas:

Define Fields

Finding & Searching

Importing & Exporting

You'll definitely get some ideas about how to either work with your current file setup, or alter your setup to include separate Line Items and Payments files.

There is also some good information available in the FileMaker help system. If you search for "export repeating", it clearly explains how to move your data from repeating fields into individual records (one for each repetition) in a Line Items type file.

Good luck!

Link to comment
Share on other sites

Hi Jim!

Thanks for the reply.

I have found a simple, not so straight, way of doing it.

I export my data base, and import it using a clone of the app..

Then I have an option of spliting the repeating field into pieces.. Then I wrote a script to find what I want, place it on a text a file outside FMaker.

I import the text file to a new Fmaker application where I can sort the values the way I want to display.

I know you guys, script geniuses, would find a more straight forward way of doing it. But I still have a lot to learn. One step a day.

I learned a lot from you today...


Link to comment
Share on other sites

Not really. As long as you use repeating fields, that's as easy as it gets. This is the primary disadvantage of repeating fields. You can't search, sort or summarize their data.

If you convert to a related structure, replacing the repeating fields with a portal to a line items file, you will gain a tremendous amount of functionality, and you could generate your summary report with a simple summary layout in the line items file and a simple sort.

I.E., a two line script:

Go to Layout[summarylayout]


Link to comment
Share on other sites

This topic is 6552 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.