FM7 Number 1 related record not showing

[SlimJim]

I have a numbered client table - starting at 1 - and an invoice entry form in which I choose a client number and then name and address data is filled in from the client table. This all works perfectly well except for client number 1 whose data is not displayed. If I change his number to anything larger it is displayed but I do not want to change a client ID at this time. Equally if I change another clients ID to 1 their details do not appear on the invoice form.

Does anybody have any idea what is going on here?

[transpower]

Check the graphical tables relationship. Be sure there is an arrow going from the client table to the invoice entry table, connecting the appropriate fields.

Then be sure that the name and address data are calculating correctly.

[Ender]

SlimJim,

It sounds like your related table does not have a record that has a Client ID = 1.

[MoonShadow]

SlimJim, you mentioned table. Are you using 7? If so, is it possible that you have specified something other than = in that relationship or could it be based upon more than one field?

If the others show, 1 should show unless it is somehow being excluded; or unless there is funky data in that related record's field or as Mike indicated unless it doesn't exist. And maybe the 1 record exists but its address is just blank. Just some ideas ...

[SlimJim]

Many thanks for the responses. I am using FM 7 and in the process of converting a solution from FM 3. My clients table does have an entry with ID = 1 and full address information. There are in fact two relationships - all of which work with all other numbers - one goes through a quotation table to the invoice via a quote ID and the other is direct to invoices to allow for one client contracting the work and onother paying for it. All relationships so far involve "=" only. I am converting the solution manually (only 9 tables involved and I am trying to simplify as I go) and have imported the data via csv files. Maybe that is the problem.

I am completly out of ideas for this and although I can resolve this by reassigning a client ID I find that unsatisfactory

[-Queue-]

Are the fields to be related of the same type (number, text, etc.)?

[ESpringer]

If you haven't do so yet: check to see whether the related (address) record with ID 1 has a space or other junk in the field interfering with the match, and likewise with the 1 in the home table.

[Ugo DI LUCA]

Hi,

There are in fact two relationships - all of which work with all other numbers - one goes through a quotation table to the invoice via a quote ID and the other is direct to invoices to allow for one client contracting the work and onother paying for it.

Hmm...

May we see the relationship graph for this ?

[SlimJim]

This part of the relationship graph involves two copies of the Clients Table labelled "Clients" and Payers" a "Quote" table and an "Invoices" table each table has a unique ID field (Client_Number; Quote_Number; Invoice_Number) each Quote is for a Specific Client identified by number, Each invoice is related to a specific job by Quote Number and paid for a by a client (not necessarily the client from the quote) identified by Client_Number

The relationships are:

[Clients:Client_Number] =

[Ugo DI LUCA]

Hi,

Well, with v7, it has become a bit difficult to analyze a relationship, but I would think your problem lies in the Graph you explained.

I'd assume the lookup doesn't work in Invoice.

Was this Invoice paid by the same Customer in Quote ?

I'm more used to work with LineItems, where several items may be invoiced at a time, so I may be wrong.

If all quotes were to be invoiced to the same customer, IMO, the InvoiceID in Invoice would match an InvoiceID in the Quote Table (not a QuoteNumber), this Quote being related to the Customer Table through the CustomerID.

If not all quotes are to be ivoiced, then you'd need either 2 IDs (PayerID and CustomerID) in the Invoice File.

The Customer ID would relate to the CustomerID in Customer Table back the "natural" Path described above, through the Quote Table.

The PayerID would relate back to the CustomerID through another relationship Invoice::PayerID -> Customer|Payer::CustomerID.

I can't test to see if this explain why your lookup fail though, as I don't have 7 right now.

[SlimJim]

I think you may be right that the problem lies here. I had this working perfectly well with FM 3 except that the connection to the customer via the quote number had to be done by a lookup since the relationships in FM3 were not transitive. I thought that having transitivity in relationships would enable me to simplify the method and not have to use a lookup.

For the record my invoices do have line items but each invoice is restricted to one job (quote) and paid by one customer. Quotes have zero or more invoices attached to them but only one contracting client. It has been an interesting and ongoing experience jumping from version 3 to version 7 but for me none of the in-between versions offered enough extra to make me want to take the jump. I still think it will be worthwhile but I am not sure as there are other issues with version 7 which make me want to go back to FM 3.

[Ugo DI LUCA]

Hi,

Definitely stay with 7 now that you've made the first step in....

"For the record my invoices do have line items but each invoice is restricted to one job (quote) and paid by one customer. Quotes have zero or more invoices attached to them but only one contracting client."

1. Your Invoice doesn't have Line Items, as it is a One to One relationship to the Quote file.

2. You can't reference the QuoteID in Invoice anymore as it doesn't rely to all items of a Quote (some would be invoiced, some not, some others posted to separate Invoices).

3. Nor you can reference an Invoice# in a Quote

This would change the settings I described as there's now a Many to Many relationship from Quotes to Invoices.

4. You now need a Join Table from Quote to Invoice where you'd link an Invoice to a Quote item.

5. You'd now get the Customer Info looked up (or displayed in a related field) by using the reverse path.

Invoice::Invoice#-->Join Table::Invoice# | Join Table::Quote#-->Quote::Quote# | Quote::CustomerID-->Customer:CustomerID

If you need help, I'm currently sucesfully (so far) playing with a v7 solution with Quotes, Customer Order, Deliveries and Invoices, for the "Sales" part of it, where :

1- Deliveries to Invoice

An Invoice may group Many Deliveries, but One delivery cannot be splitted into Many invoices

---------> One To Many

2- Orders to Deliveries

One Delivery may group Many Orders, and One Order can be splitted into Many Deliveries.

A delivery isn't necessarilly related to a previous module (items in stock)

--------> Many To Many

3. Quotes To Orders

One Order may group Many Quotes, and One Quote can be splitted into Many Orders.

An Order isn't necessarilly related to a previous Quote.

--------> Many To Many

4. Quotes To Deliveries (Direct sale from available stock)

One Delivery may group Many Quotes, and One Quote can be splitted into Many Deliveries.

Happens when some items previously quoted are in stock. No need to build a Customer Order

--------> Many To Many

So let me know if you did it.

BTW, I had this situation where an Order was invoiced to another Customer. Thanks for the reminder, I might look at this option too.

Cheers. Let us know how it goes now.

[SlimJim]

This is an interesting and enlightening discussion. In mathematical terms all the relationships that I have mentioned define a one-to-many mapping from a proper subset of the source table onto the whole destination table. In my descriptions I regard the left-hand table as the source table (in the relationship graph it is not clear cut due to the moveable nature of the representations of the tables). However, I am not sure whether I am making progress on my original question (I am making progress on understanding FM7 I think!). I have tried all the various suggestions and still have the problem of customer 1 details not showing. It would seem to be related to the relationship graph and maybe I am doing something really stupid (it wouldn't be the first time) so I am going to make a careful analysis of what I have done so far. If I find the solution I will come back and I will still be scouring these posts for suggestions.

[-Queue-]

You can attach a zipped or stuffed copy of the problem files, if you can't resolve the issue.

[SlimJim]

A good idea but I have sensitive data so I decided to make a clone copy and input a small amount of sample data. The problem disappeared!

So I cleared out the sample data and re-imported the original data and again no problem. The conclusion seems to be that there was a problem in my original importing of the data. I still have no idea what it was as the data did not show any obvious corruption but I think it can be put down to some form of data corruption which was suggested by ESpringer in an earlier post

[-Queue-]

Are there any auto-enter options that may not have been calculated during previous imports that could have effected your relationship?

[SlimJim]

No autoenter options. I am still at a very early stage in the transfer of the solution and am trying to make sure that I have the basics right. After finding that the problem disappeared when playing with a clone I have returned to the original, deleted all the data and re-imported it and there is still no problem. I'm afraid at this point I cannot reproduce the original problem.

[-Queue-]

Weird. Well, at least you got it working.