Lookup Customer Data in Invoice & Relationships

[Magistrate2020]

Hello,

I'm a very new user of FMP7 and I'm working on the most classical problem, invoices, I managed (with some difficulties) to build two databases (Invoices and Contact), now I wish to program the DB in such a way that in the invoices there's a popup menu where you can choose the customers data (not sure if I want to work field by field or if in the customers DB I want to make a field with all required data for invoicing) and I'm having serious problems with relationships/lookup, any suggestions on how should I proceed?

So far I relationed CustomerID in Invoices with CustomerID in Customers but I can't figure out how to obtain the desired effect; I know it's supposed to be something basic, but I'm a bit clueless.

As a side note, I tried to figure out how to do it by reading FM Bible but I guess it's a bit too advanced for me ATM, any help would be seriously appreciated; thanks in advance.

M.

[Søren Dyhr]

In invoicing is it correct that the address as well as prices in the item-line can't be live relations. Because an invoice is supposed to be a historic document ...the issue that shouldn't happen is if a change in the Contacts- or Items- details change all previous invoices adjusts accordingly ...because it defeats the object of being a historic document.

Here's some inspiration...

--sd

Invoice4.zip

[Magistrate2020]

Yes you are right, infact I don't need a live relation between "customer data" and "invoices" for the reason you suggested above.

I actually manage to relate "Client_ID" in "Invoices" with the same field in "Customers", and I then made twin fields in invoices that lookup values from "Customer" table and it works, if you enter the corresponding serial number in Invoices, but the fact is that I'd rather have a "Popup menu list" attached somehow to a dinamycally generated value list from the customer table; and I (almost) managed, at least graphically, my problem is that Even though I got the popuplist in invoice, selecting another customer thru it yelds no results.

Here's what I mean..

P.S. Soren, since this is the second time you're helping me, I begin to believe I owe you a beer ;)

Invoices_Customers.zip

[Søren Dyhr]

Well I changed you files in a workable manner, but you're stepping into a big debate, should realtions link with human readable/accessable data in the fields.

You can as I demonstrate it in your template link with values send to a popup. But this is only workable when the number of clients are few, and there is a problem if someone by accidentally change value via the pop ...the issue of being a historic document goes right down the drains.

This talks for letting the popup being in a global field next to a commit button, that transferes the value to the triggerfield for the lookup!!!

--sd

P.s. The beer sounds nice, but when will I be in Rome???

Invoicestweaked.zip

[Magistrate2020]

I'm looking at the tweaked file you attached, you're right about the "being editable" issue of data in the popup, but if I don't allow editing/adding fields on the popup menu and if the customer data are copied into invoice how can the end user mess up invoices/customer/datas in the database? By the way, which was my error? I'm not sure I figured out..

P.S. As for the beer, the offer is valid also if I come to Denmark in case you don't happen to be in Rome

P.P.S. If I understood correctly, your personal suggestion is that instead of a popup menu I use a keyfield with Cliente_ID, type a number and get the result from the related table "Clienti"?

Edited July 19, 2005 by Guest

[Magistrate2020]

I also noticed, that if you add a record to the clienti1 table, it doesn't automatically add to the popup menu in fatture1, is it normal or did I miss sth? *confused*

[Søren Dyhr]

No thats because the relation isn't live ... I misunderstood your question!!!

A way to do it is to import the values, with the update feature chosen when ever to button is pressed that leads you to the clientbase. But it carries a lot of syncing issues that needs to be thought throug, before implementing.

--sd

[Søren Dyhr]

If I understood correctly, your personal suggestion is that instead of a popup menu I use a keyfield with Cliente_ID, type a number and get the result from the related table "Clienti"?

Not nessersarily, but a temporary storage of the chosen that needs to be confirmed before overwriting.

--sd

Edited July 19, 2005 by Guest

[Magistrate2020]

By the way, how to make that relationship "live"?

[Søren Dyhr]

You can't use live relations for this issue on grounds I mentioned in this thread:

http://fmforums.com/forum/showtopic.php?tid/168027/

We talked about your lookups can't be allowed unless two actions have taken place the choice and the commitment to the action.

Since the actions needs to be so structured, could it be used. There should be a button next to the popup for commitment, but there is no theoretical limit to the scripting the button could have underneath.

What is required is a search for records made in the near future. That might contain alterations to old customers adresses or brand new clients. Very much to our luck is there provisions in the import dialog that allow us to both update as well as add new ones.

So it's the following invoice to a known client that perfoms the freshing including the previous clientdata exisiting nowhere else than in the invoice table yet.

So the requirement is a global field the popup works in, and button that both transfere the chosen keyvalue to the triggerfield as well as the performance of the freshing/syncing task at hand (if any??)

--sd

[Magistrate2020]

That does make sense, I'll do some experimenting with that button and update/commit feature and let you know.. Just a question, should the button "Commit Records/Requests"?

Edited July 20, 2005 by Guest

[Søren Dyhr]

No an entire script, making both the transfere and syncing of the previously entered.

--sd

[Magistrate2020]

mmm it starts going beyond my limited knowledge (scripts), maybe I should just revert back to the TYPE ID - GET THE DATA system; thank you anyway for your cooperation Soren ;)

[Søren Dyhr]

I'll make you a template tomorrow!!!

--sd

[Magistrate2020]

You're too kind, I don't know if it's ethic to overuse you this way... are you sure it's not a problem for you?

[Søren Dyhr]

Here's a suggestion for you investigate, there might be an issue or two in need for further tightning???

--sd

[Søren Dyhr]

Here's a suggestion for you investigate, there might be an issue or two in need for further tightning???

--sd

Invoice5.zip

Invoice5.zip