• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

changing FP Access reports

I use fp2003 forms to create a database and then ASP report pages from the database results wizard.  

When i want to make any changes in the report I have to rebuild the ASP report form from scratch.  Is there any way to change the elements of the database results wizard without losing the alread formatted asp report?

0
flwebster
Asked:
flwebster
  • 13
  • 7
  • 2
2 Solutions
 
hhammashCommented:
Hi,

Yes only if you add or remove things to the DRW.

So,  my recommendation is to make sure that this is what you really want before going ahead in making a fancy design.

hhammash
0
 
hhammashCommented:
Hi again,

When you create the DRW and finish, it generates ASP code.  So, if you edit the DRW and add fields it will prompt you to save,  and when you accept it will disregards previous setup and it will go back to the raw appearance.

When I create a DRW I make sure that this is what I need,  I change the setup, the design,  the colors ...etc.

I know that it is annoying when you put lot of effort in chaning the design then the DRW disregards that after editing.

hhammash
0
 
hhammashCommented:
If you change something in the SQL you won't be prompted for the change,  it is only changing the fields requested from the database.  Which is a logical thing.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
hhammashCommented:
BUT,

If you did a very hard work on the report,  time consuming work and you want to change the DRW by adding or removing a field you can do that without affecting the design.  It is a bit difficult.

How can you do it:
Let's say you want to remove a field from the DRW.

1- Open the DRW in design mode
2- Highlty the heading of the fied a title for the field
3- Switch to HTML view
4- You will see the place of that word highlight in the HTML Code. Let's say you want to remove a filed called "location". The title of the column will look like this

<td><b>Location</b></td>

You will see this before the DRW code:

  <td><b>Name</b></td>
      <td><b>Salary</b></td>
      <td><b>Location</b></td>

   </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart"  ...............etc
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results .....etc
</SCRIPT>
<% end if %>

Remove that line:
<td><b>Location</b></td>

Now,
Go to the place in the DRW at the bottom where you find field names after the variables.  You will see as many lines as the number of your fields.  The code will be in grey and the field names will be in red.


The lines I am talking about will start immediately after these lines:
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="1554" --><tr>

The line will look like this:

      <td bgcolor="#006596">
      <!--webbot bot="DatabaseResultColumn" s-columnnames="Name,Salary,Location" s-column="Location" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;BC&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Location")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="62237" --></td>

Select the line of the "location" field and remove it.  Then save the DRW.   You will see that the DRW changed and the field is no longer there, BUT,  the format you made did not change.

Now,
Adding a field which is not in the DRW.  Suppose I want to add a field called "City":

1- I go in HTML view to the table heading,  I must have 3 lines because I selected 3 fields in the first place.  Here are the lines above the DRW code:

  <td><b>Name</b></td>
  <td><b>Salary</b></td>
  <td><b>Location</b></td>

What I do is copy the last one
      <td><b>Location</b></td>
Change it to:
      <td><b>City</b></td>
Now I will have 4 lines
  <td><b>Name</b></td>
  <td><b>Salary</b></td>
  <td><b>Location</b></td>
  <td><b>City</b></td>

Go to normal view and you will see a new column added with the heading City.

Now,
Go down to those lines under the variables which I mentioned above.
Copy the last one which will look like:

<td bgcolor="#006596">
      <!--webbot bot="DatabaseResultColumn" s-columnnames="Name,Salary,Location" s-column="Location" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;BC&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Location")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="62237" --></td>

Then paste is immediately after the last one taking into consideration the <td> and the </td> to be at the beginning and at the end.  Now change the occurance of location into City.  The occurance that you need to change is in two places:
s-column="Location"  change it to "City" to look: s-column="City"
and the piece of the line which is in red:

<%=FP_FieldVal(fp_rs,"Location")%>

Change it to
<%=FP_FieldVal(fp_rs,"City")%>

Save the DRW.

The field will be added without affecting your format.

hhammash


0
 
hhammashCommented:
If your DRW is a "List - One Field Per item"  it is much more easier

Just in normal view highlight the line,  switch to HTM view,  Delete the section you selecter with the
<tr> before it
and
</tr> after it

To add
Highlight the last one
Switch to HTML view
Cop the selected section with the <tr> that is before it and the </tr> after it.  Then past under the last one or in the place you want it to appear.
Change the field name in the two locations I gave you above.

hhammash
0
 
flwebsterAuthor Commented:
Hi,
My particular problem isn't in the formatting of the report at the moment but rather in the sort order.. I have the report sorted on one field and want to sort on another.. is there a way to edit the code directly without losing the formatting of the report?  

Thanks
0
 
rcmbCommented:
To edit the code directly switch to html view and locate the red code in the DBRW region. At the top of the red code is a line of gray code that contains your SQL information. Do not edit the red code as no changes will take. In the gray line scroll your screen to the right until you find your SQL code and make your changes there. Once you save the page you will see the red code update.

You can make whatever changes you desire to the SQL in the gray region and it will not change your formatting.

RCMB
0
 
flwebsterAuthor Commented:
Great.. yes, I see the gray code and the list of fields and the setup lines.. but I have not the foggiest about how to edit it to establish a sort (timestamp from most recent backward)... I don't see anyplace in the code which identifies the sort and direction or the field on which its based... Can you be more specific as to code change?

Thanks!!!!!
0
 
rcmbCommented:
Find the area that has:

SELECT * FROM TableName WHERE fieldname='::fieldname::'

or something to that, and make it read like:

SELECT * FROM TableName WHERE fieldname='::fieldname::' ORDER BY timestamp ASC, lastname ASC, firstname ASC

the words timestamp, lastname, firstname will equal fields in your database table.

You may or may not have the WHERE clause in your SQL statement so if not just ignore that part.

RCMB
0
 
hhammashCommented:
Hi flwebster,

You should've said something about SQL my friend,  you could've spared me lot of work.  When you Say "change the elements of the database results wizard without losing the alread formatted asp report?"  means,  physical elements.

Any way,  here is the grey line, it is afer <tbody> and before <%if 0 then%>

  </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" s-columnnames="CatID,SubCat,Body,Name" s-columntypes="202,202,203,202" s-dataconnection="MemberCategory" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Categories" s-displaycolumns="CatID,SubCat,Body,Name" s-criteria="[CatID] EQ {CatID} +" s-order s-sql="SELECT * FROM Categories WHERE (CatID =  '::CatID::')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="CatID=" s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
   
Look in the first line where it says sql=Select * from Categories Where(CatID='::CatID::')

This is what you chagne in the code and save the DRW.  When you do that you will see that the red line of the :

fp_sQry="SELECT * FROM Categories WHERE (CatID =  '::CatID::')"

Has changes without changing the format.

Here is the line after modification,  I will add and AND statement and added another field which is Subcat:

s-sql="SELECT * FROM Categories WHERE (CatID =  '::CatID::' AND SubCat =  '::SubCat::')

hhammash
0
 
flwebsterAuthor Commented:
I made the changes as follows, and now my asp won't execute. Obviously, I've entered the code change incorrectly somehow. I've tried every variation I can think of without fixing it... The field name I'm trying to sort from most recent to oldest is named "timestamp"  


<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,first,lastname,hsgrade,street,town,st,zip,hmphone,email,highschool,hstown,hsst,hszip,yrsvar,hscoahc,coachphone,coachemail,D1,laxhonors,baseball,basketball,track,xcountry,soccer,football,hockey,wrestling,academicprogram,gpa,sat,psat,ACT,c9a,c9al,c9ag,c9b,c9bl,c9bg,c9c,c9cl,c9cg,c9d,c9dl,c9dg,c9e,c9el,c9eg,c9f,c9fl,c9fg,c9g,c9gl,c9gg,c10a,c10al,c10ag,c10b,c10bl,c10bg,c10c,c10cl,c10cg,c10d,c10dl,c10dg,c10e,c10el,c10eg,c10f,c10fl,c10fg,c10g,c10gl,c10gg,c11a,c11al,c11ag,c11b,c11bl,c11bg,c11c,c11cl,c11cg,c11d,c11dl,c11dg,c11e,c11el,c11eg,c11f,c11fl,c11fg,c11g,c11gl,c11gg,c12a,c12al,c12ag,c12b,c12bl,c12bg,c12c,c12cl,c12cg,c12d,c12dl,c12dg,c12e,c12el,c12eg,c12f,c12fl,c12fg,div,T1,T2,Remote_computer_name,User_name,Browser_type,Timestamp" s-columntypes="3,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,203,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,135" s-dataconnection="recruitersdb" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Results" s-displaycolumns="ID,first,lastname,hsgrade,street,town,st,zip,hmphone,email,highschool,hstown,hsst,hszip,yrsvar,hscoahc,coachphone,coachemail,D1,laxhonors,baseball,basketball,track,xcountry,soccer,football,hockey,wrestling,academicprogram,gpa,sat,psat,ACT,c9a,c9al,c9ag,c9b,c9bl,c9bg,c9c,c9cl,c9cg,c9d,c9dl,c9dg,c9e,c9el,c9eg,c9f,c9fl,c9fg,c9g,c9gl,c9gg,c10a,c10al,c10ag,c10b,c10bl,c10bg,c10c,c10cl,c10cg,c10d,c10dl,c10dg,c10e,c10el,c10eg,c10f,c10fl,c10fg,c10g,c10gl,c10gg,c11a,c11al,c11ag,c11b,c11bl,c11bg,c11c,c11cl,c11cg,c11d,c11dl,c11dg,c11e,c11el,c11eg,c11f,c11fl,c11fg,c11g,c11gl,c11gg,c12a,c12al,c12ag,c12b,c12bl,c12bg,c12c,c12cl,c12cg,c12d,c12dl,c12dg,c12e,c12el,c12eg,c12f,c12fl,c12fg,div,T1,T2,Remote_computer_name,User_name,Browser_type,Timestamp" s-criteria s-order s-sql="SELECT * FROM Results WHERE(timestamp='::timestamp::')ORDER by timestamp ASC)" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="BODY" preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" startspan --><!--#include file="../_fpclass/fpdblib.inc"-->
0
 
hhammashCommented:
Are you getting an error or just "No records returned"?

If you are sure that the table has information.  Make sure:

1- The database has write permissions
2- The database is verified: Tools > Websettings >Databases  then click on the database and click verify.

let me know please.

hhammash
0
 
flwebsterAuthor Commented:
no, cannot be returned.. when I loaded my backup copy, identical except for these changes.. it works, so clearly I'm formatting the code incorrectly,
and I'm certain that it's in the quotation marks, etc for the SELECT * FROM results WHERE (timestamp='::timestamp::' ORDER by timestamp DSC"
(or whatever the code is for descending...

by the way, my apologies for stating the problem so poorly.. in fact I have all sorts of prolblems with DRW rewriting... I try to plan carefully but
it almost never fails that someone thinks of some reason why it should be different, and generaly I've just rewritten them but
this one is a huge report and would take forever to rewrite.. so thank you for your patience wtih me, and my sincere apologies for my
poor use of the correct terminology.
0
 
hhammashCommented:
Hi,

Timestamp is a binary field,  your line should be
Where(Timestamp=Timestamp)
0
 
flwebsterAuthor Commented:
now works fine when i do ASC for ascend, what is the code for descending order (newest to oldest)?
0
 
flwebsterAuthor Commented:
Thank you.. i figured out the desc code and it works beautifully. Thank you for your patience and excellent help!!!
0
 
hhammashCommented:
Desc
0
 
hhammashCommented:
You are welcom
0
 
hhammashCommented:
No problem flwebster,  you are welcome, no harm done.  But I hope that you might benefit from the explanation on how to change Physical Formatting lateron.

Regards
hhammash
0
 
hhammashCommented:
Hi flwebster,

A piece of advice for the future.

Don't rely on timestamp fields that comes with Frontpage.  Create your own stamp.

In Access table create a date field and a time field and make them default =Date() =Now() or =Time().

Whenever a new record is added these two fileds will take the current date and current time.

Timestamp fileds get tricky sometimes and create problems with your SQL without knowing.

Regards
hhammash
0
 
flwebsterAuthor Commented:
Thank you for the advice. I'll use it for my projects from now on!
0
 
hhammashCommented:
You are welcome flwebster
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now