search for existing record when attempting to add new record

[madman411]

Hey guys. 

 

I have a 'general inventory' table which my system uses as it's primary source of information (we are a rental company.) When we don't have an item, we subrent it from another rental source. Currently when we receive a subrented item it is added to a separate table called 'subrented inventory' when a new subrental order is created - however this currently causes an issue with our staff looking up inventory in general as they're entered in to two different tables. I'm in the process of migrating this table into the 'general inventory' table so finding assets is easier, and a 'vendor' field will define if the item is ours or another companies.

 

This presents a new issue though. When our staff is adding new subrented items I need the system to recognize if we have already subbed the item in the past (by referencing the serial number and/or asset #). I understand all this will involve is a simple find script to start with, however, I still want the [existing] subbed item to appear on the NEW subrental order form, even though it is already an item in our inventory table, and already on an old subrental order form. I do NOT want a new record created for an existing item.

 

I think this will require a separate table to track occurrences outside of the 'general inventory' table? I'm sure this is easier than I'm thinking it is, but when I try to put pen to paper I'm failing to put a workable solution together.

 

Tips would be appreciated so I can finish migrating this data and implement the change. Cheers!

[databaser]

i'm not sure i undestand, but here's what i'm thinking

 

create a global field 'g1', a calculation field 'c1', and a summary field 's1'

 

have a script enter the new serial number in g1

 

the calculation for c1 is g1=serial

 

summary is the total of c1

 

have a script trigger for when you exit the serial field - the script will set g1 as that serial number., then if s1>1 it deletes that record and goes to the record that already has that serial

[bcooney]

Merging all inventory is the correct approach and using a "type" field to tag whether the item is a subrent or not.

 

Don't you have an orders table with line items (if you rent more than one item to a customer at a time)? So the process would be to find in inventory for the item, create new item record if necessary and then create an order record (button on item record). No?

[madman411]

Merging all inventory is the correct approach and using a "type" field to tag whether the item is a subrent or not.

 

Don't you have an orders table with line items (if you rent more than one item to a customer at a time)? So the process would be to find in inventory for the item, create new item record if necessary and then create an order record (button on item record). No?

 

Thanks for your help. They aren't exactly outgoing orders. When subbed items come into our facility we have staff members that "receive" them and make a record of what each item is (this is currently done on paper each time). They could be just one item, or kits. It is commonplace for us to end up subbing the same item from the same company. What I'm trying to do by creating this Receive Order system is allow for our management to see which items we are subrenting the most so they can purchase said item. That's why, rather than creating a new item each time something comes in, the system can find the item (if already subrented) and just create another tie between it and the new receive order.

 

I have an error capture script set up which prevents users from entering Serial or Asset numbers into a receive order that matches our existing inventory of which WE are the Vendor. This gets complicated though as everything is located within the same table.

 

So yes, basically there is an orders table. A user clicks "Create New Receive Order" and chooses a Sub-Vendor company name. On the Receive Order layout the user clicks "Receive Item" and a new line is created in a portal row where they have to enter as much information as possible. When they add a new line it is currently creating a new record in our inventory table.

[bcooney]

Well, I'd give them a popup select window that allows them to either select an existing item or create a new one. Search for popup select new on the forums, I've posted a demo and don't have the time this morning to find it for you.

[madman411]

Thanks bcooney.

 

Do you remember which subforum you would have posted this in? Cheers

[bcooney]

http://fmforums.com/forum/topic/73737-value-list-based-on-another-field/?p=349257

[madman411]

bcooney, my dear, you are a star