"magical button" on a database that generates and autofills a new record on a DIFFERENT database

[kuroneko_sama]

I am new here so please be patient.

I have been assigned the task of creating a "button" in our quotes database that will open a new record in our invoicing database and autofill the fields with the quote data.

In my first attempt, I created a new layout that autofills the information from a layout in the quotes database I titled "invoice"(the quotes database and invoice database are nearly identicle in layout anyway and the fields autofill among those layouts creating what I had thought would be an ideal solution...).

After some colorful criticism about following task instructions, My boss had been very clear about that NOT being what he wanted. He then explained specifically that he wants a "button" that opens a new record in the invoicing database and autofills the information shown on that current quote where the "button" is located (this information has to be entered manually otherwise).

Now, I can copy formulas and such from other databases we have and duplicate some reults but as of right now I am unaware of the ability to have a button generate a new record on a sperate database that is autofilled from the record in which the button is pressed.

Since modification has to be done on existing databases, I cannot generate a new database from scratch (also would have been ideal for me). it simply needs to look the same with a "button" added to our quotes database.

Now I am very familiar with MS office and excel and have worked with formulas and conditonal formatting, etc. on other programs. unfortunately, I have only been introduced to filemaker within the past year or so and although I can generate records and such, I have not been required to generate formulas or do any editing to the layouts themselves before now.

If anyone out there has any ideas, any help would be greatly appreicated.

Edited February 9, 2010 by Guest
The Topic: <a href="http://fmforums.com/forum/showtopic.php?tid/213320">"magical button" on a database that generates and autofills a new record on a DIFFERENT database</a> was moved from <a href="http://fmforums.com/forum/showforum.php?fid/35"

[bcooney]

Hello and welcome to the Forums!

Some questions about what you have. Do you have multiple FM files (separate icons for the Invoice database vs the Quote database) or one file with multiple tables? You mention two databases, but that's not specific enough.

If you add a foreign Quote ID to the Invoice database, you can use the Lookup entry option to look back at the Quote through a relationship from Invoice to Quote by Quote ID. How to populate the foreign quote id is a matter of a simple technique, but I need to know what tables you have to suggest the most efficient approach.

There are two approaches for the line items. Do you have a separate table for Quote Line Items related to the parent Quote? Do you have a separate table for Invoice Line Items related to the parent Invoice record?

[kuroneko_sama]

our company uses many seperate FM files on filemaker

each one is its own database.

each database does have several layouts associated to them as well.

information would include "bill to" and "ship to" information (reffering to the adresses generated from the contact list database)

the product quantity, description, item price and total price (generated from our inventory database)

the total (calculated by formula in the table)

i will attempt to insert some pictures tomorrow (i am currently on my personal laptop and not connected to the server)

in order to do what this "button" is supposed to (our current method)....

I am in the quotes database and now want to make an invoice

i would select "open recent" and select the invoicing database file

then i would need to right click on one of the entries and select "new record" (on the invoice database.) creating a blank invoice sheet.

I would then need to manually copy and paste over the information from the quotes database onto the invoice database.

we basically need a button to perform this task automatically.

they are not different layouts in the same file. they are completely different files that share much of the same information.

we do have some fields that will autofill from different files (for instance, typing in an "id number" will autofill address information from our contact list onto a shipping list... or typing in a product number will fill out the invoice description and price with information retrieved from the inventory database)

I hope this helps in explaining things

[bcooney]

Is there a reason that your company has spread out their system into multiple FM files?

Btw, let's review some basics. FM Files can have multiple tables. Each table is represented on the relationship graph by one or more table occurrences. Table occurrences are "views" into your table, and layouts are based on table occurrences. One table can have many layouts. So, telling me how many layouts you have doesn't tell me how many tables you have in your system.

You really should combine the quote and invoice files. Is there any compelling reason why not? However, you can place a table occurrence for Invoices in your Quotes relationship graph using an External File Reference. Then you can relate the two tables, Quote and Invoice.

You haven't answered my question regarding the actual tables you have in each file. Look at Manage Database in each.

[kuroneko_sama]

Regarding combining the quotes and invoices...

My first attempt at fixing this problem was to simply have a new layout in the quotes database labeled as "invoice" and that way all those fields would have just transfered between layouts (or tables as your refer).

My boss berrated for an hour regarding why he thought it was a terrible idea (something about the quote number not matching the invoice number and how he wanted a magical button to) and how he specifically asked for a button to open a new record and if i cant do what is asked of me...yadda yadda... advancement in the company limited to willingness to do what is asked...etc.

So no, I cant combine them which was my first attempt and still my suggestion.

It is my guess this is his magical way to teach me new skills without actually teaching me.

I know there are upwards of 30-50 tables when you look into the manage database section of each file

there is only one layout to the quotes database

there are 5 layouts in the invoicing database

im not concerned about merging between the layouts in the same database. I just dont know how it can create and fill a new record from a seperate database file.

I will get some screen shots of a blank invoice

and a blank quote and ill better detail with

pictures what i am trying to accomplish and what i have to work with.

i personally would have rather built them from scratch and combined those particular databases.

[Søren Dyhr]

My boss berrated for an hour regarding why he thought it was a terrible idea (something about the quote number not matching the invoice number and how he wanted a magical button to)

This is not a genuine concern, you can have as many keyfields you might wish for in the same record, so what might originate from the itemlines of a qoute could easily be the same data tied to another tables via relations, say an invoice ....

He isn't database developer obviously and have perhaps even a hidden agenda to script till he drops, now the inherent synchronization issues the spaghetti code method, is attempted solved via normalization of database structure when speaking databases, while the object model have attempted to nail similar consistency issues in straightforward coding ... while his mindset probably is dictated by his spreadsheet macro skills should he not prevail in squeezing these down on a different beast as databases in reality is.

--sd

[bcooney]

As Søren says in his wonderful Dutch-flavored English, your boss doesn't understand good database design at all and might just be stuck in Excel think. As for berating you for an hour, well...

It's a horrible position to be in, and I'll try to help you as much as I can. Perhaps if we give him his "magic button," he'll lay off for a while so that you can start a rewrite.

Yes, post a screen shot IN LAYOUT MODE of the quote form and invoice form. I'll be shocked if anything is "right" about this system.

Edited February 10, 2010 by Guest

[kuroneko_sama]

as i said (and you will see when i put up the pics...once i figure out how to attach them to the post) i had added a layout in the quotes field titled "invoice" and i had the fields that were the same autofill and the fields that were different remain different.

im under the impression he wants the ability to send multiple invoices offthe same quote and keep the records for later and other stuff like that

he claims he could probably do it himself in about 10 minutes but wants me to learn how to do it myself so i can be more useful

(i was a mechanical engineering major thatwas hired as an electrical tehcnician that now does everything from pcb soldering to software activation support, wiring diagrams to manuals, to shipping, to inventory/BOM database maintainence (on excel) and now filemaker editor.

as the signature implies, i am being turned into a human swiss army knife. but i dont mind so much as i am capable of doing the tasks... i consider it job security in this economy (i have a wife and 2 kids to support)

just gotta do the job of 5 people for the paycheck of one to get by nowadays

now to get them pictures posted....

[Søren Dyhr]

m under the impression he wants the ability to send multiple invoices offthe same quote and keep the records for later and other stuff like that

And??? Read up on the implementation of many2many relational structures... it's absolutely not an issue to share the same line items between a handfull of records.

Could you have a system where there at some point have been paid tribute to repeating fields?

--sd

[kuroneko_sama]

Unfortunately I would love to do this a number of ways that would make much more sense and I'm sure he has is reasons...

In his exact wording... I am not to do things my way, I am to do things the way they are asked to do them. that is how people get fired.

so for now i must create a "magical button" that apparently he and the guy who wrote the databases in the first place could do in a matter of minutes.

im sure this is more of a test to make sure i can see this through than an actual task, in either case i have no way around doing exactly that... making a "magical button"

here are some pics a new record of both databases

standard view and layout mode....

[kuroneko_sama]

here is the manage database list as well as the 24 different databases i work with(that could probably be consolidated...but we wont go there again)

[bcooney]

Since my last post, I put together a demo. It separates Quotes and Invoices into two FM files. It only allows one Invoice per Quote, but we can change that.

Take a look.

I will study your screen shots.

PS: I haven't built a multi-file FM system since FM7.

Sample_System.zip

[kuroneko_sama]

so as i stated before i was able to have a different layout in the quotes database called invoice that autofilled the "like fields" and i think i can link the autofill from a seperate database. my main complication is actually creating the button that will open a new record in a seperate database and then autofill those fields

[bcooney]

Soren, you are right! Repeating fields. This was obviously a FM6 version opened in FM7. What a mess.

[mr_vodka]

Your boss is one of those huff and puffers. Big bloated head that think they can do anything in 10 min. ( Encountered many of these personalities in my lifetime ) If he could do it in 10 min then why bother "teaching you" only to chastise you.

I agree with Barbara. You should give him what he wants for now to build trust. However, after it is done you should recommend "enhancing" it to the right way.

[Søren Dyhr]

Well it pretty much looks like repeating fields to me! Read what Bruce says about it!

http://fmforums.com/forum/showpost.php?post/349318/

...so this isn't going to be done in any way half decent!!! So you aim must be to avoid loops inside loops by all means... and there employing this custom function might get you somewhere in that respect:

http://www.briandunning.com/cf/250

By using it as a way to gather a series of script paramters...

--sd

[kuroneko_sama]

Your boss is one of those huff and puffers. Big bloated head that think they can do anything in 10 min. ( Encountered many of these personalities in my lifetime ) If he could do it in 10 min then why bother "teaching you" only to chastise you.

I agree with Barbara. You should give him what he wants for now to build trust. However, after it is done you should recommend "enhancing" it to the right way.

i fully agree with everything all of you have said. I have no doubt he could do this (i do many manythingsthat take me like 20 seconds to do just because he doesnt want to walk to a printer) but thats what im paid for.

my intention is to create an "ultimate database" that basically looks exactly the same using a TON less files and much fewer redundant formulas. but its all about hoop jumping here and its all about everything being accomplished in due time... im sure he will task me with rebuilding the entire thing before not too long... but he will have (to quote mr. guthrie) "27 8x10 glossy photos with circles and arrows and a paragraph on the back of each one describing what each one is" to instruct me with.

he rather me be a programmable machine than a free thinking designer... but hey, im a hardware technician that just gets assigned software tasks... or so im led to believe.

i will be happy to view the links you tossed up here and i greatly appreciate it. i actiually do (believe it or not) enjoy these kinds of off track tasks and i do like entering formulas and stuff (i LOVE making Excel Spreadsheets... and im not even being sarchastic)

someday maybe ill have developed an interesting method or custom setup that youll find useful.

and maybe someone in my position will have a question thati can confidently answer....time will tell

thanks for all the help and as i said i will be looking further into this, as well as continuing on this posting with more questions and status updates (ill be SOOO happy when i make this "magic button" happen)

[bcooney]

You certainly have the right attitude. You're dealing with a system design pre-FM7. Then, FM did not support multiple tables per file (hence the plethora of files you've inherited). Also, the developer did not make use of child tables, but rather used repeating fields. If you look at my demo, you'll see that each quote has a child table, "quote line items." Each line item is a record in the child table. A portal object is used to display the quote's child quote line items records. Why is this better than repeating fields? Many reasons, but most of all, there are no upper limits on quote items per quote. I didn't include a layout designed for printing a quote, and if that'll help, I'll add one to the demo. It would be based on the Quote Line Item table.

BTW, Custom Functions can only be defined with FM Advanced.

Just for kicks, I'd love to see a screen shot of the Relationship Graph--but then this snow has me in a bit of a mood.

[Lee Smith]

Hi kuroneko_sama,

I feel your pain. Many of us have been in the exact same position as you.

I'm happy to see that you are being helped by some of our finest, so I won't muddy the water.

However you might find this thread enjoyable to read Does anyone takes us seriously?

Good luck with your project.

Lee

[bcooney]

Many thanks, Lee.

[Søren Dyhr]

Alright I toyed a bit with the what could be done... I've attached a template showing a way to approach it. There isn't two files but as such can another file be dealt with by referencing it, which already might exist as common passwords... but with version 7 and upwards have it been possible to make layouts with field originating from other files, as long as they're listed in the references.

The reason for this approach is that it simplified the scripting to keep all within same file ... now I've just discovered that you are on an earlier version not facilitating indirection, so the setting is going to a bit more tiresome:

http://sixfriedrice.com/wp/set-field-by-name-exposed/

But take a look at the attached file and the scripting...

--sd

magic.zip

[kuroneko_sama]

So am I required to have filemaker advanced in order to have a "magical button" or is this within the ability of filemaker pro 9?

(I'm sure he would be thrilled if I told him I'd require an upgrade considering he is under the impression it can "easily" be done as-is)

[Søren Dyhr]

You would need Advanced to implement the custom functions used, but I could manage the functionality with previous versions though, eliminating the need for advanced... it just makes it easier. The use of the FM10 direction function shouldn't be a problem either - if you're prepared to give a more detailed look into the inner secrets of your file ... I just used present functions as they were needed without much consideration to backwards compatibility...

--sd

[kuroneko_sama]

Barbara,

I did like what I saw in that demo and im sure thats on the same lines of what he wants. I'm sure I could, with a little time poking about, impliment something similar into what im doing.

I also appreciated seeing that I am not alone in the "boss tosses it at a generic employee so he doesnt need to pay a pro" category. (Though I am a hardware guy among an ofice of computer programmers and software writers) I just made the mistake of making really good spreadsheets on Excel and mentioning that I enjoy that sort of thing (and yes I am extremely aware that this is a much different system than Excel).

I noticed there are training sessions from "the support group" in Boston.

I was wondering if you would reccomend it for someone like me (I could probably convince my boss to cover it). or is it like Excel in which in due time you can self-teach reasonably enough to cover most of your bases.

have any of you taken it?

Even if it isnt covered by my boss, is it something I should consider out of pocket?

[bcooney]

I would recommend a class (the Support Group is very good), but only if the boss pays. Otherwise, you can buy training materials thru FMI. They are also online videos thru FMI. And...many good books (Coffey, Cologon, etc).

Spend the time learning the basics. Many people just dive in with little understand of the core concepts, only to find that they can't produce any of the reports they require bcs their data structure is all wrong.

Study the demo. Feel free to ask any questions.

[Jesper Søholm Biberkopf]

[i just realised I have overlooked some of the posts in this thread. But having spent a good hour on fine tuning this answer, I'll just leave it, hoping it will help somebody some day...]

Hi Kuroneko:

You sound like you could use a quick and dirty solution now, and then you might want to dig into the finer database theory later.

First of all. I'm not sure from the pictures whether you are running FileMaker 7 or 8. So I don't know if your system supports 'script variables', so I will sketch an old fashioned solution.

You need to move the quote data to a new empty invoice. For this purpose you build a new relationship called 'GetQuoteData' in your invoice db:

The primary key is a new global field in your invoice db: QNUMBER_g

The foreign key is the QNUMBER in your quotes db, which must be a unique.

Make sure new QNUMBER_g field has the same data type (e.g. 'number') as the QNUMBER field.

Now you need to go to the Invoice file and make a script called "Make Invoice from Quote" that looks something like this:

SetField [ QNUMBER_g ; get(ScriptParameter) ]

GoToBrowseMode

Commit Record

GoToLayout [ Invoice layout ]

NewRecord

SetField [ CustomerName ; GetQuoteData::CustomerName ]

SetField [ CustomerAddress ; GetQuoteData::CustomerAddress ]

... and you simply make as many SetField script steps that's needed to fill in all the fields that corresponds between the two files, and end it with a:

Commit Record

Now you go back to the Quote file and make a new script called "Make Quote into Invoice". This script consists of one script step:

Perform Script [ "Make Invoice from Quote" (in quote file) ; parameter: QNUMBER ]

This script calls your new script in the invoice file WITH A SCRIPT PARAMETER. And the value that goes in the script parameter is naturally our QNUMBER.

Having done this script, you produce your magic button and attach your "Make Invoice from Quote"-script to the button.

In this way we get that quote number parsed to the global key field in the invoice file, thus creating the connection between the new invoice and the quote.

If your boss was a little visionary, he would like to be able to track that particular invoice back to that particular quote. And if he likes that, you should make the field a regular field instead of a global field.

This can be helpful even if he wants to make more invoices from the same quote.

Now, this is a fairly basic outline of the principles. You can add all kinds of controls and tests along the line to ensure data integrity and avoid human errors.

For one, you might want to include a "Show Custom Dialog" + and "If (get(lastmessagechoice) = ...) to top of the button script so it doesn't fire unintensionally, if you happen to push it.

Sorry, if it's a bit basic and long, but when I don't know your skills I thought it best to make a full cooking recipe.

Hope this can help you keep your job ;-) ...

Edited February 18, 2010 by Guest

[kuroneko_sama]

so now i can finally make a button and get the script going to open a new record.... i even got a prompt to ask me if i wish to generate an invoice, and i got the cancel button to work (i had some issues with the scripting of the custom dialog)

my problem now is i still cant seem to figure out how to getthe fields to autofill from that quote. i dont get the whole global field vs regular field and the whole parameter thing.

following the instructions i was still unable to get the fields to fill out.

but now i can comfortably write a script (so long as i dont have to toss in the calculations within brackets) but im still pretty lost when it comes to managing database fields and im iffy on the relationships

and i will repeat...this is filemaker pro 9

[Søren Dyhr]

SetField [ QNUMBER_g ; get(ScriptParameter) ]

GoToBrowseMode

Commit Record

GoToLayout [ Invoice layout ]

NewRecord

SetField [ CustomerName ; GetQuoteData::CustomerName ]

SetField [ CustomerAddress ; GetQuoteData::CustomerAddress ]

Jesper if you're going to use a global fields anyway, might you consider this:

http://fmforums.com/forum/showpost.php?post/355447/

--sd