Missing Related Fields

[zippyaus]

Hi all

I am using fx.php with FMSA9 and php5 on Mac OS X.

I have a calculated field that is made up of several fields from related databases.

I have a php page that returns the calculation and displays everything from the primary table but not the related fields.

I have added the fields to my PHP layout in Filemaker and turned on Debug in FX.php.  When I click the debug link to show the xml page, the related fields are not shown.

Code:

<ERRORCODE>0</ERRORCODE>
<PRODUCT BUILD="01/12/2008" NAME="FileMaker Web Publishing Engine" VERSION="9.0.3.316"/>
<DATABASE DATEFORMAT="MM/dd/yyyy" LAYOUT="PHP_Web" NAME="Events.fp7" RECORDS="24933" TIMEFORMAT="HH:mm:ss"/>
−
<METADATA>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Status" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Task Date" TYPE="DATE"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Type" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="CD Type" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Display Event_ALL_Web" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Group Lookup" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="" TYPE=""/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="" TYPE=""/>
</METADATA>

I am pretty sure I have setup the accounts the same in the other databases.  I have enable the guest account and given it access to the FMxslt, FMxml and FMphp which is what I have in the Events.fp7 database.  When I look at the databases in the server console, they show as having ticks against these.

Am I missing something.

Thanks in advance for any assistance

Zippy

[webko]

If the related fields are shown on your layout in layout mode like:

FOREIGN_TABLE::field_name

Then to be viewed via the web, those tables must also be web accessible.

OTOH, if you define a local field as a calc to = FOREIGN_TABLE::field_name then the data should be shown

[zippyaus]

Webco,

so if I am displaying data in a calculated field, regardless of the setup of the other table it should be shown?

Is that correct?  Even if it is on another server? (Which is not the case here, all the tables are on the same server, but I have a 4 servers)

The calculated fields of fields from FOREIGN_TABLE::field_name are not be shown.

I am using the following to display my field

Code:

<?php
$checkHeader = NULL;

foreach ($catResult['data'] as $key => $catData)
{
$Detail = $catData['Display Event Web Full'][0];


echo "<tr valign='top'>\n
<td width='20' height='15'>
                <div align='center'><img src='/Images/Bullet.png' width='9' height='12'> </div></td>\n
<td>$Detail</td>\n
</tr>\n";

}
// End the foreach loop
?>

Cheers

Z

[zippyaus]

If the related fields are shown on your layout in layout mode like:

FOREIGN_TABLE::field_name

Then to be viewed via the web, those tables must also be web accessible.

OTOH, if you define a local field as a calc to = FOREIGN_TABLE::field_name then the data should be shown

OK, to clarify.  I have 3 databases.  Events, Staff and Cases.  The Events database is web publishing all of it's local data.  I have a relationship to Staff and Cases.  I want to display the name of the staff member and the name of the case.  I can see both of these fields on my layout so my relationship is working.  I have enabled the guest account to have access to the FMxslt, FMxml and FMphp which is what I have in the Events database.  But these fields do not show up in my php page.  My php appears correct.

I have created a calculated field call Details.  It contains the event details (from Events), the Staff members name (from Staff) and the case detail (from Case).  On my Filemaker layout I can see all the data.  It is displayed correctly as "Some Event to be attended by Staff Member on Case Date"  When I display this calculated field on my PHP page, all I see is "Some Event to be attended by  on "

Webco has suggested that the related information in a calculation should be displayed regardless of the webaccess setting.

I am really stumped as to why this would be happening.

I have also created the fields as calculated fields in there own right in the Events database and tried to display these to no avail, ie.  a calculation called StaffMember, which is just a text calc of Staff::FullName.  This does not display either.

What am I missing here? Any help would be greatly appreciated.

Cheers

ZP

[webko]

Well ,my normal approach is to try something simple and then work up to the full-blown version...

So, try these ideas in order and see where they fail

Code:

<?php 
foreach ($catResult['data'] as $key => $catData)
{
$Detail = $catData['Display Event Web Full'][0];
echo "Detail ".$Detail;
} ?>

If we get something back, that's a start - I'll note that your original code may not work as the $Detail is included as a string rather than a variable - if the above works, try:

Code:

<?php
foreach ($catResult['data'] as $key => $catData)
{
$Detail = $catData['Display Event Web Full'][0];

echo "<tr valign='top'>\n
<td width='20' height='15'>
                <div align='center'><img src='/Images/Bullet.png' width='9' height='12'> </div></td>\n
<td>".$Detail."</td>\n
</tr>\n";

} ?>

[zippyaus]

OK

I tried the first part and it returns the data minus the related fields.

Display Event Web Full is a calculated field in Filemaker being

Code:

Substitute ( GetAsText ( Task Date )  ; "." ; "/" ) & " - " &
Matter Numbers::Matter Description & " [Matter " & Matter No & "]  " &
"Represented by " & SC Assigned To::Full Name & " - " &
If ( IsEmpty ( Task Assigned ) ; "" ; " - "  & Task Assigned) &
If ( IsEmpty ( Task Time ) ; "" ; " at " & TimeAsText ( Task Time ) )

It displays correctly in the Filemaker layout.

Using the code provide I have created a simple php page

Code:

<?php

define('DEBUG', TRUE); 

include_once("../FX/FX.php");
include_once("../FX/server_data.php");
include_once("../FX/FMErrors.php");

$Group=$_REQUEST['Group'];

$cat=new FX($serverIP,$webCompanionPort,'FMPro7');
$cat->setDBData('Matter Events.fp7','PHP_Web');

$today = date("m/d/Y");

$todate = mktime(0,0,0,date("m"),date("d")+14,date("Y"));
$nextdate = date("m/d/Y", $todate);

$cat->AddDBParam('Group Lookup',$Group);
$cat->AddDBParam('Status','Active');
$cat->AddDBParam('CD Type','CD');
$cat->AddDBParam('Task Date',$today.'...'.$nextdate);

$cat->AddSortParam('Task Date','ascend');

$catResult=$cat->FMFind();
foreach ($catResult['data'] as $key => $catData)

?>

<html>
<head>
<title>List CD Matters</title>
</head>

<body>

<?php 
foreach ($catResult['data'] as $key => $catData)
{
$Detail = $catData['Display Event Web Full'][0];
echo "<br>Detail ".$Detail;
} ?>
 
</body>
</html>

This returns the list of CD matters for the next 14 days.  However it still does not show the related data.

I get (for example)

Detail 28/08/2008 - [Matter 1234] Represented by - Hearing at 10:00 AM
Detail 28/08/2008 - [Matter 5678] Represented by - Hearing at 11:00 AM
Detail 28/08/2008 - [Matter 9874] Represented by - Hearing at 12:00 PM

I should get (for example)

Detail 28/08/2008 - Matter 1 Description [Matter 1234] Represented by Some Guy - Hearing at 10:00 AM
Detail 28/08/2008 - Matter 2 Description[Matter 5678] Represented by Webco Myhero - Hearing at 11:00 AM
Detail 28/08/2008 - Matter 3 Description[Matter 9874] Represented by Whatam Idoingwrong- Hearing at 12:00 PM

There is no detail for Matter Numbers::Matter Description or SC Assigned To::Full Name

Thanks for your on going assistance with this.

I have posted the entire page in case it is some thing in my FX.php setup rather than Filemaker database setup

Thanks again

[webko]

The only thing I can think of is because they are in different files - try assigning the web user into those files the same as the main one, and see what happens.

I haven't seen this before, but I do not use seperate files since V7, only other tables in the same file (which are therefore accessible by the web user and password)

[zippyaus]

OK, I have to be missing something fundamental here.

I have not set up php pages for all the other databases, created layouts with the fields I want to display and added those to the php page.

I can load each of these pages, Staff and Cases and see the information I require on each of them.  So I know that the password and access setup is working on those database.

I can see those fields and data on my primary database via the relationship in Filemaker, yet the data is not displayed in the php page displaying the related information.  In fact the with debug on, those fields placed on the layout do not return anything, not even the field name.

Anyone have a clue as to why this is happening.

ZP

[webko]

I really can't tell from here... Is it possible for you to bundle everything up and shoot it over to me - then I can install on my test machine and see what may or may not be happening??

[zippyaus]

Webco

Thanks for the offer, but because of the design of the databases it is not practical.

However, you did get me thinking.  So I created 3 new databases in a similar array.  Event, Case and Staff and related them in the same manner as my production system.  I have hosted them on the same server and low and behold the related data works.

There must be something dodgy with the databases.  I will try take them offline on the weekend and see if I can restart the server.  It might be a bit difficult as it is end of month.

Cheers and many thanks

ZP

[zippyaus]

So my son woke me at 6am this morning.  Perfect time for a server restart.  I have closed all the databases on the server and restarted it.

Unfortunately this has not helped.

Back to the drawingboard.

S