Distributing data over other groups??

[CADaugherty]

I have a field {table.field#} with a value in it that I am trying to spred into other groups.

This works (but...)

switch ({table1.#})=###,{table2.#})

...but now I am trying to make that value = to a name field in table2.

so, really: How do I assign that # to = to a string from table2?

Any help would be great. I have been spinning my wheels for 2 weeks on this one.

Thanks.

 

[lbass]

Can you please show sample data for the fields from each table? Also what is the content of your actual switch formula?

-LB

 

[CADaugherty]

master.BU = 10,11,12,13....
employee.BU = 10,11,12,13....
Unit.BU = 10,11,12,13...
Unit.name = XYZ, LOL, OMG

switch ({master.BU}=10,{employee.BU})

join is on master.EmpID to employee.ID

So if my BU = 10, I need it to look at the employee.BU table and use that field as the new BU with the corresponding name from the Unit table.

Does that make sense???

 

[lbass]

Why aren't you simply linking unit.BU to employee.BU and then placing unit.name on the report?

-LB

 

[CADaugherty]

I am already pulling information from the Unit table into the report and linking it on an account field. If I link back to the employee table it sets off a loop and gives me data for all employees attached to that branch.

 

[lbass]

Add the unit table a second time. An alias "table_1" will be created automatically.

-LB

 

[CADaugherty]

LOL! That is what I want to do, but my boss says "NO passthru tables!

 

[lbass]

What does that mean? This is a simple lookup table.

If there aren't that many values, you could hardcode this in a formula.

-LB

 

[CADaugherty]

That is a thought too, but the values will be changing and they don't want to do that either.

 

[lbass]

Please explain what is unacceptable about adding the table a second time to use as a simple lookup table.

-LB

 

[CADaugherty]

I had a couple of reasons given to me, but can't really get into it here... I am just unable to go about it thoes two ways. No hard coding the Units and no second tables. Sorry. They are very good work arounds - I think.

Thank you for your suggestions.

 

[Turkbear]

Hi,
What are all the fields ( and their source) in your report?

To get what you want, given the unreasonable restrictions placed on you, may be hard - the more we know about what you have and what you need, the more help we can give ( maybe).





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

What happens when you do this?

switch ({employee.BU}={unit.BU},{unit.name})

-LB

 

[CADaugherty]

switch ({employee.BU}={unit.BU},{unit.name})

= I get the BU but not the name. It would be perfect if this would just give me the little words in there.

I have been told by our IT guy that the best way is to add the table a second time. (I personally think this is reasonable) But somehow it looses its integrity and tracking???? Maybe someone can give me a better reason/explanation that might persuade my boss to go this route. I have tried it that way and it works beautifully.

 

[Turkbear]

Hi,
It is hard to prove a negative but adding the table twice does nothing to integrity or tracking as far as I can determine..You are just querying the same table twice!



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Adding the table a second time was one of my original suggestions--so your IT guy, Turkbear, and I all agree you should go that route. Add the table a second time, and link Unit_1.BU to employee.BU in the database expert, and then place unit_1.name on the report for the description.

Why not do it and then show the boss the results?

-LB

 

[CADaugherty]

Thank you all. I did just that lbass and am hoping that she agrees to it. Sure makes things a lot easier for me. And as fields change, so should that table - wouldn't they? If we use hardcoding, we may eventually drop data off without realizing it.

I feel better about my decision, but she will have to be the final ok on this one.

Thanks again to all!!!

 

[lbass]

Yes, that is the benefit of the using the table--it will be updated as the data is. If you are in doubt, you can go to database->verify database to make sure it gets updated when the database does, but this should occur automatically if you are using a recent CR version.

-LB