Script gets field name from another table's record

[chrisknight55]

Classes::field1 = "Wed1"

Classes::field2 = "Eve"

Classes::field3 = "Sailing 101"

I want to build a variable from two fields in one table:

Set Variable[$twofields; Value:Classes::field1 + Value:Classes::field2]

And then use this variable to identify a field in another table called Schedules.

In the above example I'd have identified a field in the second table called Schedules::Wed1Eve.

So that I can put Classes:field3 ("Sailing 101") into Schedules::Wed1Eve

[bcooney]

Chris (I assume that's your name),

It doesn't seem as if you are starting off with the correct data structure.

Can you tell us the names of the tables that you have and how they are related? "Field1," "field2," etc., I hope are not your actual field names...

To answer your question, however, you do not use "+" to concatenate, but rather "&". However, what you've shown here is not a good start.

Just to guess, you are tracking enrollment in Classes.

Two links that probably will help:

Comment's Bookings Demo

and anything about "Enrollment" from Ender, start with this:

Ender's ERD for an Enrollment system

[chrisknight55]

Hi BCooney. Thanks for replying.

The real field names are different and descriptive. I used Field1 and Field2 as examples just for this post.

And I realized afterward that I meant & not + to concatenate two strings.

Structure weakness aside, and forgetting the concatenation issue, How do I softcode a field name?

If the table name is "BlankSched", and the field name is "Wed1Eve" and the classname is "Sailing 101"

How do I Set Field?

How do I put "Sailing 101" into BlankSched::Wed1Eve ?

Much thanks

Chris

[comment]

Structure weakness aside ...

I'd suggest you put the structure weakness first, because solving that will make your question redundant. If you have a field named "Wed1Eve", then you probably also have another field named "Thu2Mor" and many other fields like that. All these labels are DATA - and data should go INSIDE fields, not next to them.

Once you have the correct structure in place, the answer to the question 'which classes are scheduled for Wednesdays evening' will come directly through a relationship - matching on day-of-week and time. No scripts, "soft-coding a field name" or concatenations tricks are required.

[chrisknight55]

Hi Michael

Thanks. I appreciate that you feel I'm asking the wrong question and therefore are reluctant to give me an answer.

But a simple "Set Field By Name" would have done no harm. I did find that by reading, and it works and it does exactly what I want it to do.

The field Wed1Eve is not data, it's a position on a calendar, that I'm filling with a class name.

I'm sure your solution would be more elegant, powerful and more structurally sound, but the solution I asked for works just fine for my needs.

Respectfully,

Chris

Edited October 23, 2010 by Guest

[bcooney]

"I'm sure your solution would be more elegant, powerful and more structurally sound, but the solution I asked for works just fine for my needs"

Well, hope it works out for you. Many times, not having a structurally sound system only reveals itself when you need to report on the data---and can't.

[chrisknight55]

Yes, thanks.

[comment]

a simple "Set Field By Name" would have done no harm.

That's difficult for me to judge. My impression is that providing such answer would be akin to fixing the engine of a car stuck in mud - so that the driver can continue to dig himself deeper...

The field Wed1Eve is not data

I am afraid I cannot agree with you here. "Wednesday" is an answer to the question "When?". Or, if you prefer a more technical term, "When" and "Wednesday" are an attribute–value pair - and Wednesday" is the value part.

Having fields named like "Wed1Eve" is similar to having fields named "John", "Peter" etc. instead of one field named "Name", with records containing the values "John", "Peter" etc. in the Name field.

[chrisknight55]

Thanks. I'm not far enough along to understand what solution you would devise. I only know that this works for the purpose.

[chrisknight55]

Ummm, Wed1Eve is not the answer to when. It's the answer to Where, a unique place on a printable calendar. There's likely a better way, but this is doing what I want. More than I can say for the economy, traffic, The Kardasians or growing older.

Thanks again.

[LaRetta]

You may THINK this is giving you what you want but I assure you that you are walking forward thinking you are fine and you are not. You need to realize that we've been walking this road for a long time (and hundreds of thousands of miles) and we don't make suggestions lightly nor without GOOD reason.

I can only highly suggest you step back and seriously consider what you are being told by the top developer in this business (Comment); wisdom that people pay top money for is being given here for free. Truly. :smile2:

[chrisknight55]

But the responses have ONLY been to

Not go down this path.

No one has offered another path.

And I hope Comment others know I truly value their expertise and their willingness to help.

[LaRetta]

I'm sure your solution would be more elegant, powerful and more structurally sound, but the solution I asked for works just fine for my needs.

Does that sound like you want assistance to solving your REAL problem?

You have repeated several times that what you have suits your needs so no, why should anyone OFFER if you won't even ASK ... "What do you suggest? How would you do it?" ... or any similar phrase?

:wink2:

[chrisknight55]

Yes, I could have asked, and I likely should have. I didn't have the impression that anyone knew what my solution was, and I didn't want to abandon it.

I'd have loved to have someone say "Here's what you've asked for (Set Field By Name) but there are problems with what we think your structure is. If you can describe further what you're trying to accomplish, we can set you on a better path."

If you've taken anything I've written as being ungrateful, it's just the resident frustration.

Many thanks.

[comment]

I think we are beating a dead horse here. FWIW, I probably WOULD have mentioned Set Field By Name [] - had I understood your question. I was actually suspecting you are looking for the Get Field() function. I didn't think it was worth exploring, because - as you well know by now - I don't think you should go down this path anyway.

Now I'd suggest we put this aside. I am tempted to say "put this aside until you run into the next problem due to inadequate data structure", but I am not that kind of person... :

[LaRetta]

I didn't have the impression that anyone knew what my solution was, and I didn't want to abandon it.

WEll, better to abandon it now than after you've put in quite a bit more work and that was the input your were receiving. WE may not know your exact situation but I can assure you that we know pending doom when we see it.

The fact that you don't want to abandon a sinking ship isn't our problem ... it is yours. The proper solution has nothing to do with your question. Your question is a sign of improper design, as you've been told now more than once.

I won't keep trying to talk you into something that we know you should listen to. You are going down a wrong path and you don't want to change what you are doing. Honestly, that's fine with me and I'm sure that's fine with Barbara and Comment as well. Knowledge may be power but it is worthless unless it is taken advantage of.

[chrisknight55]

Hang on. You're taking offense. But I don't think you understand my structure.

I think you all are assuming I've built a multi-record table using Wed1Eve, Thur1Morn, etc as field names. I have not.

I have a table of students, each student has enrolled in a course. Sailing 101 would be an example.

I have another table of all classes for all courses, and I can filter that, for any one student, by the name of their course, giving me a found set of their particular classes (that month's schedule of Sailing 101 classes).

Each class has a particular week, day and time it's offered. One of the Sailing 101 course's classes is offered Wed1Eve.

I then have a blank schedule, form-view that has a field for each day of the week, four weeks a month, both morning and evening.

This blank schedule table is only used as a single record, for one student at a time, for printing purposes only. It's here that I use fields such as Wed1Eve or Thur1morn as placeholders, spots on the printed schedule.

I fill the schedule by walking through the found set for that particular student, taking each of their classes that month and 'placing' it on the schedule. (Set Field by Name) After printing it I delete the record and fill it for the next student.

There is no need to store the schedule of each student, so this is a momentary use of the blank schedule table.

And since each class has an Instructor, I can also print that instructor's individual schedule that month. Each class is held in a particular room, and a room's usage schedule can also be printed.

If I've built a structurally weak database solution, I'd greatly appreciate knowing why, and, if I haven't completely singed everyone's feathers, how it could be done differently.

Thanks much.

[LaRetta]

I'm not taking offense at all, Chris and Comment is the easiest-going person here. We're trying to help. Unfortunately, I spend the last of my energy drawing your attention and now I'm out of time.

But since you've provided more information, it might be enough for others to help you get turned the proper direction because, from your description, it has been confirmed that we had reason to be concerned, "that has a field for each day of the week, four weeks a month, both morning and evening. "

You can take advantage of vs. 11's filtered portals for display of your form - you need records instead of fields. Good luck to you ... I'm going to be swamped for next two days finishing major project but I'll help if I get another chance. :laugh2:

[chrisknight55]

Many thanks, especially for your kindness and understanding.

[comment]

Have a look at the attached example. This is not the only way to do it, but it has the advantage of keeping the data layer clean - at the expense of laboring over the layout.

WeeklyRoster11.zip

[David Jondreau]

Sounds like you've got quite a bit of advice already...what can a little more hurt?

You should look at SeedCode's Calendar solution, it's designed to take actual dates, rather than a weekly designation (like Wed1Eve). With it though, and a table for "excluded" dates (vacation/holidays), you could script the dates a class will be held and gussy up your solution a little.