2. PHPRunner General questions
  3. [SOLVED]  Show whether a related field is NULL on LIST page?
This topic is locked
[SOLVED]

 Show whether a related field is NULL on LIST page?

 		12/11/2006 6:35:39 PM
PHPRunner General questions
J
 Jkelleyus author

My project for tracking scrap production at this charity organization is progressing nicely... thanks to phpRunner and you folks here.
THANKS!
Now, here's another challenge: I have a table called SCRAP. It relates to other tables for looking up part numbers, customers, etc.
I've also now created a new table called SCRAP_NOTES. It is related to SCRAP by a number that corresponds to the ID of the record in SCRAP_NOTES. Not all records in SCRAP will have a reference to SCRAP_NOTES. Many times it will be NULL.
However, if a note exists I want to be able to let the user know they can click on a detail line for more information. Is this possible... and if so... how difficult?
Thanks,
JAKE
J
 		Jane 12/12/2006

Jake,
you can do the following:

  1. edit SQL query on the Edit SQL query tab.

    Here is a sample:
    select FieldID,

    FieldID as link,

    Field2

    ...

    from SCRAP

where FieldID is the foreign key in the SCRAP table.
2. set up this link field as Custom on the "View as" settings dialog on the Visual Editor tab and add your code:

global $conn;

$strdetail = "select * from SCRAP_NOTES where FieldID=".$value;

$rsdetail=db_query($strdetail,$conn);

$datad=db_fetch_array($rsdetail);

if ($datad)

$value="<a href=\"SCRAP_NOTES_list.php?mastertable=SCRAP&masterkey1=".$value."\">SCRAP_NOTES</a>";

else

$value="";
J
 		Jkelleyus author 12/12/2006

Thnaks, Jane... That worked well but I think I'm going to try another approach to this.
In order for 'challenged folks' to use this system, I'm going to add a VCHAR to the SCRAP table itself... rather than linking to a different table.
That way, when they add scrap the comments can go right in the ADD form without a lot of clicking back-and-forth.
Assume I call that VCHAR column NOTES... is there a way to have the LIST page show if anything exists in that column before they click on it to view or edit? Similar to your LINK example? I don't want to show the entire NOTES column on the list page... just indicate if any notes exist for the particular record.
Thanks again,
Jake
J
 		Jane 12/13/2006

Jake,
I'm not sure that I understand you correctly.

If you want to show how many SCRAP NOTES exist for the record use the following code:

global $conn;

$strdetail = "select count(*) from SCRAP_NOTES where FieldID=".$value;

$rsdetail=db_query($strdetail,$conn);

$datad=db_fetch_numarray($rsdetail);

if ($datad[0]>0)

$value="<a href=\"SCRAP_NOTES_list.php?mastertable=SCRAP&masterkey1=".$value."\">".$datad[0]." SCRAP_NOTES</a>";

else

$value="";
J
 		Jkelleyus author 12/13/2006

Sorry... I wasn't clear at all.
I'm going to do away with using another table to store the scrap notes. I'll just include a VCHAR field in the original SCRAP table. As this table reflects only one scrap record at a time anyway, making another table for notes seemed redundant.
So... The original SCRAP TABLE, which has fields with DATE, Cust_ID, PART_ID, PRODUCTION_ID, and Qty will now also have a field titled "Notes".
Since this is a VCHAR field, and since it may be as long as 500 characters, I want to set up the list page so that it displays something like "Notes Exist" rather than the field itself. This List page current displays Date, Customer, PartNo, and a production date.
What kind of code would I use in the Visual Editor Custom dialog to display a message basically saying whether the SCRAP.Notes field is null or contains a entry? Something like "Notes Exist" or "No Notes" depening on whether this field is NULL in the record...
I hope that makes it more clear... Sorry... Long day!
Thanks again,
Jake
J
 		Jane 12/14/2006

Jake,
I see what you're saying.

Here is a sample code:

if ($value)

$value="Notes Exist";

else

$value="No Notes";