1) why di you not use:
Set Field [ mediaSortResult; If ( BuyNumbers::media="Radio"; RadioMetros::metro; DMA::DMA) ]
and for 2) why not:
If ( BuyNumbers::media="Radio"; RadioMetros::metro; DMA::DMA)
from the names, RadioMetros::metro and DMA::DMA
reference data in different table occurrences. Thus there must be a relationship involvled. What relationship do you have?
Both methods fail
Exactly HOW do they fail? What results to you get?
Do you see correct or incorrect values in MediaSortResult? If they are incorrect, what incorrect results?
Radio Metros are a geographic area found within DMAs. Two tables that are both linked to the Orders database.
The correct result appears on the "Media Orders" form, but not on the "route" report (see attached). I need the report to list all radio station beneath the Radio Metro, and the TV station under the DMA.
I don't see any "attached". Was your "attached" a jpeg gif or png file? As stated in the controls below, those are the only file types that can be uploaded with the Upload an image controls.
I need more detail about the actual relationships.
Your original post refers to three different table occurrences:
BuyNumbers, RadioMetros and DMA
Your most recent post refers to an "orders database"
I'd need to know precisely how these are linked in relationships (match fields, operators...) and the table occurrence context (A layout based on what table is current?) at the time this script step executes.
It was a PDF. Attached are .jpgs.
I have more than a dozen tables.
Orders (the main table) is connected to Buy Numbers
Orders <connected to> DMA <connected to <Radio Metro>
Note: Everything is joined properly. All the date shows up just fine, it is only a problem when I sort the route report on the If function field called Media Sort.
I wasn't suggesting that your relationships were wrong, just that I can't suggest a solution without a much more complete picture of the particular tables and relstionships involved.
From what you have posted, I have a bit more info
But that's still a very incomplete picture. I asked for the match fields as a way to tell which relationshihps are many to many, One to many, etc as that can be very key detail, but that info is still missing.
On what table is your route report based?
How is the value returned by this calculation supposed to affect what is seen on the Route Report?
Match Field: __pkOrdersID
Orders linked to 13 other tables. The report in question is linked to "Clients", Buy Numbers", "Media Companies," "DMA," and Radio Metros"
Clients / __pkClientsID / One -- Orders linked to "Clients" by foreign key _kpClientID
Buy Numbers / __pkBuyBumberID / One -- Orders linked to Buy Numbers by foreign key _fkBuyNumberID
Media Company / __pkMediCompanyID / One -- Orders linked to Media Companies by foreign key _fkMediaCompanyID
Media Company is linked to DMA and Metro by respective foreign keys. Both DMA and Radio Metros are a "many" relationship.
Table is based on orders.
On the Media Orders form above you can see that the MediaSortResult field is correct in that it is displaying the DMA name as this station is a TV station, and not a radio station. Consequently, the DMA name appears, not the Radio Metro name. On the Radio Buy Route Sheet is supposed to look like this:
Market: Eugene (DMA) listing station KVAL
(Ignore Sacramento -- it was in there by mistake)
Market: San Diego (DMA) listing KGTV
Note: If I sort the report by DMA, rather than the IF function, the report works fine.
The report in question is linked to "Clients", Buy Numbers", "Media Companies," "DMA," and Radio Metros"
But only one of those may be named in Layout Setup | Show Records From. Which one is listed there? From your statement "Table is based on Orders", I take it that Orders is listed in that drop down.
I can now deduce these relationships:
On what table is the first layout, the one where you have a correct result, based? (What table occurrence name appears in Layout Setup | Show Records from?)
And I assume that the field that is empty on the Route Report the calculation field where you select between DMA and Radio Metro tables for the name?
Evaluation Context would appear to be the first key thing to check If my assumptions and deductions are accurate. When you defined your calculation field, I assume that it was defined in Orders and Orders was selected in the Context drop down in Specify Calculation? Or did you define it in MediaCompany with MediaCompany as the context table occurrence?
Other factors that could produce empty fields when DMA::DMA is supposed to supply the name on the report:
1) missing or incorrectly linked Media company record.
2) missing or incorrectly linked DMA record.
Another possiblity is shown in the one to many relationships between media company and both DMA and Radio Metro. That implies that Media Company links to many records in each of those tables, but your calculations can only reference the "first" such realted record in each of those tables. If the DMA or Metros field is empty in that first related record, you'll get an empty result.
I figured it out. I had an incorrect field in my sort order. Sorry I troubled you with all this. Thank you for all your input.