Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Repeating Field


DocD
 Share

Recommended Posts

...hmmm, I have a repeating field called 'Service Payments Made'. Simply this repeating field lists multiple payments made and the total needs to get subtracted from a field called 'Service Cost'. Then I am attempting to have that calculation placed into a field called 'Services Amount Due'.

 

Any solutions for this probably basic situtation? Thank you all!

 

DocD Oh Really!

Link to comment
Share on other sites

...in addition to the above question, I am also attempting to use TextColor in the next step to indicate the status of payment (i.e. "Owes", "Paid"...) in an additonal text field called 'Payment Status'. My formula in an auto enter calculation is;

 

Case ( Services_AmountDue=0 ; "Paid" ; Services_AmountDue>0 ; "Owes" ; Services_AmountDue

 

DocD Oh Really!

Link to comment
Share on other sites

Services_AmountDue=Service_Cost - sum(Service_Payments_made)

 

and

 

Payment_Status =

Case

(

Services_AmountDue=0 ; TextColor ( "Paid" ; RGB ( 20 ; 200 ; 10 ) );

Services_AmountDue>0 ; TextColor ( "Owes" ; RGB ( 200 ; 0 ; 0 ) ) ;

TextColor ( "Overpay" ; RGB ( 0 ; 0 ; 225 ) )

)

 

I tweaked the colour values to make them a little less eyewatering than full 255;0;0 etc. But of course the effect is slightly different for each screen.

 

 

Maarten

 

 

ps I hesitate to give the usual lecture about repeating fields. tracking payments is better done in a related lineitems file.

Link to comment
Share on other sites

I agree with KJOE. You should create a seperate table with line items. A record being created for each payment made and then you could sum these line items etc.

 

However, if you want to add up the payments made in a repeating field you will need to write a loop that looks at each repetition. I have no idea how many repetitions you have set the field for (This is another reason to use a related table and add records. You can add as many or as few as you need. What happens if a little old lady makes payments of $5 a week for the next 112 weeks? I doubt you have the repeating field set that high?) Anyway, a loop can go round and round looking at each repetition and adding it to a global variable until you've looked at all the repetitions. You should use a couple global variables to control the loop also. One to count which repetition you are on and one to contain the maximum number of repetitions. If you need help with the actual hard code let me know.

 

But depending on how far you are in your development process you should SERIOUSLY consider redesigning the structure and have a seperate table to contain these records rather than storing them in a repeating field.

 

Randy

Link to comment
Share on other sites

However, if you want to add up the payments made in a repeating field you will need to write a loop that looks at each repetition.

Not true. The aggregate calcs work on local repeating fields just fine.

Link to comment
Share on other sites

...ouch! Indeed I am not much in favor of repeating fields although in this case there are only two payments to be made. I appreciate all of the input! whew, those articles are indeed anti-repeating fields for sincere reasons!

 

The solutions provided made a simple solution for my question. And, do I use the same formula for TextColor for the 'Services Amount Due' field as well? It is a calculation field...does that make a difference? Thank you so much kjoe!!!

 

DocDsmiley-laughing

Link to comment
Share on other sites

...is there a method for extracting a specific one of the repeating fields? I see 'Get ( ActiveRepetitionNumber )', but how is this used? Where does one put the repeating field for FM to search from?

 

Thanks...

 

DocD smiley-smile

Link to comment
Share on other sites

...is there a method for extracting a specific one of the repeating fields? I see 'Get ( ActiveRepetitionNumber )', but how is this used?

To reference a specific repeatition of a given field, you can use either:

 

GetRepetition ( NameOfFieldHere; n )

 

...where "n" is the number of the repetition you want to reference - or simply:

 

NameOfFieldHere[n]

 

Where does one put the repeating field for FM to search from?

Not quite sure if I understand what it is you're trying to do here. However it's not possible to search on a specific repetition in isolation - at least not without a bit of trickery (eg referencing the repetition in a calc and performing a find on the calc rather than the repeating field)... ;)

Link to comment
Share on other sites

Indeed I am not much in favor of repeating fields although in this case there are only two payments to be made.

 

Well allright. Nevertheless, in the future your solution may be required to hold multiple payments... if you go for the repeating field solution you'll have a lot of work getting your old data in... if you set it up relational from the get go, all you'll need to do is enlarge the portal for the payments a bit and maybe add the scroll bar... that's about ten minutes tweaking a layout... think about it. And about Randy's little old lady.

 

maarten

Link to comment
Share on other sites

...thanks for ALL your input about these blasted repeating fields. Right now I merely have those two repetitive fields I previously mentioned. However, I was curious about the ability to use one of the repeating fields' repetitive fields...if I could use it if needed to create some other calculation...truly just curious!

 

I appreciate everyone's concern about my development and modeling! What a super community for guiding we neophytes down the right path!!!

 

DocD smiley-wink

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use