Repeating Fields

[SimonB]

I am new to Filemaker. I have the job of changing an existing file that has a repeating field in it (6 repeating unique payments). Which I have to show calculations for commissions with things like (Should Be Sold Price : / Total Lot Price * $ Value Shipped) for several sales people in a repeating fashion. I currently only seem to get 00.00 on entering new payments! I tried get repeatition but this only seems to work for one. How do you set up repeating calculations....perplexed!

[Chuck]

My first advise is that if your job is to change the file, one of the things you might consider changing is to replace the repeating fields with relationships.

My second advice is to give a better idea of exactly what you are trying to do. You mention that you have a repeating field with six repetitions. If your target field (the calculation field) is also repeating, then you should be able to get six results in the target based on information in the original repeating field.

Chuck

[SimonB]

I have been asked to leave the original alone.

I thought this should work also...

payments field (upto 6 possible entries) is first calculated for a dollars available field (upto 6 possible amounts)which is then used to calculate commissions, which are split up for 3 salespersons with unique commissions. It looks like a spread sheet and in fact is intended too. It is essentially a balance sheet for commissions.

However when I enter new payments in the repeat fields which should in turn give me results in the repeat calculation fields. I get 00.00 across the board...I have noticed that if I remove all calculations and replace with something like 1*1*2 that I do get results...so what am I doing wrong?

[Chuck]

I apologize for asking for more clarification, but can you give specifics as to the definitions of the fields? For instance, you have a field called Payments (?) that is a calculation field with 6 repetitions? And a field called Dollars_Avail that is a number field with 6 reps? And a field called Commissions? Does it have reps? What is the calculation? What should it show?

The more specific information I have the better I can help you.

Chuck

[SimonB]

Column 1. Payment entry (6 reps)

Column 2. Calculates Avail$ for commissions

(payment-company cut)Displayed (6 reps)

Column 3. Takes result of column 2 and splits it by split to salesperson(s) such as 50/50/0 or 50/25/25 whatever is determined by management (max split is between 3 possible people). Displayed (6 reps)

Column 4. Calculates final $ to individual salesperson (result of column3*individual commission rate of salesperson (6 reps).

Therefore I have 3 more columns the same as this one (6 reps).

The intention was to show a balance sheet showing the state of payments for that order and the salespersons.

[Chuck]

Please supply the calculations for columns 2, 3 and 4.

Thanks,

Chuck

[SimonB]

Hi Chuck,

Sorry I didn't get back to you sooner. I came back later that night and had a brainwave.

I just discovered how to use extend. Which I knew existed but got the wrong end of the stick on it. I suddenly realized what the problem was...I had referenced non-repeating fields!

Ta very much for your follow-up though and I am sure that I will find something else to mess up. This will teach me to not look at a product for 10 years and then expect to go back just like that.

[mr_package]

Wow, this is exactly the problem I'm having... if someone can help me out that would help.

I'm developing a pricequote/invoicing system. For the items in the order, I'm using repeating fields with Price, Description, Quantity, Total, etc.

One of the repeating fields is also "Discout" which reflects "Price * Discout_Percentage"... each repetition requires, therefore, a calculation based on Discount_Percentage, however this value is only entered once and has no repetitions. This is throwing off the subsequent calculations.

This is the same problem described in this thread... unfortunately I can't figure out how to circumvent the problem. Right now my idea is to copy the discount_percentage into a hidden field with 20 repetitions... but I'm not sure how to get FMP to do that without a rather ugly script... this is also not ideal since I'd rather have it done automatically as opposed to making the use run a script.

Is there a more elegant way of doing this? Sorry for not being more clear on the problem (trying my best- honest!)

jeff

[LiveOak]

When creating a calculation with both repeating and non-repeating fields, some care must be taken. For example:

A - repeating field

B - non-repeating field

C - calculated, repeating field

The calculation C = A * B will not work properly. Only the first repeat of C will be calculated. The correct calculation is:

C = A * Extend(:

-bd

[This message has been edited by LiveOak (edited December 29, 2000).]