I'm guessing it's a Relationship problem

[Mickdn]

Instead of trying to explain my problem it will be easier to show (Attached file Automotive.zip).

Head to the Vehicles Layout, click New Service Button. Why won't the Service Layout update the fields unless I click on Service Date, pick a date and then click out. Been going over this for hours and it still eludes me.

Many Thanks

Automotive.zip

[LaRetta]

You have a mismatch of data types on the Vehicle ID. It is listed as number in vehicles 00042 but as text in Services 00042 and they can not match. If you remove the 000 from the 42, it will match Vehicles::VehicleID.

Or since you want the leading zeros, your Vehicles::VehicleID should be text and not number.

[LaRetta]

I was wrong. You DO have data mismatches on both your CustomerID and VehicleID and they need to be corrected as I've indicated but that does not resolve your issue (it did for me because when I changed the ID data-type, it caused the update). I then suspected index issues or corruption but recovery shows no problems and it still will not update the customer information as it should.

I've seen this before ... between customers, invoices and lineitems and it had to do with the order of field creation but I thought that issue was resolved when vs. 9 came out. I'm out of ideas, sorry. You can add a Refresh Window[flush cache] to the end but that is very harsh.

[Vaughan]

The data type mis-match between the key fields is not good but I think a bigger problem is that the customerid serial field is entering "00016" but it is a number field, so the leading zeros will probably be ignored. Which is the problem, it's dodgy.

As LaRetta suggested, clean up the data entered into the fields and match the type (I prefer number fields). IMHO leading zeros are a waste of space.

[Mickdn]

Thanks to LaRetta & Vaughan, leading zero's never to be seen again! Corrected the mis-matched fields, but still didn't solve the problem, parts of the Service layout were copied from a previous DB, so I'll try re-making Services from scratch.

[LaRetta]

Yes, I knew it was still breaking for you, Mick. I had even recreated the IDs as numbers and removed the values and re-input them. It works if you script it and you won't need Refresh Window[Flush cached join] which should be avoided at (almost) all costs. It doesn't work if you set that global field with a value but it works if you set a standard field:

Set Variable [ $newService ; Vehicles::VehicleID ]

Go To Layout [ Services ]

New Record/Request

Set Field [ Services::VehicleID ; $newService ]

Commit Records/Requests

Set Field [ Services::ServiceDate ; any date ]

Commit Records/Requests

Set Field [ Services::ServiceDate ; "" ]

Go To Field [ Services::ServiceNotes ]

So you can add the lines in blue (and it works for me). Those three lines do not stress your solution at all and it sure beats flushing the cache, although rebuild might be simpler and safer; I am not sure I would trust a file which exhibits this type of behavior but I confess to be overly-cautious on corruption issues.

It eats at me when I can't figure something out ...

[comment]

recovery shows no problems

Nevertheless, if you create a new layout of the Service table and point the script to there, it works, so...

[bcooney]

btw, the New Service button on the Services form has no way to capture the Vehicle ID. You cannot use the same script that you use on the Vehicle form for a new service. Perhaps, only allow new services from the vehicle form.

[comment]

the New Service button on the Services form has no way to capture the Vehicle ID.

And why not?

[Mickdn]

Tried remaking Services layout, as you guessed, didn't help. What I did find though was that if I used the "Customer Portal" to specify the Name & Address fields instead of "Customer" it all worked fine. I'm not sure if it's the best FileMaker practice to do it that way but everything seems to function ok or should I use LaRetta's extra 3 script steps?

[bcooney]

Stand corrected, comment. It'll go thru the relationship to grab the ID of the current record. I can't think without Anchor-Buoy.

[LaRetta]

It does not work for me either ... I tried duplicating existing services layout, deleting all objects and re-adding them. Then I tried creating a new layout and adding the fields. Nada also. If it works for Mac (creating a new layout) but not Windows 7 or Windows XP, it is version thing? Strange. Even adding Cartesian to global did not force refresh.

I cannot make it work even if I create a new Services table (I copy/pasted the existing one) and use new layout. Then I created new Service table from scratch and it still will not work - it will not display the customer information. Maybe the issue is in Vehicles or Customers and not Service at all...

I can not make a recommendation here; who am I? I could only say what I would do if it were my file: I would not use this file. I would rebuild. And several times during a day as you design, create an empty clone and set aside for the Master. Keep them dated and keep notes of the changes you make to the current working Master as you go. And if you discover corruption, go back to the master Prior to the corruption and start rebuilding forward again, using your notes to remind you of what you changed or use FMDiff (FMDiff.com) to see what has changed between two versions and search for hidden corruption. You can also google for FileMaker corruption to get some great links to help you decide what to do. Even though Recover says there are no problems with this file, Mick, we all know problems exist.

Others will probably have differing opinions and I hope you consider all ideas before choosing how you wish to resolve it. Forcing the update (by the script I provided or by using flush cache) will not solve the problem and you will continue to run into this issue in your file every time you want to use this relationship (which will be a LOT).

[comment]

FWIW, I am attaching the "corrected" file. However, I too would throw it away and revert to last good backup - or start from scratch.

Automotive2.zip

[LaRetta]

This works for me. Upon review, you didn't change anything except create a new layout? I did that too! I even created a new Services table! Anything else you did, Michael, to help me understand why it works for you?

Can I replicate step by step what you did to see if I can make it work (only for my own satisfaction to discount xplat from the mix and not to solve the problem itself)?

[comment]

Can I replicate step by step what you did

Starting from the Service Records layout:

Edit Layout > New Layout > Standard Form > [selected all the fields you see on the layout] >Default theme > Finish

[LaRetta]

Thank you for indulging me, Michael. It works for me also when I replicate what you did and place all fields. In my original test, I only placed the fields which were listed on the original service form and I manually placed them in same order as original. So on this file which worked with new layout and all fields, I then removed all other fields and changed the stacking order so they are in same order as original layout (I think) ... customer name first then the service fields. And it breaks. I've attached the file.

I think it suggests that it is not the layout but rather the field creation order and since I created a new service table and it still broke, I think the problem is in Vehicles; back to my original line of thinking. If this file breaks for you as well then I can at least drop the 'xplat' concerns.

I'm not sure what all I can learn from this but I would not be satisfied until I tried.

AutomotiveBREAK.zip

[comment]

I think it suggests that it is not the layout but rather the field creation order

It cannot be the field creation order, because that hasn't changed when I created the new layout.

I see a script that works purely on the data level - yet the results are different when a different layout (of the same TO) is used. That simply makes no sense - and the only conclusion I can draw is that the file is so f'ed up it should be burned with acid.

Oh, and your file does break for me as well.

[LaRetta]

LOL, Comment said, "is so f'ed up it should be burned with acid." I have no idea what brought about that symbolism but it is true. I would like to ask why it can't be a break at the table level instead of layouts; since changing order of fields on SAME layout works then breaks but I suppose it is moot discussion when dealing with funkiness.

Mick, are you getting this? We think your file was spawned from the other side and should be put down. And I am very sorry to give you this suggestion; it is not made lightly. We prefer to solve problems here and not suggest that you destroy your hard work. Although painful, it will be more so if you keep this file-from-the-dark-side.

Never mind ... you are correct ... it wouldn't WORK when you create a new layout if it was field-creation order.

[bruceR]

FWIW I see that if you merely repeat the set field step (after fixing the index issues) it behaves reliably.

automotive1.zip

[comment]

We prefer to solve problems here and not suggest that you destroy your hard work.

I absolutely agree. I am not happy about the diagnosis given here, since it's based purely on a failure to find a tangible cause. It's like convicting someone of murder on circumstantial evidence alone (how do you like them symbolisms?).

[Mickdn]

Did not realise my problem would turn into such a hot topic, many thanks to everybody who have spent their valuable time and effort into sorting it out. As suggested by LaRetta I'm going to start from scratch, no copying, no pasting, just a blank layout. Will let you know how it goes. PS: To LaRetta, do you really have a cat that looks like that?

[LaRetta]

Cat? Are you calling me a cat? I've been told it is a very good picture of me (brings out the color of my skin) although I wish I was smiling in it.

[LaRetta]

It's like convicting someone of murder on circumstantial evidence alone (how do you like them symbolisms?).

I do like it very much and it fits perfectly in this situation. If it were murder trial, it would be thrown out ... we have more than preponderance but not beyond reasonable doubt; we only have clear and convincing evidence.

I think my favorite about whether to continue using an obviously damaged file was from Fenton when he said (paraphrased) that using a damaged file was like unsafe sex. Odds are, you will be fine but ...

[Mickdn]

Finally got this thing working properly, created a new DB from scratch, just Tables, Fields and Relationships. I did cheat with the Scripts and imported them. Everything worked just as it should have. Then went back to my original and deleted the Customers Table. Then imported the Customers Table from the new DB I made, created the Relationships and added it to the original Customers Layout, fixed up the usual Table Missing error's in Fields & Scripts and it's still working. As for working out what caused the problem in the first place I have no idea, it just seems to have been a gremlin in the Customers Table.

Many thanks again to all who have helped.

PS: LaRetta, loverly picture, but I suggest you stay out of the sun.