help w/value list FM6

[blomgren]

I have a related file that builds a value list

fields 'attendance_code' = A-Absent AM...

'debit' = .5

I use the field 'attendance_code' (alpha) for selection purposes but need the related field 'debit' (numeric)to be used in calculating a number of fields using the 'attendance_code VL'.

Perhaps this should be in Calculations but I think I am just stuck getting 'attendance_code' = 'debit'

thanks!

[blomgren]

I forgot...

I'm not sure how to count or sum my value list?

[Lee Smith]

Not quite sure if I'm following what you want. If I'm understanding you correctly, then you want to select the value of A-Absent AM from your value list and have a second field (debit?) calculation it as -.5

If so, you need to hard code your "debit" field using something like:

Case(attendance_code ="A-Absent AM", -.5,

attendance_code = "A-Absent PM", -.5,

attendance_code = "A-Absent AM PM", -1)

HTH

Lee

[blomgren]

So... it would be best for me to hard code both the VL and amount of debit. I was afraid of that. I am trying to implement a school attendance that has user defined fields so setup is more an option. But this will work great.

Thanks, Lee

[Lee Smith]

There are other ways you could do this, but this seemed to be what you described.

Yes, you would be hard coding both sides (as shown). You need to be sure that all values in your Value List are also included in the calculation.

The great thing about this Forum is that, you can upload a sample of your file(s) to help explain what you trying to accomplish. This can be helpful to both you, and us, when things get beyond the theory stage.

Lee

[comment]

I am not sure I follow either.

You can have a related (or not) file with a record for each code and its respective value, i.e.

Codefield (text) = "xyz"

Valuefield (number) = 5

You can then define a value list using values from field Valuefield, with 'Also display values from Codefield'.

In the master file, define a number field as a popup, using the value list. During selection, you will see both the codes and their associated values - but only the value will be input. Nothing is hard-coded, and you or your users can add/delete/edit the records in the codes file.

[MoonShadow]

I wouldn't hard code for the world. What if your Debit figures change? And even if they didn't, why go to all that work when you have the data in your related file?

1) If this Debit amount changes over time but your Debit is hardcoded, you'll always be in Define Fields.

2) If you need to 'freeze' the Debit amount in your Main file, add an identical field in Main (with Auto-Enter Lookup) based upon a relationship between your attendance_code on both sides.

Does your related file (attendance) contain one each of the attendance_code? A=Absent AM and a Debit? Each attendance_code has its own debit amount? If you want to LOOKUP the Debit, what is the determining criteria on which Debit to choose (if there is more than one attendance_code match)?

3) If all you care about is displaying the Sum of related Debit amounts, establish a new relationship between your attendance_code fields. They will be unique in the Main file (selecting them from VL from your related) but will be 'many' on your related side. No problem.

If they are related, it's a piece of cake to pull that value over. All you need to know is what match (relationship) will determine which Debit to pull. If a match doesn't exist, create a new relationship establishing the match and just look up whatever you need, or access that value through the matched relationship for your calculations. You can use this Sum Debit value however you wish as:

Sum(YourMatchedRelationship; Debit)

Oh, rethink this ... don't hardcode this puppy.

[Lee Smith]

Say What!

All those values, possible changes to the values in the future, hard work setting it up.

How many values can there be. am, pm all day? How can a half a day be change to something else? and a Whole day? Sometimes we have a tendency to confuse people with over complicating a simple task or procedure.

My 2

[MoonShadow]

The question isn't necessarily about the attendence_code, Lee. Although the attendence_code already exists as records and hardcoding them again is redundant but the calcs would also have to be modified if a new attendence_code were added.

But worse still, would be the probability of multiple Debit amounts changing. This is why that related table exists in the first place. Simpler to point to your related Debit amount than to change calculations which must be manually corrected every time there is a price change.

And you are correct ... there is a big difference between overcomplicating a situation vs. overlooking the obvious logic of it.. Use the relationship. Either as Comment suggested (displaying the related Debit amounts) or Lookup or calc. That's what the relationship is there for. Not complicated at all.

[Lee Smith]

I don't disagree that there are other ways to do this, in fact I said so in my earlier post.

One of the great thing about the Forum is, we supply a few alternatives solutions to their problems, and then they can weight them all, and use what fits their needs the best.

BTW, the only thing I have ever said never use are Repeating Fields, and then I met Ugo and JT.

Lee