Calculating remaining inventory on X date based on items already reserved

[Joe_Schmo]

I'm building an inventory tracking DB for a rental company. Most of it is pretty straight forward but the one thing I'm not sure how to approach is calculating the remaining inventory on a given date. It would have to subtract all items reserved for any date range that covers the day in question. Something like:

On: 7/24/12

ITEM ON HAND TOTAL

Table 5 7

Chairs 30 80

-where TOTAL is the total number of that item owned by the shop, and ON HAND is the number left that is not being rented on that date

Then, if one table and 10 chairs are returned on the 25th (and nothing else is rented) the 26th would look like:

On: 7/26/12

ITEM ON HAND TOTAL

Table 6 7

Chairs 40 80

So far, I have tables for INVENTORY, INVOICES, and LINE ITEMS. There is a portal on the INVOICES layout that shows (and creates records in) the LINE ITEMS table.

I'm thinking I should use a global date field as the filter when viewing the layout that will show the remaining items. And have it filter all line items who's rental period either ends before or starts after the date in question. Then total the number of items being rented of each type and subtract that from the total owned by the shop for each item. I'm pretty sure I can set up this layout as far as filtering the line items by rental date, but how would I then total each item separately, and subtract that from the value in the INVENTORY table for the total owned by the shop?

[eos]

From your description, I assume you're thinking about starting the relationship to LineItems from Invoices.

Instead, define it from Inventory; this lets you calculate the respective number for each individual product, which you can then display/use wherever the Inventory item (product) itself is displayed - e.g. in a line item row in a portal in Invoices.

[Joe_Schmo]

I do have a relationship defined between INVOICES and LINE ITEMS for the portal to add items to the invoice. But that's just for the invoices layout. So are you saying I should make a separate relationship for the new layout that will total the items left in inventory, or are you saying to change the one relationship that I have?

this lets you calculate the respective number for each individual product

-but how? With a summary field? In which table?

[eos]

I do have a relationship defined between INVOICES and LINE ITEMS for the portal to add items to the invoice. But that's just for the invoices layout. So are you saying I should make a separate relationship for the new layout that will total the items left in inventory, or are you saying to change the one relationship that I have?

-but how? With a summary field? In which table?

In Inventory, because you want the individual value for each product. If you set up a filtered relationship from Inventory to LineItems, based on InventoryID and the global date field, a new calculation field in Inventory, like

quantityTotal - sum ( myFilteredLineItems::quantity )

will give you the available quantity for the product on that date.

[Lee Smith]

Take a look at the presentation by Todd Geist Inventory Transactions, and maybe it can help.

[Joe_Schmo]

Take a look at the presentation by Todd Geist Inventory Transactions, and maybe it can help.

Lee-

Great video, very informative. Thanks for sharing. I'm not sure it's the right approach for my application but it's still good info that may come in handy in the future.

For this DB though, there won't be multiple users. Just one termical at the front counter were all the transactions will take place. And I like the idea of having it done automatally with a summary field and filtered portal rather than relying on the user to click the update inventory button at the right time. This set up is different because it's not a snapshot of the current inventory that I'm after. I need to be able to look at what the inventory will be on any given day based on reservations and whats already rented out.

[eos]

And I like the idea of having it done automatally with a summary field and filtered portal rather than relying on the user to click the update inventory button at the right time.

I was thinking about recommending a transaction-based approach, but realized that you wanted to see the state of things at an arbitrary date. On the other hand, you could do both.

Also be aware that the outlined method uses a filtered relationship, not a filtered portal.

[Joe_Schmo]

I was thinking about recommending a transaction-based approach, but realized that you wanted to see the state of things at an arbitrary date. On the other hand, you could do both.

Also be aware that the outlined method uses a filtered relationship, not a filtered portal.

Right, filtered relationship is what I meant. I don't think filtered portals were introduced until FM 11 and I'm still on 10.

[Joe_Schmo]

Here's what I have so far. The STOCK layout is the one I'm trying to get the total rented/remaining on. I haven't created a field or calculation for the number remaining yet because I'm having problems getting the filtered relationship set up for the date to be viewed. The Stock:g.Date field is the one the user will use to select a date to inquire about. I'm trying to filter line items that are rented out on or before that date AND being returned on or after that date.

Could you take a look and see what I'm doing wrong please? Thank you

Rental.zip

[eos]

You need the gDate field in the Inventory table, because the Inventory ID must be factored into the relationship predicates to get the numbers for the individual items.

See if this helps:

Rental2.fp7.zip

[Joe_Schmo]

You need the gDate field in the Inventory table, because the Inventory ID must be factored into the relationship predicates to get the numbers for the individual items.

See if this helps:

Ok, I see what you are saying about the gDate field needing to be in the inventory table. This is way closer to what I am after than what I had before, but it's still not quite working right. I would like for the date to be the only field used to navigate and/or edit the layout, and for all items in the inventory to be listed in the portal showing the # remaining in stock for that day. And there should be no way to accidentally edit the records from there. It's just a viewable, list of what's in stock. When I was trying to check another item with your layout, I ended up renaming an item instead but the ID field stayed the same. I'll see what I can come up with based on what you added, but if you know what I'm saying and could give me a few pointers it would be greatly appreciated. Thanks for all your help!

[eos]

Ok, I see what you are saying about the gDate field needing to be in the inventory table. This is way closer to what I am after than what I had before, but it's still not quite working right.

In fact it is working right, but if I understand you correctly, you want to eliminate the risk of accidentally overwriting the current item data. Well, then simply remove all fields from the layout related to the current item; all you need are gDate and the portal for the Line Item allByFilterDate TO.

Here's another version, with outAtDate in its own field instead of buried in the calculation, so you can display it in the portal, too.

Rental3.fp7.zip

[Joe_Schmo]

That will work! I was Kind of hoping to have ALL items listed in the portal, but I think I can get around that by creating an invoice with all items on it and the quantities set to zero, then set the dates to 0/00/00 and 12/12/99.

The only thing that bugs me is that your GTRR script is finding a set of records in the inventory table, so it's not showing all records. That doesn't matter in the InventoryDateCheck layout since you are looking at records from the LineItems portal, but when you change your view to Inventory, it's still only showing a subset. Should I set a 'Show All" script to trigger when loading the Inventory layout, and set the script you made to run on layout load for the InventoryDateCheck?

[eos]

That will work! I was Kind of hoping to have ALL items listed in the portal, but I think I can get around that by creating an invoice with all items on it and the quantities set to zero, then set the dates to 0/00/00 and 12/12/99.

Maybe I'm missing something here, but isn't your intent to use a date as a filter? Why now would you want to show all items?

Of course, if you want to show all inventory items, you can use a cartesian relationship, and also display the calc fields; but the numbers (if any) would still be based on gDate.

The only thing that bugs me is that your GTRR script is finding a set of records in the inventory table, so it's not showing all records. That doesn't matter in the InventoryDateCheck layout since you are looking at records from the LineItems portal, but when you change your view to Inventory, it's still only showing a subset. Should I set a 'Show All" script to trigger when loading the Inventory layout, and set the script you made to run on layout load for the InventoryDateCheck?

The GTRR script was merely a tool to get a list of Inventory items with at least one line item, to have some test data.

The relationships (and thus the portal) are not dependent on a found set in either table (which you can see for yourself by simply omitting all records in both tables). Just delete the script.

[Joe_Schmo]

Maybe I'm missing something here, but isn't your intent to use a date as a filter? Why now would you want to show all items?

Of course, if you want to show all inventory items, you can use a cartesian relationship, and also display the calc fields; but the numbers (if any) would still be based on gDate.

No, you're not missing anything. That's just me not being entirely sure what I'm after lol. The way it is now, if a user wants to check the availability of an item before renting out more than they have, they would check on that date and see the number remaining. But if none were rented out, it won't appear on the list. I was thinking it would be nice if it appeared and said that none were rented for that date, instead of the user assuming that based on it not appearing on the list. That was an after thought, half way through developing this layout, and a mis-communication on my part.

The GTRR script was merely a tool to get a list of Inventory items with at least one line item, to have some test data.

The relationships (and thus the portal) are not dependent on a found set in either table (which you can see for yourself by simply omitting all records in both tables). Just delete the script.

So I won't ever need the script? Even if more items are added to the inventory?

[eos]

No, you're not missing anything. That's just me not being entirely sure what I'm after lol. The way it is now, if a user wants to check the availability of an item before renting out more than they have, they would check on that date and see the number remaining. But if none were rented out, it won't appear on the list. I was thinking it would be nice if it appeared and said that none were rented for that date, instead of the user assuming that based on it not appearing on the list. That was an after thought, half way through developing this layout, and a mis-communication on my part.

So I won't ever need the script? Even if more items are added to the inventory?

By implication, an item not appearing in the portal is an item not being rented out on that day. Maybe add a header to make clear what is shown.

Depending on the ratio of existing vs. rented-out items, a complete list might be helpful, with rented items being conditionally formatted.

As for the script: again, it's not necessary. Check how the relationships work.

[Joe_Schmo]

I noticed a few line items that weren't showing up as rented on certain days when they were on an invoice for that day. Since the line item rent and return dates were look ups, it only works if the date is entered before adding line items. So I tried making the line item rent/return dates a calculation and that broke the relationship filtering. I guess you can't filter a relationship based on a calculation field. So how should I go about making sure the dates in the line item records is updated before viewing the check inventory layout?

[eos]

You can use calculations in relationships just fine, but when they're on the “right-hand side” of a relationship (that would be LineItems in this case), they need to be indexed, or indexable; this means you can't refer global fields, non-indexable fields or fields from other tables.

I'd say make sure that every invoice (and its line items) have a return date, e.g. by defining a standard renting period. Then either set the return date manually or have it (tentatively) calculated both in the invoice and the line items based on rentDate + renting period, before you enter any line items. If you later change the return date, use a script (trigger) to update the line items.

btw, some of your line items don't have dates because you're using dates in the invoice, but timestamps in the line items, and the auto-enter calc is incorrect. Maybe better to use date fields in the line items, since for filtering the time component isn't critical, and should you need it, it's in the invoice.

Look at the script for the return date field in Invoice, and the auto-enter options for line items.

Rental4.fp7.zip

[Joe_Schmo]

btw, some of your line items don't have dates because you're using dates in the invoice, but timestamps in the line items, and the auto-enter calc is incorrect. Maybe better to use date fields in the line items, since for filtering the time component isn't critical, and should you need it, it's in the invoice.

Yeah, I noticed that too and have corrected it in the version I'm working with now. Thanks again man, you've been a huge help! I'll let you know how it goes.

[Joe_Schmo]

WOW! Great script man! I like how you used the scriptparameters and "if" steps to allow for using the same script on enter and exit. Great error trapping and logging too. That's something I need to start doing more of. I modified the script to update the rent date as well for all line items when it is changed too. So the same script now handles on enter and on exit of the rent and return date.

I've learned a lot on this project, and most of it was from you my friend! I can't thank you enough!

One thing I noticed though is that in the loop, you have IF GetLastError.... and then no = X or anything after it. Should that have been IF not isEmpty(GetLastError)?

I've got just about everything working now. Here it is in case you are interested. I think one of the only major issues I have to work on is figuring out how to print the invoice when the line items fill more than the portal can display... but that's another thread

Thanks again man!

Rental3.2.fp7.zip

[eos]

[…] scriptparameters and "if" steps to allow for using the same script on enter and exit […]

It's a good thing script parameters can be used in triggers, so you can put all your logic into one script; but this can be tricky, e.g. if your exit block re-enters the field …this is where having FM Advanced comes in handy; someone said that using script triggers without Script Debugger is like flying blind, and I agree.

One thing I noticed though is that in the loop, you have IF GetLastError.... and then no = X or anything after it. Should that have been IF not isEmpty(GetLastError)?

If [ ] expects a boolean result, and any (error) number ≠ 0 is evaluated as true, so this works if you only want to know if an error occurred. (You can make it explicit by using GetAsBoolean(), though.) Depending on the actions in your script, you can of course trap for specific error numbers and write appropriate error messages. Note the implicit error trapping in the check if there are any related line items to be updated, and only then GTRR.

I've got just about everything working now. Here it is in case you are interested. I think one of the only major issues I have to work on is figuring out how to print the invoice when the line items fill more than the portal can display... but that's another thread

That's not so bad … one approach is building a list layout based on a LineItems TO, putting the boilerplate stuff into header and footer, making two sub-summary parts for the two item categories and displaying the items themselves in the body part. When it's time to print, GTRR to that layout, sort, print and return. Only downside is that list view has no vertical parts, which means you'd have to modify your existing invoice design.

Good luck with the project!

[Joe_Schmo]

That's not so bad … one approach is building a list layout based on a LineItems TO, putting the boilerplate stuff into header and footer, making two sub-summary parts for the two item categories and displaying the items themselves in the body part. When it's time to print, GTRR to that layout, sort, print and return. Only downside is that list view has no vertical parts, which means you'd have to modify your existing invoice design.

Yeah, I've used that same approach before but it won't work in this case without modifying the layout like you said. You said that's one approach... do you know of any others? It seems like this has to come up fairly often when printing a layout with a portal. I'm surprised FM hasn't implemented a feature that handles printing multiple pages of the layout and split the related records based on how many fit in the portal.

[eos]

I'm surprised FM hasn't implemented a feature that handles printing multiple pages of the layout and split the related records based on how many fit in the portal.

Just to keep us alert and on our toes and make us come up with creative stuff.

There's a technique for swapping in different sets of related records into a portal, but I misplaced the instruction manual for that one.

Another would be to List () the item names and prices and build your own formatted text field. In your case this would mean that you didn't have to alter the layout, just replace the portal with the text box.

[Joe_Schmo]

There's a technique for swapping in different sets of related records into a portal, but I misplaced the instruction manual for that one.

I had a similar idea but haven't tried it yet. But instead of swapping, I was thinking of sorting. I could create an auto-entered serial number field that resets to 1 for each invoice. Then have a calculation that divides that into groups of 10 (0r however many fit in the portal) so #s 1-10 would all = 1, 11-20 would all = 2, etc. If I had less than 20 and sorted one way, then the other that would cover it. More than 20 would require a custom sort but still be managable. But this way there would be no need for changing the layout or creating a new one.

Another would be to List () the item names and prices and build your own formatted text field. In your case this would mean that you didn't have to alter the layout, just replace the portal with the text box.

This sounds like it might be easier though! I think I'll try it first. I guess I would still have to have a count of the # of line items per invoice to help with parsing the items over multiple sheets when printed so maybe I'll end up making that field either way.

Thanks for all your help! Have a great weekend

[eos]

I had a similar idea but haven't tried it yet. But instead of swapping, I was thinking of sorting. I could create an auto-entered serial number field that resets to 1 for each invoice. Then have a calculation that divides that into groups of 10 (0r however many fit in the portal) so #s 1-10 would all = 1, 11-20 would all = 2, etc. If I had less than 20 and sorted one way, then the other that would cover it. More than 20 would require a custom sort but still be managable. But this way there would be no need for changing the layout or creating a new one..

[…]

Thanks for all your help! Have a great weekend

What seems like a problem is in fact just another challenge!

You're welcome. Same to you.