SQL Select logic, Form item reference in report 1

[Labell]

I'm not sure if everyone has read this but, I am a new software engineer, brought in on a really old product. I had never heard of FoxPro before until a couple months ago. I've managed to learn enough to make a few modifications to a form and report. SQL Statements still really get to me, but I am learning quickly. I attached a link to a picture of the pieces I need help on.

RED - This SQL Statement, I modified slightly but didn't get the effect I was looking for; to link the NSN numbers (BLUE) to one another to get the COST of each item from the partmast table which has all items and costs of each item associated.

BLUE - the items in question.

GREEN - I'd like to take the date selections here and put them in a Report by means of taking the numbers and sticking them on the page. No clue how to go about that one.

 

[Olaf Doschke]

Ok, we see what you end up with, but not the original code, which worked and what you changed.
It's very hard to see what you want without knowing about the structures and their meaning.

You say what you want to do is "to link the NSN numbers (BLUE) to one another".

I'll take it as you want to join two tables on the common field of "NSN number".

1. tipp: BROWSE NOCAP will make the browse window show the technical field name instead of the user friendly caption, so it's easier to see what you need to refer to in code. Field names with spaces in them are technically not allowed, so I know these are the field captoins only.

2. tipp: To join two table you write:
SELECT fieldlist FROM table1 INNER JOIN table2 ON table1.nsnnumber = table2.nsnnumber

The join type might also be LEFT JOIN, depends on your needs.

Bye, Olaf.

 

[Mike Lewis]

Labell,

A (belated) welcome to the forum - and to the world of Visual FoxPro.

I think Olaf has answered your main question, about how to use the MSN to link the tables.

Regarding your "green" question: I understand you are prompting the user for a date range, and you want to report on orders within that range.

The dates that the user enters will be stored in the Value properties of the respective textboxes. You can access these values by reference to THISFORM.TxtSDate.Value and THISFORM.TxtEDate.Value respectively.

Now, you need to make sure that you had initialised those text boxes as dates (as opposed to character strings or numbers). That way, you force the user to enter a valid date, rather than an arbitrary series of characters that might or might not represent a date. To achieve that, add this line of code to the Init of the two textboxes:

this.Value = {}

Now you have the dates, you can wire them into your SELECT statements. That's a matter of extending your WHERE clause.

The first SELECT already has a WHERE clause which is telling it to limit the selection to orders that are closed. There's another condition in there as well (&cond), but we have no way of knowing what that's doing. To add the date selection, extend the clause as follows:

WHERE oo.Status = 'CLOSED' AND &Cond ;
[b]AND BETWEEN(ooHead.OrderDate, THISFORM.TxtSDate.Value, THISFORM.TxtEDate.Value)[/b]

(Note that the semi-colon is just to tell it to break the line there.)

I'm assuming that you are running the SELECT from within the form (the one that is prompting for the dates). If not, it won't like the reference to THISFORM. In that case, you will have to copy the contents of the textboxes to a pair of variables, and make those variable available to the SELECT. If you don't understand how to do that, ask.

Hope this will get you started on this aspect of the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Labell]

Mike,

I already have them selected out by dates in some code up above what I have shown. I simply want the String value put into the report.

 

[Labell]

On what Olaf said,

The SELECT statement I think I'm supposed to use looks something like this:

SELECT partmast.cost FROM partmast LEFT JOIN oo_line ON oo_line.nsn = partmast.nsn

I am using a 'SELECT outbound' statement to use the cursor as the ending select statement. I tried simply adding this line and adding 'INTO CURSOR outbound' to the end, but it wasn't what I was looking for. Do I somehow have to incorporate that select statement into the outbound cursor?

 

[Labell]

Here's a picture of what the Report looks like. I'd like the Date range that the user selects to display below the Title of the report.

The costs are obviously not linked correctly.

I'm going off what I learned from reading how another form / report was done. So this code may or may not be very effective or efficient for my needs. If there is an easier to way to pull this off, I'm all ears.

 

[Mike Lewis]

To add the date range to the report title:

First, you need to store the dates in a pair of private variables. You need to make them private, because otherwise they will be out of scope in the report.

Assuming that your code that launches the report is still in the form that prompts for the dates, you need to amend it as follows:

PRIVATE pdStart, pdEnd
pdStart = THISFORM.TxtSDate.Value
pdEnd = THISFORM.TxtEDate.Value

REPORT FORM .... (same as you have now)

In the report, add a field to the report, and place it beneath the title. In the Expression box (in the field's properties), add this code (or something similar):

"Showing orders between " + TRANSFORM(pdStart) + " and " + TRANSFORM(pdEnd)

I think that will do what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Labell]

Thank you Mike, that did it!

Now onto this SELECT problem... wish me luck :/

 

[Mike Lewis]

Glad that bit of the problem is sorted at least, Labell. As for the bigger issue, I'll leave you in Olaf's capable hands.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

I tried simply adding this line and adding 'INTO CURSOR outbound' to the end, but it wasn't what I was looking for.

If you think this appends that data, then certainly you don't get what you expect. INTO CURSOR always creates a new cursor. To get the cost as another field into the report cursor you need to add the join to the SQL you already have, yes.

For example, if your query now looks like:

Select ...
From ...
join ...
join ...
into cursor outbound

change it to

Select ..., PARTMAST.COSTS 
From ...
left join ...
inner join ...
LEFT JOIN PARTMAST ON PARTMAST.nsn = oo_line.nsn
into cursor outbound

I believe you already have oo_line in your original query and you want to add data from partmast, therefore you join partmast, not vice versa.

Also, of course you need to change the report to display that new costs field in some report field, it won't appear just because you added it to the report cursor. That's just the step making it possible to put that data into the report.

Bye, Olaf.

 

[Labell]

I found the issue. It wasn't that I had a bad SELECT statement. I had to remove the 'partmast.' from the report fields when getting the 'cost' to show up. 'partmast.cost' did not work, 'cost' did work. If that makes any sense.

Thank you both very much for the help.

 

[Mike Lewis]

Yes, it does make sense. It's because Partmast isn't the alias of the selected cursor (or table) at the time you run the report. The report was looking for the Cost field in a cursor called Partmast. For whatever reason, that field doesn't exist.

By removing Partmast (and the dot), you tell it to look for Cost in the current alias, which is presumably the right one.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

In short: partmast.cost is the valid full qualified name in the query, but in the end you create a cursor outbound, of which this becomes a field, so then to adress this field within the cursor, the full qualified name is outbound.cost.

Actually, if you do this query in the command window, both names will be valid, because an sql query opens the dbfs and they are kept open together with the reyult set, so after the query both outbound.cost and partmast.cost can be addressed. But as outbound is the report cursor, only it will be scanned from top to bottom and so either you prefix report field expressions with outbound or not at all.

Bye, Olaf.