Field for Company Code lookup and fill in compnay address field

[Bot Roda]

I have this old invoice form I set up a very long time ago using one of the earlier versions of FM. Somehow my Company Code field and connected address field got "broken". When I type in the initials for a given company then hit tab it prompts me to lookup previous year version of FM, and generally I use last year's record since most of my clients have been with me for many many years. Well, as a result of getting messed up instead of the company's address being filled in the address field it's repeating the initials of the company code. I've included screenshots showing the layout for the invoice form, then the specify field box, then the "manage database for 23 client list", then I hit the options button to open the "for company code" field. Then hit "specify" to open "Lookup for company code" field.

No tsure where to go with this or what to do to remedy. Should I be working with the "address" field instead of the Company Code field for starters?

Thanks in advance for any help rendered.

[Tpaairman]

It looks like it's finding the info in a related table.  Since this started in an old version of FMP, I would go check the relation and make sure something didn't get lost in the update. 

[Bot Roda]

Thanks for your response Tpaairman 🙂 should I check the relation in the older version, which may be a problem as I no longer have that version as it won't run on my current Mac OS; Catalina. I tried to open it and got the message that it wasn't compatible. If I check in the current version of FM (I'm running v16) exactly where and how would I do that...sorry for inexperience. Thanks again.

[Tpaairman]

No need to apologize.  That's the point of asking questions.

You need to verify this in whatever version of Filemaker you are using.  Any old version is out of the picture.

From what I can see here, it's looking like you have a related table.  So in other words, you have one table for your invoices, and another for your customers.  In each table there has to be a field that connects to two.  It appears that both fields are called company code, which is OK.  What you need to do is go to define database, and you will see three tabs at the top of the window that pops up.  One is tables, the next is fields, and the last is relationships.  Click on relationships, and you will see all of your tables as boxes on there.  Find these two tables - invoices and clients, and see if there is a line with a small box connecting the two.  Double click on that little box and make sure that the two fields equal each other.

But, taking a step back for a second, I'm a little confused about having the looked up value in there, given how you said this is supposed to work.  What the looked up value does is enters info as soon as the record is created.  So once you create a new record, then click the field to enter the initials, you area already past that point of it looking anything up.  Was that set up since you updated your version of Filemaker?

[comment]

3 hours ago, Tpaairman said:

What the looked up value does is enters info as soon as the record is created.  So once you create a new record, then click the field to enter the initials, you area already past that point of it looking anything up. 

That's simply not true. A lookup occurs only when the matchfield is populated - and re-occurs every time the matchfield is modified.

 

@Bot Roda

I am afraid I don't understand the situation you describe at all. In an effort to move this forward, I am attaching a minimal demo of how a lookup should be set up. Hopefully you will be able to adapt this to your situation.

 

 

lookup.fmp12

Edited April 7, 2023 by comment

[Bot Roda]

Thanks to both of you for your help so far. Maybe to help explain it better, the way I had initially set this up many, many versions ago and the way it used to work was upon creation of a new record, I tabbed to the Company Code field and I entered the specific Company Code I had assigned for each clients company that they worked for, upon doing that it automatically populated the address field with that client's company address and ALSO populated the Company name field with the company's full name; all based on the acronym I used for that client's code. Set up like that all I had to do was enter a few initials in the Company Code field and it filled out the other two fields sparing me having to type all that info in.  Somewhere along the way, whatever happened, it now populates the address field with the company code and not the address. However, it does correctly fill in the company name field with the company name so it's only the address field as it relates to the company code field that, to me, is broken.

 

Many thanks again for your help with this and more importantly for your time. Much appreciated.

[comment]

I am afraid that's still not clear. Upon creation of a new record in which table? And from which table is the data supposed to be looked up? And how is the relationship between these two tables defined? 

15 minutes ago, Bot Roda said:

it now populates the address field with the company code and not the address.

So define the target field's lookup to copy value from the address field instead of from the company code field. That should be easy enough - unless I am missing something (which is very likely, given what I said in the beginning ).

 

[Bot Roda]

Thank you for your reply. I got it fixed thanks to your solution. It was a simple matter as you suggested to copy the value from the address field instead of the company code. Thanks again for yours and Tpaairman's help and time! Very much appreciated...this was driving me crazy 🙂 Have a great Easter weekend.