The layout is based on the "Retailer" table occurrence, which has the record of a particular retailer.
There is a one field portal to a "Purchase Order" table (the table occurence is called rgi_PO and 'allow creation' is checked on the Purchase Order table side). This relationship is based on:
Retailer::kp_RetailerID = PO::kf_RetailerID
Retailer::gYear = PO::PO_Year
Retailer::gQuarter = PO::PO_Quarter
Retailer::z_gMDF = PO::PO_Type
(z_gMDF is a calculation to be the text of "MDF")
The user enters a PO number into this one field portal, which creates the PO record in the Purchase Order table.
Then there is another portal extending out from the rgi_PO table occurence called "rgi_pos_INVOICES", which is a table of invoice numbers that are connected to this Retailer and PO number. This relationship is based on:
rgi_POs::kf_ID_PO = rgi_pos_INVOICES::kf_POIDSerial
rgi_POs::kf_RetailerID = rgi_pos_INVOICES::kf_RetailerID
rgi_POs::z_MDFtype = rgi_pos_INVOICES::Type
kf_ID_PO is an automatically generated serial number, and rgi_pos_INVOICES::kf_POIDSerial has this serial number put into it when the invoice record is created.
That is farely intense.
I would Save as the database.
Open that that saved as copy on each computer and test on each hosting computers.
Keeping a good journal for the process.
Yes, I did that already. I zipped the database (which created the copy) and sent that file to the other user, who then opened it up and we tested it that way. Same thing - the person hosting it can see the invoices but the person connecting can't.
I just went through again and checked that all the data that the relationships are based on is correct, and it is. Edit: and the connected user CAN see the invoice record when they navigate to a table view of the invoice records, based on the invoice table.
I also made sure a refresh script (commit records, refresh window (flush cached join results)) was being run after an invoice had been created.
Are you using Instant Web Publishing to see the hosted file or connecting via a Filemaker Client?
Hildy, are you setting the global fields with a script when the file opens? Or are they typed in by the user?
It is being connected to by a Filemaker client, not IWP.
The gYear and gQuarter globals are set by a script at startup to the current quarter and year. The user then clicks on arrows to move forwards and backwards quarters via scripts.
The z_gMDF and z_MDF fields in the Retailer and Purchase Order table USED to be a calculation field returning the word "MDF" as text. However, once you mentioned that, I played around with making them text fields and setting them on startup. (This is a field that the user never sees or edits - it's just used to be able to make sure the invoices we see here are of the type "MDF" rather than some other ones in the database.)
With this change, I've got someone connected now and it seems to be working! Good news. I'll test a bit more, but perhaps the MDF global fields being calculations was the problem.
Can anyone think why? It seems very strange that I could see it but not connected users.
When it was a calculation field, you might have has it set as a number. You have to specify what your calculation returns, i.e., number, text, container, etc.
See below the text box where you type your calculation, there is a menu there.
Also, use a global field better than a calculation. It will be faster. Just set it at file open.
The calc was set to return text. But yes, fair point about setting the global field on open. It seems to have done the trick!
(Although I'm still curious to know why the calc field would have caused that particular issue.)
did you have indexing on? if not, this could be it too.
Global fields behave differently when the file is shared over the network instead of on a single user machine (or the host). That's probably why things behaved differently. Your script to set the values is now making sure both guest and host machines have the same values in the global field.
See this knowledge base article for more on the subject: http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=3604
Thanks for the link Phil. Yes, I understand that the global fields can be different in a multi-user session. This was why I made the field a calculation returning the text I wanted, figuring that it was the easiest way to make sure the global field has in it what I wanted. I don't understand why the relationship would behave differently with a connected user. Indeed, I checked to make sure that "mdf" was appearing in the global fields from the connected user point and view when they were still calculations, and it did appear. So the data was there, but for some reason it meant the relationship wasn't working properly.
Deltatango - I checked an older version of the database, and when the field was a calculation, I did have it set to being a global as well, so there was no option for indexing. Perhaps this is a conflict, being global and a calculation.
So the problem seems to lie with the field being a calculation, but if that was the problem, and given that for the connected user the word 'mdf' DID appear in the calculation field, then I don't understand why it would work for the person hosting the database - shouldn't it not have worked for them too?
I'm happy it's fixed, but I'd like to understand why.
Thanks for everyone's input.