Jump to content

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

Recommended Posts

  • Newbies
Posted

I have a counting database. There are 3 datafiles. Articles, positions and bills. I want to create a field in the article file which shows the sum of an item per year. So not only for the current year. It should show the total of each item sold for 2004 in one field, for 2005 in another field etc. I am grateful for any idea.

Posted

Assuming you've already got a year field in your table, the simplest way to do this is to use a summary field, and generate a report sorted by year. If you don't already have one, you can create a Year field by using a stored calculated field or a field that auto-enters the year using the Year() function.

If your goal is to display the totals on screen during data entry, one way to do this is to make a field holding the year in the items file, and a global year field in your article table, and create a relationship from the global year field to the items year field. Then create a calculated field that uses the Count() function to get a count of items through that relationship. By entering different years in the global field, you'll display the totals for various years. You could create a series of such fields and relationships to display sums or counts for multiple years at one time, or you could use a script to change the year and do the calculations for you and display the results.

There are better ways to achieve your goal, but this is probably the simplest to implement.

Posted

  Quote
Assuming you've already got a year field in your table, the simplest way to do this is to use a summary field, and generate a report sorted by year. If you don't already have one, you can create a Year field by using a stored calculated field or a field that auto-enters the year using the Year() function.

Is this the most convenient way to do it - Isn't it waste of storage, to make an autoenter calc depending on the invoice/billing date?? ...when relations can be stacked to show everything "more than" as well as "less than" ...and even perhaps excluding itemID's equal to NN written in third line of the relations def.

With previous versions didn't we have much choise when trying to define a range via the key from parentside, except SmartRanges or a plugin ...but today isn't there much point in fledging the related with yet another field.

--sd

  • Newbies
Posted

Hi Barbecue,

Thank you for your instant help. Yet I could not get through. You said:

"If your goal is to display the totals on screen during data entry, one way to do this is to make a field holding the year in the items file,"

with items file you mean the article file?

I created a number field with the option -> auto enter -> calculated value-> year(bill:billingdate)

I created a global number field in the article file and typed in the number 2004 to give it a try

I created a relationship from the global year field to the billingdate year field. Then create a calculated field that uses the Count() function to get a count of items through that relationship.

The calculated field shows the total of all items and not the total of each item sold. I guess something is missing. Any idea?

Posted

Yes, by items I meant Articles. I need to get more sleep.

If I understand you correctly, for each type of item you want to show the total count of that item only that was sold.

To do this, you'll need to modify your auto enter field. I assume each type of item has some sort of item code or catalog number. Change the auto enter number into a text field, and enter the calculation

Year(bill:billingdate) & "-" & Item Number.

Change the global year field from number to text, and enter the year, a "-", and the item ID. The count should now show only the total of that item.

You can make a calculated field that adds the "-" and Item Number onto the end of the year, and create the relationship between this new calculated field and the auto enter calculation instead, which will let you enter just the year and display the count for each item as you move through the list.

  • Newbies
Posted

Everything sounds brilliant. You understood correctly my problem. Yet I still did not manage to get it work.

You are correct. I have an auto item number mixed with letters.

I changed the auto enter number into a text field and entered the calculation

Year(bill:billingdate) & "-" & Item Number

Do I have to convert the "Year(billingdate)" into text with the calculation "number to text" at the bill file? Or should be the change to a text field do this automatically?

I changed the global year field from number to text,

but I could not find out where to enter the year, a"-", and the item ID.

Silly question: Can't be all kept as number fields? Why the change into text field?

BTW: What a cute grandchild you have. Thank you for this sweat picture.

Posted

Changing to a text field should take care of the conversion for you.

If you manually enter the year, a "-" and the item ID in the global field, it should work. Try that, and when you have it working, then you can create a separate global field to contain the year alone, and change the current global field into a calculation that combines the new global year with the "-" and the item ID.

For example, suppose you sold a widget, with item number 786, in 2003. In your articles table, you'll have the auto-enter calculation 2003-786

In your global text field, if you type in 2003-786, you should then see the count of widgets for that year. You can verify that your count is correct by doing a find in the articles table based on the item number and a date range, and make sure you get the same result. After you've got the relationship working, create a new global year field, and change the current global text field into a calculation field like this:

TheNewYearField & "-" & TheItemIDValue

Now if you just enter the year in your new year field, the calculation will automatically add on the -ItemID part, and your summary function should display the correct count.

The reason I suggested converting to text is because you need to concatenate the two numbers into one longer string, and that's easier to do as text than as a number field.

The reason I suggested putting a "-" in between the two values is to make it easier to keep the two separate. Also, in this case it's not an issue, but sometimes when you combine two numbers like this, you could get an ambigous result.

For example, if you have two values, 4120 and 120, and you combined the year with the item number, you'd get 2004120. But does 2004120 really mean 2004 and 120? Or could it be 200 and 4120? Without the "-" there's no way to be sure. I realize this is a silly example, because we didn't have FileMaker in the year 200, but there are real situations where similar kinds of concatenations can cause problems.

It's a good idea to get in the habit of using a distinction when creating a concatenated value like this, especially if the contents of one of the fields could vary in length or type of characters.

  • Newbies
Posted

Hi

Thank you for this wonderful explanation.

Yet still something must be missing. After trying a lot of different possibilities I am coming now back to you with the next problem:

I created a text field and enter the calculation

Year(bill:billingdate) & "-" & Item Number

I change the global year field from number to text, and enter the year, a "-", and the item ID the way you have asked me to do.

I tried to create a relationship from the global year field to the text field but it says:

Posted

You can't create a relationship from the text field to the global field, but you can create one from the global to the text field.

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