I'm trying to make a calculation for a field that Gets the Repetition # of a repeating field.

My ultimate goal is to make odd numbered repetitions (containing a price) to be added to a subtotal field AND the even numbered repetitions (containing a receipt number) to return as 0... thus, not being amended to the subtotal field.

In addition, I need the subtotals to correspond to the Expense Type field (something that I don't even know if it is possible)

I can't seem to get it to work... any ideas? Can someone point me in the right direction?

Please see attached file. Thanks in advance

Report.zip

Back to the original advice. Get rid of the repeats. You're just digging in deeper to the problems they generate. Will you ever want to know, for instance, how much was spent on hotels in a certain period? Or by a certain person? Or by a certain person in a designated period?

Oops, sorry. You're using repeats.

Edited August 6, 201015 yr by Guest

Oh my goodness, VXASUXV, I agree STRONGLY with BruceR on this one! I looked at your file and you should NOT be using repeating fields here. And it seems like you've been warned before.

I simply can't say it strongly enough. And we cannot help you in this path because you are painting yourself into a corner. As you have been experiencing at every step in your design, you hit a snag and/or dead-end. Don't use repeaters for data.

Change it now before you waste more time only to reach the same conclusion.:badidea:

Back to the original advice. Get rid of the repeats. You're just digging in deeper to the problems they generate. Will you ever want to know, for instance, how much was spent on hotels in a certain period? Or by a certain person? Or by a certain person in a designated period?

Oops, sorry. You're using repeats.

I don't believe so, from my understanding, they just want to be able to generate a quick and easy report for submitting travel expenses. Tracking expense over a period of time is beyond the scope of their needs. They just want to be able to record expenses, receipts, etc into a database and be print a record for accounting.

Oh my goodness, VXASUXV, I agree STRONGLY with BruceR on this one! I looked at your file and you should NOT be using repeating fields here. And it seems like you've been warned before.

I simply can't say it strongly enough. And we cannot help you in this path because you are painting yourself into a corner. As you have been experiencing at every step in your design, you hit a snag and/or dead-end. Don't use repeaters for data.

Change it now before you waste more time only to reach the same conclusion.

What would you recommend? Just doing a portal setup via a related database? Again, I'm more of a beginner so I'm confused as to the logic/advantage of the portals vs. repeating fields in this scenario.

Someone helped with an alternative way of doing this by running a Pattern Count and returning the expense cost as 0 when the Expense Type = Receipt, but it was rather cumbersome and aesthetically displeasing to have to do a "Receipt" field after each incidence of expense.

Edited August 7, 201015 yr by Guest

"Again, I'm more of a beginner so I'm confused as to the logic/advantage of the portals vs. repeating fields in this scenario."

No matter how they will be using the data; no matter what your FM level; no matter whether you see the logic or advantage ... the combined experience between Bruce and I exceeds 16 years as Developers. And most other Developers would be suggesting the same thing. We understand wanting something simple and that is what we are trying to move you towards. Repeating fields is the more difficult way to proceed. Truly.

BTW, your response to Bruce about "they just want to be able to generate a quick and easy report for submitting travel expenses" is said in total honesty and we believe you. But I can assure you that what they say is not what they want next week or the week after. You will have spent all this time providing what you think is quick and easy (and it will be difficult). And then they will ask for one more thing and it will be IMPOSSIBLE in your existing structure and you will have to start all over ... trust me on this one ... been there and done it more times than I'd like to admit.

I do not have time to provide a sample structure right now; maybe Bruce (or someone else) can. But if you haven't been assisted in moving towards an easier approach using proper relationships by mid-weekend, then I will make the time. The right foundation is that important. Proper relational structure is ALWAYS easier, faster and more flexible. We will help you get there.

UPDATE: As an example of how much easier it would be using relational ... your existing 35 fields would reduce to 5. Let us show you the magic of relational design. Once you grok it, you'll never look back.

Edited August 7, 201015 yr by Guest
Added update

I'm eager to learn, so I look forward to an example (if of course, you have the time). Thanks LaRetta

A small addition to what Bruce and LaRetta said:

Your starting point should be to have a separate record for each expense. It is a common mistake to design a solution from the point-of-view of 'how will users enter the data'. Repeating fields are a powerful lure here, because they make it very easy to put the data in - but equally hard to get it out.

Separate records, OTOH, make it very easy to find and aggregate expenses by any criteria (time span, person/s, type of expense, etc.). However, building the user interface (such as the weekly entry form in your file) requires more effort.

Here is something you could use as a starting point:

ExpenseLog.zip

A small addition to what Bruce and LaRetta said:

Your starting point should be to have a separate record for each expense. It is a common mistake to design a solution from the point-of-view of 'how will users enter the data'. Repeating fields are a powerful lure here, because they make it very easy to put the data in - but equally hard to get it out.

Separate records, OTOH, make it very easy to find and aggregate expenses by any criteria (time span, person/s, type of expense, etc.). However, building the user interface (such as the weekly entry form in your file) requires more effort.

Obviously I completely agree with the recommendations to use a related table. But at least the *appearance* of the existing format and click-any-cell to edit has great appeal from the user's point of view. And I have received complaints from clients about not being able to emulate that kind of format.

So I wonder about techniques we might use or develop to meet both needs. The problem with a portal method, say multiple portals side by side to emulate the original layout, is that we must populate all the rows even if we aren't going to use them, so the user can click anywhere.

I have done some exploring along this line and may have something to post later, but some discussion or examples by others would be great.

The problem with a portal method, say multiple portals side by side to emulate the original layout, is that we must populate all the rows even if we aren't going to use them, so the user can click anywhere.

Not really:

http://fmforums.com/forum/showpost.php?post/205259/

And that was before version 11 and filtered portals.

Looks excellent, thanks!

But does it only work for data display or did I miss something? I tried only briefly but it did not seem like I could click on a random cell and enter data.

All it takes is turning "Allow creation..." on and enabling the field for entry.

Well so it does; doh! Thanks.

Obviously I completely agree with the recommendations to use a related table. But at least the *appearance* of the existing format and click-any-cell to edit has great appeal from the user's point of view. And I have received complaints from clients about not being able to emulate that kind of format.

So I wonder about techniques we might use or develop to meet both needs. The problem with a portal method, say multiple portals side by side to emulate the original layout, is that we must populate all the rows even if we aren't going to use them, so the user can click anywhere.

I have done some exploring along this line and may have something to post later, but some discussion or examples by others would be great.

This is among my main concerns. I've done similar setups for the client (friend) and they always have complaints about not being having a similar format to the original file.

I agree with a new record for each expense, because you're 100% correct about not being able to get information out. In the long run, they are easier for tracking information... that being said, I think this is beyond the means of the client.

For me, this is one of the annoying situations where you're torn between doing what is CORRECT and what THE CLIENT WANTS....

This is among my main concerns. I've done similar setups for the client (friend) and they always have complaints about not being having a similar format to the original file.

I agree with a new record for each expense, because you're 100% correct about not being able to get information out. In the long run, they are easier for tracking information... that being said, I think this is beyond the means of the client.

For me, this is one of the annoying situations where you're torn between doing what is CORRECT and what THE CLIENT WANTS....

How is this beyond the means of the client? Your comment is completely unexplained.

You've already been provided with everything you need to make this work. It's a one hour job.

Earlier I said:

It is a common mistake to design a solution from the point-of-view of 'how will users enter the data'.

Even with repeating fields, the idea of users entering different types of data into the same field (as per your original question) is the same type of mistake. You should use another field for the receipt numbers, and build your layout using individual repetitions.

The way you have it now, you are shooting yourself in both feet.

This is among my main concerns. I've done similar setups for the client (friend) and they always have complaints about not being having a similar format to the original file.

For me, this is one of the annoying situations where you're torn between doing what is CORRECT and what THE CLIENT WANTS....

Research your options, understand available techniques, pay for expert help if you need to, and then do what is correct. You may not be charging enough.

There are consequences to design choices that your client may not understand. That comes with the territory, and is probably the case in most fields.