how to auto-insert into 2 related DBs?

[Leb i Sol]

Hello People!

Here is my set of relationship questions I hope you can help:

-------Reports.fp5---------------(master)

ReportID

PO

..

___________________________________

------Items.fp5------------------(related to Reports.fp5

ReportID

PO

...

___________________________________

I would like to have Items.fp5 to automatically insert the same PO from Reports.fp5 whenever I insert PO number into Reports.fp5!The reason being is that 1 PO can have many additional Items that I will be adding later!

How to do this? Am I on the right track at ALL?

Shoud PO field be a Auto- LookedUp or calculated value?:

Thank you guys

[willi]

Hi,

please give some more infos about the entries of Items or reports.

Is the a 1:n relation between report and items or the other way (then you need a relation with ReportID, i think you have it).

If is this so: you need the next relation between Itelms and POs.

Or would I be on the wrong way?

Willi

[Ugo DI LUCA]

What about using PO as a related field from Report.fp5 into Items.fp5 ?

[Leb i Sol]

sorry I made a mistake

--Items.fp5--

ItemID

PO

------------

and

--Reports.fp5---

ReportID

PO

---------------

I am not sure about the realtionships my slef

what I intend to do is say: Items have a PO number that is just a number entered but that PO number can have an dditional -many items that should be stored in Items.fp5.

I am a bit confused since I do not know how to "check on types of relationships in FM ( i know in Access) but what I made was ITESMtoREPORTS relationship... and I do not even know what I made....One-to-one or one-to-many..which direstion do they flow?!

help

....danke fiel

thank you guys

------------

[Leb i Sol]

I tried something like that but then the Auto-Insert function doesn't work?! I am new to FM so please bare with me...I do understand some concepts of relationships since I did some work in Access but it had a visual tool for relationships and all.....so what you are saying to to drop the somenameID fields and just use PO and PO in both tables?

[Ugo DI LUCA]

Create a field call PO, drop it on the layout. Then double click into the field in layout mode and change its relationship to be ::ItemstoReport.

Now, it could be interresting to know what your Item file is (Is it a Line Item (join table) or a Product file ?

[Leb i Sol]

here is an image of the tables...please take a look...this is jsut an idea....meaning any suggestion on changing things arround is More than welcome

My goal is to end up with these results:

Location Name | QTYtotal | POTotal|

--------------------------------------

ONTARIO | 3| $1200

LA | 2 | $800

NY | 5 |$1000

soo....

[Ugo DI LUCA]

Hi,

This is cleare now.

Then Location.fp5 is your "Product file", Report.fp5 is your "PO File" and Items.fp5 is your "Line Items" file.

There is a one to many relationship from "Locations" to "Reports", that's why you need a join file. Your "Item.fp5" File.

This Item.fp5 file, being a join file, would not be seen by anybody. It's the main file of your system but nobody should work directly on it (at least this is how I work). But also being a join file, this is the file that hold allthe informations, so all "reports", prints,... would be set from this file.

Now, go into your Report.fp5 and create a portal linked to the Item.fp5 using the PO#. Set this poral to allow creation of related records, and you will start having a real relational database, entering the locations to the Item.fp5 using this Portal.

Locations would be choosen from a value list of all locations from the Location.fp5 (use the format value list, use value from file Locations).

Now, if your goal is to see who purchased Ontario, make a portal into the Location.fp5 and use a relationship to the Item file to have a list of all PO'S that has Ontario in it. With some more time, you will even find a way to filter the whole Location file to see any records, customers, PO, invoice,...

If you need some more steps, I'm right behind you ...

[Ugo DI LUCA]

Edited.My God 4 dupes in a row

[Leb i Sol]

ok let me try few things before any questions come up....

I do need people to work on Items.fp5 because I would have people insert data into Reports and then IF any aditional itmes go with the same PO I would have the users(insert for them) Update the Items.fp5 file assuming that PO got inserted automatically when they were typeing it into Reports.fp5..makes sense?

by the way this all is intended for CDML use not the by use of FM

[Leb i Sol]

ok..I GIVE Up on this relationship thing.....sorry guys but thanx for the help

it doesn't do crap for me! Portals do not call up anything and all the Sum fields are wrong.....

I follwed some examples from this forum but mostly pepople here make some invoices and what not...I just need some numbers added and to cut down on duplicates....this is what got me here in the 1st place

...oh why oh why did I ever pick up FileMaker...

here is the attch. of how porely it looks now

I guess back to a pen and paper (maybe a calculator on a side)

[Ugo DI LUCA]

Well, not exactly. I'm not sure about your business, but in many cases, the Line Item (your Item.fp5) is used for other modules. The same line could be shared by the PO file and the Invoice file for example. In my system, the same line item is used for Quotation(proposal), Customer Order, Purchase Order, Shipping, Delivery and Invoice. The number of records needed (and calculations) would overide the file and layout, and it would be somehow difficult to have a clear view of all operations.

Consider this line item as a stockfile for all the other modules attached to it. Link this line item to the PO by the PO#, to the Invoice by the Inv#,...and use portals in these files to display the information using these "links".

That's one reason you will see user saying they enter records "from" a portal instead of "in" the portal...

The dynamic trick here is that you can create related records into the Line Item, checking the allow creation of related records. What does this mean ? Well, any time you will click in a new row for PO #55, a new line in Item.fp5 will be created, and the PO# in Item.fp5 will be populated with "PO#55".

Note that this portal show records from the Line Item, that is that you can change/edit/delete the Qty/price at any time with immediate update in the Line Item (in fact when you know that what the fields showing in the portal are actually records from Item.fp5, it is quite obvious).

Fields in the portals can be formated as you wish even with different format from the Item.fp5 file. The format will not alter the result into the Item.fp5 (you could for example show a result with 15,025 in the Line Item and use format 15,03 in the portal,...).

Before processing entries from the portal from PO (report.fp5), you will set your field Location formated as a value list using all records from Location.fp5 (see Value list menu).

If you defined (and you should have) the prices and details fields in the Item.fp5 to be lookup fields from the Location field (or more probably Location_ID), then you will have exactly the same result while processing an entry from a portal than if you had processed that entry directly into the Item.fp5 file.

When entering the quantity from the portal, your calc qty*price will be set...

Do you understand the process.

[Ugo DI LUCA]

I'll fix it for you. Let me 15 minutes and you will love FM. You will find it so simple that you will never quit.

[Leb i Sol]

I dont think there is an icon to describe the expression on my face >?X

anyway..I will give it a try some other day...this one was successfully un-productive and one big mental loop so I better stop asking questions.

Thank you Ugo for all of your time and patience I really apprechiate it!

[Leb i Sol]

...I do understand the "relationships" but with the SQL in mind.This busniess of "portals" was something that I only heard of in "Space Invaders" not in databases ..LOL...so it will take some time before it all settles....I hope it does...

it is funny that my profile is now "journeyman" I feel more as a "lost nomad"

[BobWeaver]

Don't despair! Filemaker may take some getting used to, especially if you've come from the SQL world. But, Filemaker can be made to do some very impressive things without a lot of development time once you get the hang of it.

[Leb i Sol]

thanx for the support!

I will not give up (yet) it is just that SQL seems more "logical" and specific than FM...( I guess that is why they call it StandardQL)

e.g "Go to layout[]" command...what layout ? go when? what sripts? What relationship did I just make? One-to-one? one-to-many...how to check...where does it say what relationship? how come u can name connecting fields differently and yet the same name filed can not be Auto-Inserted or looked up? ALL THIS AND so on.......

what a mess in my head

perhaps some other day will be a better day......

thanx

[Ugo DI LUCA]

There it is. Start with the reports PO. Don't even try to open the other file at first. Just enter the values from this file and when it is over, check the result in customer, line item, and your location file.

Hope it helps...

[Leb i Sol]

WOW...I don't know what to say but THANK YOU!

I will experiment with it and see if it does have the structure I really need. I am sure I can learn from it.....I will let you know what comes out of it!

I am Very Grateful and I hope some other people outhere will benifit from these long posts

thank you Ugo!

[Ugo DI LUCA]

To have this attachment really work, set the Customer_ID field in the Line Item as a indexed (uncheck the calculate if...). This way, this record will be populated and its purhase would auto-propagate to the customer file.

The Sum(LineItem::Total purchase) in the Location file gives you the amount sold on this Location. In order to have the numbers (quantity), just make a calc Sum(LineItem::Quantity) and in order to know how many timers this Location was Ordered (n

[Leb i Sol]

Line Item.fp5 has Customer_ID as Indexed,Lookup !

did you mean to say uncheck to lookup?

the Sum part worked like a dream!

again...[THANK YOU]

[Ugo DI LUCA]

Nope.

You need it to be a lookup, but you need to go into the Options and uncheck the box index if necessary. This will make Customer_ID a realyy auto-indexed field.

If the Customer_ID isn't populated in the line item, you wouldn't be able to use this link to the customer. Actually, as first instance, I just relookup manually all fields of the line item but this isn't necessary of course if you index the Customer_ID.

Let me know for more problems.

[Leb i Sol]

so far so good.... it is slowly making some sense!

how can I repay this favor...?

[Leb i Sol]

Mr. Ugo!

quick question:

in the DB you donated to me ...can a NEW PO be generated without having to create the Customer first in Customer.fp5?

[ my idea was: have Customer Name (and ID) entered on Reports.fp5 along with the NEW PO and have Customer.fp5 file Look-up those fileds rather than the other way arround?!]

What i want to do is put this sys. on-line (CDML) and have users genereate new Reports but without really creating a whole new Customer Account...just the neame...but it seems that then the totals do now workout unless the Customer already exists. Can this be done so I have only one form on AddReport.html where I can use In-line to insert the same (CustomerName) info into 2nd DB although it is a look-up field?

I know it all sounds funny but I hope you get my idea

thank you

[Ugo DI LUCA]

Hi Leb i Sol,

I think I understand what you are looking for. Actually it looks like what I am also doing in my FM Solution (not online, no CDML though), as I often make some quotations without a Client Account # (using though a Prospect_ID in a Prospect File)

But it becomes a scripted solution.

I build my solution following the path of Online Payment Internet Sites, where you start with viewing the Product list, then proceed with a selection of items, and finally create the account with Billing and Shipping Adress.

You will notice that quite the whole process always takes place within the Product file.

So here is in brief the path and structure of my solution :

1. The user (in my case the salesman) is presented with a list of articles he can filter using a portal.

2. Each row of the portal holds a button send to basket that populate a text field t_Product selected with value "selected".

3. I hold a global field called g_item selected which global text value is "selected".

4. Therefore, when the user has finished the selection from the original portal, he moves to another layout where all products selected appears in another portal using a self-relationship g_selected::t_selected.

5. This portal also hold fields n_quantity, price, c_total selected and a checkmark in order to definitely validate the choice. The total of the selection appears lower with a Sum(::c_total selected)

6. Out of the portal stands a field t_module with value-list attached to it (quotation or customer order) and a Confirmation button, that the user clicks when all products to quote/order are checked.

7. At this point, the user moves to another layout for Customer Info, still in the Product file.

- If the contact is a customer, he fills-in the g_Customer_ID or choose the correct Customer_ID from a value-list.

- If the contact is a prospect, he fills in a g_prospect name field.

8. There is a Proceed button that will set the whole thing by a script. This is the tough part of it. This script will :

A- Reduce the found set of records in the Product file to only those selected.

B- either create a new Quotation or a new Customer Order (your PO I think)

C- Eventually create a new t_Prospect_ID in the Prospect file and set field t_Prospect Name there using constant relationship from Prospect File to Product_ID (g_prospect Name)+ By a "cascade", go back to the newly created Quotation# and populate the t_Prospect_ID and t_Prospect Name from another constant relation from Prospect File to Quotation + Go back to Product file and set global field g_Quotation#

or

If the contact is already a customer, populate the t_Customer_ID into the Quotation File from a constant relation from Product File to Quotation File using g_customer_ID from Product file + set global field g_Quotation#

or

If it is a Customer Order and the Contact already is a Customer, populate the t_Customer_ID into the Customer Order File from a constant relation from Product File to Customer Order File using g_customer_ID from Product file + set global field g_Customer Order#

or

If it is a Customer Order and the Contact is not already a Customer, create a Customer_ID and set the t_Customer_Name using constant from Product to Customer File + populate the t_Customer_ID into the Customer Order File from a constant relation from Product File to Customer Order File using g_customer_ID from Product file.

D. A loop sub-script will go from the first record of found set in the Product File to the last and "insert" each record into a separate line of the line-items.

Each line of the line-item will automatically be populated by the Quotation#, Prospect_ID, Product_ID and Quantity. Names, dates and product descriptions will comes to the line-items by the standards defined look-up keys.

E. At the end, the script will delete every globals and temporary fields (product selected, n_quantity, t_module, ...)

9. The user will end-up in the Prospect or Customer File according to the earlier choice without knowing it (playing with the Interface) and will finally fill-in the adress if not already existant.

10. If the user comes back later to add some articles to an existing order/quotation, he wil be asked the # and will process the new entries from the Quotation or Customer Order file, using the portal in the way I posted it in the attachment.

Hope this is not too confusing. I think you may find a way through these lines though.

[Leb i Sol]

oh man...thank you!

let me digest this for a while and I will get back to you ons I find more question.... I know my posts might sound weird but as I said this is not "the usual" solution for invoce etc. but rather reporting solution to sales made regardless if the customer exists in the DB or not his/her name need to be "re-recoded" if it makes sense...

anyway, I am grateful as always!

[Ugo DI LUCA]

I don't know if this could help as our businesses are different (dealing with wholesaling (purchase/sale), but when a Customer_ID isn't populated, it auto-enter my own unique_ID. This way, it's easier for me to look at all non populated customer orders...

[Leb i Sol]

hello Ugo!

well...I tryed...and after making this whole DB into a mess I started looking into it form different perspective.I really need this to work with CDML (which I am still learning) that sometimes does not like executing scripts from HTML files. No one wiill ever be using this DB in FileMaker but ONLY on web.

I don't know if this is possible but I am sure you have an answer to it:

[1]-What if I eleminate the Customer.fp5 all together since I really don't care about "their" individual records and just have the CustomerName and ID genereated along with NewPO in Reports.fp5?

[2]- Or (which was my original question in this loooong thread ) to have the field CustomerName in Customer.fp5 get auto-inserted (not looked up) from Reports.fp5 when I make a NewPO. Duplicates of CustomerName actually would be welcomed since I do not need to keep the track of each customer's purchases. I am more after Location and PO TOTALS rather than customer's.

so.....what do you think? possible or is this just another whole re-desing of DB we are talking about....

OR OR maybe I have not read your post correctly so if you say "Read my post again" that would probably be the answer to my questions

thank you again

[Leb i Sol]

"but when a Customer_ID isn't populated, it auto-enter my own unique_ID"

..I thought that Customer_ID (auto-increment) is always populated as long as we insert Customer Name?!

as I said we really do not need to "create or keep" the customers DB per say...its more of Reports that I am after...meaning I could have

CustomerID | Customer Name|

1.........| Jack K.Smith

2.........| Jack K.Smith

3.........| Jack K.Smith

4.........| Jack Smith

if this is what it takes to get arround it......

[Ugo DI LUCA]

Hi,

I really don't know a nut on HTML and CDML. I'll probably learn from your posts in the near future.

Could you just explain in some more details what exactly is your business and what you use these files for. This would probably help us find a logical answer.

As a first look, I would tend for Option 1 (with a bunch of seljoin on Customer_ID), but both solutions are workable i THINK.

Hi.

[Leb i Sol]

Need I say that the only reason I got into DBs is because I liked Photoshop and design ....that needed to be "justed up" with some DB in the back

ok ok I think I have figured it out using your structure.....now the CDML part

here is comes (in THEORY for now):

Add Repot.html---->Continue.html --->Added.html

=====Add Report .html =====================================

Enter Coustomer Name:[___________] -->into Custommer.fp5

and trigger creation of CustomerID (which is auto-incerement serial)

..........................[sUBMIT][RESET]..................

=========================================================

|

|

|

========Continue.html===========================

[hidded field=CustomerID from Customer.fp5 = Token.1]--->into Reports.fp5 rather than lookup from Customer.fp5

Enter Note:[________________]-->into Reports.fp5

and the other info

[__________]

[__________]

[__________] ---> into Reports.fp5

....................[sUBMIT][RESET]....................

===================================================

|

|

|

|

|

====Added.html=================================

thank you and have anice day

===============================================

what do you think?

maybe I had too much to drink last night and this will never work...one way to find out

[Leb i Sol]

..about the busniess...it sums to this:

-My boss has 5 stores and each store/location makes/issues handwrites pre-made POs to customers

-He purchased FM to "collect info" in it = no clue about what the databasese is

-My boss ONLY wants daily-weekly-monthly (whatever date range) reports on each Location with TOTALS of items sold and $$$ earned per location [ PERIOD ]

-My boss has problems shutting down his PC therfore you can forget the use of FM from his side

-He wants these reports accessable from anywhere ....=www_CDML

- He walked up to me and said :"You are our computer guy...how would you like to do some DB design and save me $$$. Here is this thing called FileMaker."

I said:"OK" ( as if I would say anyhting else)...opened the box of FileMaker about 2 months ago started....

the rest is history

who bought what and how many times they came to store and where they live...he doesn't care( FOR NOW) all he wants to know is THE TOTALS...give me the totals...

sooo...after some Google.com I am here on FMforums .....

[Ugo DI LUCA]

Hi Leb i Sol,

This will probably end up this thread as CDML is like peanut butter for me. I must admit I even never read a post on this Forum. As I plan to learn, apart reading the Forum threads, what book would you recomand as a start.

For your particular use of FM, it's just OK for me. I'm quite sure that when you will "join" your left brain (design) and right brain into your first database interface, you will soon get addicted to that software, and will convince your Boss to jump into it for more managerial purpose.

[Leb i Sol]

thank you for all the help man....you gave me so many ideas that I am very gratefull!

CDML is something that I had to pick up right away with FM so I am new at it.

I don't know any books but there online respurce is http://www.fmwebschools.com for CDML which is relatively .....well its POOR programming language that lives on HTML.

Another site you might look at is: http://www.vtc.com/filemaker-pro.htm for some video tutorials where I picked up a lot of stuff. As far was web is concerened there is also a Lasso - middleware for FileMaker which seems the be a higher level/way of putting FM on-line...however is costs $$$$ while CDML is FM's native. I would be happy to pass on any knowledge ( not much ) that I got so far.

MOST of my knowledge is in ASP and VBscript so if you have any projects on those platforms I would be more than happy to help out!

If you need anything feel free to leave me a Private Msg.

[Ugo DI LUCA]

Thanks Leb. This is classic sharing knowledge I think