1 2 Previous Next 15 Replies Latest reply on Jan 13, 2012 10:51 AM by philmodjunk

    Show an Item available...

    Bricktop

      Title

      Show an Item available...

      Post

      Good Morning,

      I'm looking for some help, I have a "lines table" and "equipment table" for a rental data base, I have a start date and end date in my "lines table" and I'm trying to get a "status" field on my equipment table to tell me if its rented out or available. RIGHT NOW I look at my "lines table" sort to see the last end date for a particular piece and if its earlier than today then I know its available. I'm looking for some help with a script or validation to put on my "equipment table" that looks at the lastest end date in my "lines table" validates to todays date and to show me if its available or not.

      Please and Thank You

        • 1. Re: Show an Item available...
          philmodjunk

          This is a complex issue discussed previously here in the forum. One key detail to confirm first: In your database, does each line item record represent a single equipment item or a group of items? (Do you rent 500 folding chairs as a single item or are you renting things like ditchdiggers, Jack Hammers, etc.?)

          • 2. Re: Show an Item available...
            Bricktop

            Each line item records represent a single piece of equipment but there are some items that have the same name those I'm not to concerned about those can show any "status". For fixing purposes status I can name all pieces different I just rather have the status that's more important.

            • 3. Re: Show an Item available...
              philmodjunk

              Each Item should be listed in an Equiptment table with one record for each oen and a serial number generated in the same table to uniquely identify each. Use this field to link equipment records to other tables in your database, such as the LineItems table. (There are likely serial numbers on your equipment and you can put them in fields in this same table, but don't use them in relationships.)

              Sometimes equipment gets reserved in advanced. "I need to rent the ditchdigger for a week starting next Monday, can I pick it up Friday night?"

              From your initial post where you describe checking for the most recent rental date, it would appear that this is not the case, can you confrim that?

              If so, then checking the end dates is not sufficient to determine item availability.

              Here's a method that replicates your current method.

              Add a new occurrence of LineItems and link it like this to your rental invoice table:

              Invoices::gEquipmentID = LineItemsAvailabilty::EquipmentID AND
              Invoices::cToday < LineItemsAvailability::EndDate

              gEquipmentID would be a field with global storage and formatted with the same drop down list as you have for equipment Id in your line items portal. cToday is an UNSTORED calculation field defined to return a date: Get ( CurrentDate )

              This calculation field defined in Invoices will report if an item is available:
              If ( LineItemsAvailabilty::EquipmentID ; "Not Available" ; "Available" )

              Be sure to clear the "do not evaluate if all referenced fields are empty" check box or the field will be blank when the item is available.

              You'd use this field by selecting an EquipmentID in the gEquipementID field. If the calculation field returns "available", add the item to the lineitems portal. With a script you can automate the process so that selecting an item that is available automatically addes it to a new record in the portal.

              Please note that if future rental reservations are possible, you'll need to use a much more complex relationship (several in fact), to correctly check availability.

              If you've never created new table occurrences before:

              In Manage | Database | relationships, make a new table occurrence of LineItems by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

              We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

              Add it to your relationships like this:

              • 4. Re: Show an Item available...
                Bricktop

                Each Item should be listed in an Equiptment table with one record for each oen and a serial number generated in the same table to uniquely identify each. Use this field to link equipment records to other tables in your database, such as the LineItems table.

                I have that.

                I cant use global field because It deletes previous records.

                From your initial post where you describe checking for the most recent rental date, it would appear that this is not the case, can you confrim that?

                I have a start date and end date field in my lines table, EquipmentID in my equipment table I just wanted a status field in my equpiment table to show available or rented based on the end date < todays date.

                • 5. Re: Show an Item available...
                  Sorbsbuster

                  gEquipmentId is used to allow you to specify an EquipmentID to look for (match to).  It has to be global.  It doesn't delete any records, and doesn't over-write the EquipmentID data.

                  • 6. Re: Show an Item available...
                    philmodjunk

                    I cant use global field because It deletes previous records.

                    The global field does not delete any records. It is a tempoary holding place for your equipment ID so that you can check availability before adding the item to the invoice. If the item is available, you then either manually of through a script add the equipmentID to a new line item record for this invoice. This avoids complications where the relationship would identify the new lineitem record as the conflicting record when checking availability.

                    I have a start date and end date field in my lines table, EquipmentID in my equipment table I just wanted a status field in my equpiment table to show available or rented based on the end date < todays date.

                    Which is exactly what my suggested solution does. I'm just identifying the limitations of this approach. Namely, that if you allow customers to reserve equipment for a future date interval, this method will fail to check availability correctly in all situtations.

                    • 7. Re: Show an Item available...
                      Bricktop

                      Well when I went to change my EquimentID to a global field it I had to uncheck unique value and it says "Turning on of off global storage will cause exsising data in this field to be lost. I made a copy of my data base and It did erase all previous data.

                      • 8. Re: Show an Item available...
                        Sorbsbuster

                        Phil said: "gEquipmentID would be a field with global storage and formatted with the same drop down list as you have for equipment Id in your line items portal"

                        It is another field - it is not the same field as EquipmentID.

                        • 9. Re: Show an Item available...
                          Bricktop

                          So Ive been trying and trying using my table and field names and it seems Im missing something (again sorry I'm a newbie and I'm really trying) Im lost on " UNSTORED calculation" and new table " I THINK Frown

                          In Manage | Database | relationships, make a new table occurrence of LineItems by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box."

                          Should I do this for what you show as " LineItemsAvailabilty" in other words make a new table naming in LineItemsAvailabilty.

                          • 10. Re: Show an Item available...
                            Bricktop

                            This calculation field defined in Invoices will report if an item is available:
                            If ( LineItemsAvailabilty::EquipmentID ; "Not Available" ; "Available" )

                            What calculation field?

                            • 11. Re: Show an Item available...
                              philmodjunk

                              Should I do this for what you show as " LineItemsAvailabilty" in other words make a new table naming in LineItemsAvailabilty.

                              Yes but this is NOT a new table. It is a new occurrence of your existing table.

                              What calculation field?

                              This will be the calcluation field that you define in your invoices table with that expression and add to your layout to show item availability.

                              To create a calculaton field, Open Manage | Database | Fields, select the table you need in the table drop down, enter the field name you want and select "calculation" as the field type. Click create. This opens the specify calculation dialog where you enter the expression needed for this calculation. There's a list of tables and alist of fields at the top. Double clicking a field name adds it to your calculation with the correct table occurrence reference if it is from another table.

                              To make the calculation unstored, click the storage options button in the specify calculation dialog and select the "do not store..." check box.

                              • 12. Re: Show an Item available...
                                Bricktop

                                Thanks for all your help but its not working I think It has to do with my lines table having lots of the same item listed with lots of end dates. It seems to beyond my knowledge, Ive made several different attempts and its making my head spin. I even went to the starter solutions and tried to use the Inventory way, but to no avail.

                                Ive been wanting to take classes but they're all while I work. Again thanks a bunch, Ill have to try and figure out something else.

                                I love FMP I just wish I knew it better!!!

                                • 13. Re: Show an Item available...
                                  philmodjunk

                                  but its not working...

                                  How is it "not working"? That is often a helpful clue in figuring out where to go from here.

                                  • 14. Re: Show an Item available...
                                    Bricktop

                                    Nothing shows but Im also trying to make my stuff align with yours.  Maybe it can be put in a step by step format.

                                    See it seems to me that your looking at it in the future Im look at it right now. I have items rented and I want to see whats not rented thats all. As I scroll threw my equpiment list I want see its status which I guessing will come from my lines table.

                                    1 2 Previous Next