Data from one record into many

[LFO Staff Member]

Hi folks, after some searching I was unable to find what I was looking for. Since this is my first post, let me know what I can do to make myself sound more coherent! :

We have two tables, one table called "A" is the main table we work in. Each record represents one 'adjustment'.

Table B contains a layout that lists a 6 'adjustments' on one record.

Since basically these are the same bits of data in the two tables, we want to automatically have the 6 adjustments from the ONE record in the B table to 1 adjustment per record in the A table.

From one record with many parts to many records with one part... basically.

I know we can do this with basic If.. Then... statements, but I was wondering if we could loop through this.

The problem is that in table B, the loop would have to change which field names it copys from everytime. Is it possible to dynamically change the field name from which the loop copies its value?

The example i've attached has field names called "AdjustmentNameA" through F respectively.

Could I, in a loop rather than longer code, copy values from AdjustmentNameA and the repective "Amt" fields in table B to table A, then create a new record, then continue through each value?

ManyToOne.zip

[IdealData]

Your design is absolutely perfect - if you relate Table A and Table B.

The whole point about relational databases is to remove duplication and redundancy of data, but you are proposing quite the opposite.

Have a good read of the user guide on relationships and the penny should drop pretty quickly when you start using a PORTAL.

[Ender]

Welcome LFO,

Your design needs some work, but it's hard to identify what the records in table "B" are supposed to be. It will help if you can first name each table for what it actually is. "A" and "B" are not very descriptive. Obviously one of the tables is about an "Adjustment", whatever that is. Do you need to remember anything about Clients in the solution?

If you can explain what you're trying to build a solution for, it might help us come up with a structure.

[LFO Staff Member]

Thanks for the response! The attachment was just a quick thing I made to try and demonstate what I was having to do. I'll try and explain the situation a little better.

The database we are working from has 4 tables already. One main one where everyone is working from is called Budget Tracking. We are inputting the amount of money and agency gets, then we can sum it all up in a nice report. We give this to the state congress members. Each record in the Budget Tracking database represents one "adjustment" to the budget, plus or minus some amounts we put in. That part is working flawlessly (I've been able to learn tons from these forums!)

The next thing we want to do is take what is called a BA7 Request and get that into our Budget Tracking table. The BA7 Request is a list of several adjustments at various places in the budget. In other words one BA7 record can equal up to 6 records in the Budget Tracking database.

The question I have isn't about this design, because I have my own objections about this setup but I am not in a position to object. The question I had was concering looping and field names.

Can a loop copy a value from a different place each time it loops? For instance if I have a field called Prog1, can it perform the loop once copying the value from Prog1 then on the second loop Prog2 and on the third loop through copy the value from Prog3 and so forth?

[Ender]

You could build such a loop by branching for each case, but I strongly urge you to reconsider the structure here. It would be better to leave the Budget Tracking data in their current table and simply relate the BA7 table to that Budget Tracking table.

If each Budget Tracking record is only ever associated with one BA7 Request, then put the BA7 Request's record ID in those Budget Tracking records as a foreign key. The relationship would be:

Budget Tracking <=> BA7 Request =

Budget Tracking::BA7_ID = BA7 Request::Record ID

If each Budget Tracking record could be associated with many BA7 Requests, then the relationship is a many-to-many, requiring a join table holding a list of Budget Tracking ID, BA7 ID pairs.

Once the relationship is established, a portal could be used to display the multiple Budget Tracking records from the BA7 Request layout.

[LFO Staff Member]

Thanks, thats a much better way to do the data base and I will do my best to relate that idea, but as I mentioned before, I only have limited input on this thing.

My solution to the problem was to build a loop structure that would use the Go To Next Field step to copy the values into global variables from the BA7 table. Then the script goes over to the Budget Tracking table and copies the values into their appropriate fields. So mission accomplished within the parameters i was given.

But if you have time... The relationship would be each BA7 to many Budget tracking records. The stupid problem is we have to have the data from the BA7 Request INTO the Budget Tracking table. Displaying it in a portal doesn't help, we need that data in the table itself for calculations. If there is a way to get the data from a portal into the table, point me to it.

//edit

After reading more about portals in the Missing Manual for FM8 I can see how to do this now! Thanks for the heads up guys!

//edit

I understand that the smartest way to do this is just to have another layout in the Budget Tracking table, but again, I'm just a student worker here, developing their database... I'm under the thumb of other people.

Anyway thanks for the help!

Edited June 9, 2006 by Guest