Finding products sold in database

[fundidor]

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

etc..

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?

Thanks!

Bernardo H

[Jim McKee]

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!

[fundidor]

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...

Thanks!

[BobWeaver]

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]

Sort[restore]