Adding a record in a related file

[BrianKL]

I must be missing something here. I am trying to create a related record in another file with it's own unique line item number. I am using two files, Product, and Inventory Line Items. The only purpose of the Inventory Line Items file is to accumulate what has been received per item. I will then use this total to calculate my QOH in the Products file.

Products file fields:

Category

Item#(indexed, auto-enter, serial)

Name

Qty Received

Date Received

Inventory file fields:

InventoryID#(indexed,auto-enter, serial)

Category

Item#

Name

Qty Received

_Total Qty Received (Sum)

Date Received

Relationships are as follows:

(Prod)Category::(Inv)Category (allow creation of related records)

(Prod)Item#:(Inv)Item# (allow creation of related records)

(Prod)Name::(Inv)Name (allow creation of related records)

(Prod)Qty Received::(Inv)Qty Received (allow creation of related records)

(Prod)Date Received::(Inv)Date Received (allow creation of related records)

Do I need the relationship going from Products to Inventory Line Items, or reverse. Or does it matter?

I want to enter the actual inventory into Products, and update the Inventory Line Item File

with a button in the Products file. This button is supposed to update the Inventory Line Items with the Category, Item#, Name, Qty Received, and Date Received.

I went to the Inventory file and created a script that adds a new record. The script is:

Enter Browse Mode []

Go To layout["Form"]

New Record/request

Show All Records

I attached the script(add a new record) to the button by doing the following.

Specify Button, Perform Script, add new record in inventory file, exit script. Do I need to exit the script, resume, or what?

I performed the script but it didn't pass the data over. It just created another line item number.

Any ideas of what I am doing wrong here? Is it my relationships, or my script?

As always, thanks in advance!

[andygaunt]

Brian,

you have written the script in the inventory database and you want that line to appear in the products database.

What you need to do is pass the product id to the inventory database so it can relate the records together.

I couldn't see in your inventory list that you have a field for the product id.

Either do this in a portal (and as you have allow creation of records on), whenever you create a new inventory line item, if the portal is related by the product id it will send this information for you.

But if you do by a script the way you currently have it, add a paste command to the inventory script to paste in the product id.

Now in the products database do a script

Copy [Product ID]

Perform script [external, inventory, add new record].

And before anyone says, ugh, copy and paste, I know.

Why not just do this in the portal. It would be a lot simpler me thinks.

HTH

[spragueg]

Yes you need the relationship from Products to Inventory Line Items.

You need to create a portal that shows the Inventory Line Items inside the Products file. Make sure to set the relationship to "Alow Creation of Related Records".

Then you can create a script that goes to the last portal row within the Inventory Line Items for a product. Then use the "Set Field" script step to put Qty Recieved into the Inventory Line Items file. If the relationship is based on product id the relationship will automatically enter the product id into the Product Id field of the inventory file.

Once you have done that use the Qty Ordered field calc. (You already have that)

Then the Qty Recieved field to You can get this the same way you did the Qty Ordered using something like this... Sum(Inventory Variable::Qty recieved).

Then make a new calc to subtract the two to get current number instock.

Does that help?

G

[ March 21, 2002, 10:29 AM: Message edited by: spragueg ]

[BrianKL]

Andy,

Thanks for replying! Honestly, I am not sure that I really understand the Portal thing. My understanding of a Portal is that it is a 'view' into another file. Other than that, I am clueless as how to actually use it. I've read the 'bible' about it, but I am still unclear about when, why, and how to use it. If you could enlighten me a little bit on the use of portals, that would be GREATLY appreciated.

Thanks!

[BrianKL]

Graham,

Ok, here's another thing that I am unsure about. And that is a 'set field' What is it, and how do you know when, where, why, and how to use it?

[Chuck]

The Set Field step is probably the most commonly used script step. All it does is set the contents of the specified field to a specified calculation. The field you are setting can be a local field or a related field.

Chuck

[BrianKL]

Ok, which file do I need to set the portal up in? The Product or Inventory Line Item file? Do I need to specify a Portal field? I just dragged the Portal field from the toolbar over and told it to use the Inventory file. I have a relationship of (Product file) Item#:(Inventory file) Item#. Is there anything else that I need to do.

If I understand you correctly, I can just input the Qty Received and Date Received in the Product file? HOW will create a new record in the Portal to the Inventory file? Is there a script that I need to do?

[andygaunt]

Brian,

In your list of fields you do have a (Prod)Item#::(Inv)Item# relationship.

And as your products is your main file this is where you do your work.

So, in products, add a portal to the layout.

And yes, a portal is a window to another databases data that is related to this one, but the window is not closed. You can pass information through it and also receive through it as well.

OK, once the portal has been added, if you add into the first line of the portal only the fields from your (Prod)Item#:(Inv)Item# relationship

Now, your script.

If you script it to say;

Go to Field Prod (Prod)Item#::(Inv)Item# (A visible field in the portal)

Go to portal row [last]

Set field (Prod)Item#::(Inv)Item# (Whatever field you want to set and then specify the contents".

When this script runs it will go to the correct portal by the go to field step (and I did this in case you add another portal to the layout).

Go to portal row last (which is the first empty portal row.

Set field (use this instead of copy/paste). The field does not have to be on the layout to be set. As long as it exists on one layout you are fine.

Does this help.

[BrianKL]

Andy, (or anyone who wants to jump in on this)

Yes, the Product file is where I do my work. I am a little uncertain about the instructions, "if you add into the first line of the portal only the fields from your (Prod)Item#:(Inv)Item# relationship. All I want to enter is the Qty and the Date received and then the rest of the data will hopefully be transfered.

Now, I've added the portal and I started the script, but whats the purpose of the set field? What field would be best to 'set'. Do I want to set the field from Product or Inventory? When I specify the contents, do I want the same content as the set field?

Now, where does this script come into play? Does it run automatically? Or is it going to be attached to my button?

I REALLY do appreciate your help and your patience with a rookie!!!!

[andygaunt]

Brian,

Halfway home.

When you add the portal to the layout, place in the first line of the portal the fields you want to be displayed. These will be from the inventory file via the relationship (Prod)Item#::(Inv)Item# . So place in here your date and quantity fields.

The script step takes you to the last portal row and then you start to set the field.

Basically this is like copy and paste, but without the problems therein.

The question really is, do you want ot enter the quantity and date automatically or manually.

Should have asked this earlier really.

If manually, jjust click in the last portal row (the empty one) and type in the qty and the date.

No script required.

If you want to do this based on some information from the products file then your set field would be

Set Field [(Prod)Item#:(Inv)Item# Qty, The field you want it set from in the products file)

Do the same for date.

The script would then be attached to a button.

Is this getting easier to understand or harder. I can't tell anymore!

quote:

---

Originally posted by BrianKL:

Andy, (or anyone who wants to jump in on this)

Yes, the Product file is where I do my work. I am a little uncertain about the instructions, "if you add into the first line of the portal only the fields from your (Prod)Item#::(Inv)Item# relationship. All I want to enter is the Qty and the Date received and then the rest of the data will hopefully be transfered.

Now, I've added the portal and I started the script, but whats the purpose of the set field? What field would be best to 'set'. Do I want to set the field from Product or Inventory? When I specify the contents, do I want the same content as the set field?

Now, where does this script come into play? Does it run automatically? Or is it going to be attached to my button?

I REALLY do appreciate your help and your patience with a rookie!

---

[BrianKL]

Andy,

I want to just enter the Qty Received and the Date Received in the Product file and hit the Update Inventory button to update the Inventory Line Item File.

Ok, I created my portal script as follows.

Go To Field[ProductItem#_InvLineItemItem#..."]

Go To Portal Row[selectt, Last]

Set Field ["Item#","ProductsItem#_InvLineItemsItem#::Qty Received"]

Set Field["Item#","ProductsItem#_InvLineItemsItem#:Date Received"]

Is that correct?

Now, I know this may sound crazy, but I was expecting to see my fields in the Portal. But all I see is BLACK. So, I dragged the two fields in the Product file, Qty Received, and Date Received inside the Portal that I created in the Product file. Was that a mistake?

I guess the question I have, where is the physical field location that I am going to enter the data. In the Products file, or in the Inventory file. And, will the entry of those fields take place inside the Portal? If the fields are in the Portal, which file is actually supplying the data?

[andygaunt]

Brian,

you are getting it now.

OK. To see the information related to that particular product via the relationship (Prod)Item#::(Inv)Item# you have the portal.

Now, you need the fields in there that correspond with that portal

(Prod)Item#:(Inv)Item#Qty

(Prod)Item#::(Inv)Item# Date.

These fields are in the inventory database and we use the portal (window) to look at the related data.

Drag a field into the portal and where it says current file, change to this relationship.

Don't put the Qty & Date fields from the products database (or the current database inside the portal).

The portal is to look at related data, so use the fields from the inventory.

Now, in your script you want to set the qty and date received.

What do you want to set it to.

Set Field ["Item#","ProductsItem#_InvLineItemsItem#::Qty Received"]

Set Field["Item#","ProductsItem#_InvLineItemsItem#::Date Received"]

This tells the database what field to set (the specify field button)

Click the specify button to tell it what to set to.

But, my previous question still stands.

Is this something you would do manually.

Click in the portal and type in the qty and date, or is it something that requires automation?

[spragueg]

Before you can create a portal you first need a relationship.

You Invoice and Invoice Line Items files use a relationship and a portal to allow you to add products to an invoice.

The invoice has an id or invoice number. Each line item must be directly associated with it's invoice so each line item must also have a invoice id field.

Now if you make a relationship from Invoice file to the Invoice line Items file based on the Invoice file's id field matching the Invoice Line Items invoice id field that relationship and create a portal based on that relationship in the Invoice file. The portal in the Invoice file will show line items whose invoice ids match that of the current invoice record.

Does that make sense?

G

[BrianKL]

Graham,

My relationship is (Prod)Item#:(Inv)Item#. I beleive that this is what my Portal relationship is based on. Yes, it is. I also have allow creation of related records. Is that correct? When I enter the date and the Qty received, the remaining information should be passed on as well, correct? Should the Date and Qty Received be the fields that appear in the Portal? I still have them as separate fields in the work file (products).

And what about the script? Please refer to my previous post.

[BrianKL]

I think that I might be getting this. I put the Qty Received and Date Received fields inside of the Portal and selected the relationship of ProductsItem#::InvLineItemItem#. Then, I clicked on the fields and went to SPECIFY and chose the relationship and the :Qty Received and ::Date Received. Was that correct?

Now, to answer the question of do I want to do it manually, or automatically. Automatically. Is my script supposed to be in Products, or Inventory. And, do I need an Exit statement after the Set fields. If the script is to be located in the InvLineItem file, then I need to make the button in the Products file to Perform script, external, and then exit, resume, or what?

Now, assuming I have all of this correct to this point, I am getting an error message, "The date in this field must be a valid date in the range of years 1 to 3000 and should look like "12/25/1997". I have the date in the correct format. Any ideas?

So, since I have the relationship of ProductItem#::InvLineItemItem#, it should pass the other data over for Category and Name. And all I need is the one relationship of Item#::Item#. I don't need to have multiple relationships for ALL of the fields. Correct?

[andygaunt]

Brian,

Glad you are getting it.

OK, your post.The script is located in the products file. The go to field and go to portal row last set up for the new record.

No external script required.

Now, the date problem is not you, it is set field.

try

Set Field ProductsItem#::InvLineItemItem#Date Received , "TextToDate(whatever the date should be)

You can add an exit record/request to free that record in your app for others to modify.

And no, you don't need all the other fields in a relationship.

But, you might want to set them in the inventory to lookup their corresponding value in the products via id relationship

quote:

---

I think that I might be getting this. I put the Qty Received and Date Received fields inside of the Portal and selected the relationship of ProductsItem#

:InvLineItemItem#. Then, I clicked on the fields and went to SPECIFY and chose the relationship and the ::Qty Received and ::Date Received. Was that correct?

Now, to answer the question of do I want to do it manually, or automatically. Automatically. Is my script supposed to be in Products, or Inventory. And, do I need an Exit statement after the Set fields. If the script is to be located in the InvLineItem file, then I need to make the button in the Products file to Perform script, external, and then exit, resume, or what?

Now, assuming I have all of this correct to this point, I am getting an error message, "The date in this field must be a valid date in the range of years 1 to 3000 and should look like "12/25/1997". I have the date in the correct format. Any ideas?

So, since I have the relationship of ProductItem#::InvLineItemItem#, it should pass the other data over for Category and Name. And all I need is the one relationship of Item#::Item#. I don't need to have multiple relationships for ALL of the fields. Correct?

---

[BrianKL]

Andy,

When I went to SetField ProductsItme#::InvLineItemItem# Date Received and Specify, I chose the 'Text To Date (text)". I went to click Ok, and then this error came up. "Either an operator was omitted, this function cannot be found, or "("was not expected here.

Now, where it says (text) in the statement, do I need to type a date here? Or, will I just need to type the date in the field. I guess what I am actually asking do I change it in the statement, or in the field. I assume that this will covert the text date to actual date format? Will I have to use mmddyy format when entering the date? Or,mmddcy.

Thanks!!!!

[andygaunt]

Brian, almost there with this one I think.

This set field is dependant on what information you want to place in here.

Do you have a date field you are calling the information from.

I think you said you have a date and qty fields in the products file.

so your set fields will be.

Set Field [ProductsItem#_InvLineItemsItem#::Qty Received, Qty received]

Set Field[ProductsItem#_InvLineItemsItem#:Date Received, Date Received]

This should then set the qty received in the line items to the field qty received in the current file, and the date in the same way.

you should only need TextToDate (if the information you are calling is not a date field.

The error this date should be in format.... only occurs if one of the files is not set to use your system formats (ie, it is ddmmyy instead of mmddyy)

Try this and let me know.

Alternatively we can discuss off list and try and set up an instant messenger session, so we can talk in realtime.

Let me know.

[BrianKL]

Andy,

Check your mail.

[BrianKL]

Andy,

Thanks for all your help and your willingness to stay with me all the way through this issue!!! Your FM Pro knowledge is GREAT! Not very many people would go to the extent that you have in order to help a rookie out.

Thanks again!!!

Brian

[ March 25, 2002, 10:56 AM: Message edited by: BrianKL ]