Joe_Schmo

reiersga, I know this is an old thread but did you ever find a solution? I'd like to be able to do this as well and the web scraping option seems viable. The Google spreadsheet itself won't work though. That's not an HTML table like in the video. But if you go to File>Publish to the web, and import from that URL then you will get the data you need to start with. At first glance, I don't see timestamps included in the data though so you may need another column to use as a unique ID. I guess those values are stored somewhere else and imported as it's being displayed in the webpage, as opposed to being part of the HTML like the rest of the data.

That's what I was afraid of. Thanks for confirming though. Looks like we are either upgrading all clients or going with server advanced and hosting via IWP then.

Ok, but just to be clear- I'm not trying to share a single run time build to multiple users over the network. I want multiple builds of the runtime on multiple machines to all access the same database on the server. So you're saying THAT is not possible right? I know you can't share a signle build over the network. Just want to make sure we're talking about the same scenario here. Thanks :)

Does anyone know if it is possible to convert a fp7 database to fmp12, then to a stand alone run time and have it access the original database in fp7 format hosted on FMP 10 Server? We currently have FMP10 Server and about 20 machines with a standard FMP10 client installed. We've purchased FMP12 Server and a few client licenses but are trying to avoid upgrading ALL the clients if the run time solutions can be used to access either server. We would prefer if they could access the FMP 10 server, at least for now until the migration is complete. If it is possible, do you have to change the table::field reference for every field in every table to the remote DB? Or is there a simple way that's more of a global reference to the remote DB that affects all fields?

Yeah, I thought you didn't want any backup software to try grabbing a copy of the DB while it's running. This will be on a Windows PC though so that wouldn't be an option anyway. I think I'm just going to use a script to save a copy with all records and dump it in Dropbox, but not actually store the original in Dropbox. Then use a VBscript and scheduled task to archive all but the most recent and purge them after so long.

We have FMPServer 10 at work. However, I am building a DB for a friend who owns a small business. They will only use it in one location and never have more than one user at a time. So it is not likely that I will convince them to spend the extra money to buy the server version for a single user. So I'm looking for an alternative solution that either backs up on line or to and external drive to prevent data loss if the HD fails on the system where the DB will be stored and ran from.

I have a DB that will be running natively without a FMP server. What's the best way to back up the records and/or whole DB? Would something as simple as placing the DB file in Dropbox be sufficient? Export all records periodically via a script? Should I have a scipt to flush cache to disc or commit records more often than if it were on a server?

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. 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

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.

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

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.

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?

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?

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?

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!

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

Some things in FM (and OSX in general) are so simple that it doesn't even occur to you that the solution could be that simple! I entered Sum ( Line_Items::RentalCost ) in the calculation for the field I wanted the total in and it worked. I thought I needed to do something different to tell FM to only total the rental costs for this invoice but I guess the relationship does that already! THANKS And, I do plan on removing items from view when printing as you mentioned, but I haven't really started tweaking the layout yet. Still working on getting all the calculations to work. Thanks again for you help!

How can I get a sum of all the values from all rows in a given field of a portal? I have a line items portal on an Invoice layout. I need the total of each charge in the line items to be displayed outside the portal (in the rest of the invoice) and be able to use that in another calculation. In the attached file, it's the "Rental Cost" of the top portal that I want to add up and put into the "Rent" field in the Fee section on the right of the layout. Rental.fp7.zip

That's pretty much what I ended up doing. The ID field is just before the item name field. The item field is uneditable (it's a look up from the ID), and when you click in it, a script selects the ID field instead. So you always edit the ID field and the item name gets looked up along with the price. Thanks for the suggestion :)

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

Ok, well that was my concern so that's good to know. I'm still not quite sure how to use this method though, without having the ID as a field on the portal. I can get it to work if the user is selecting the ID from the value list, and I can hide that ID in the value list, but the ID field itself still has to be on the portal right?

I was just playing around with that as a possible solution. Have you seen this situation before, and is that the common way around it? If I make the INVENTORY::ItemName field validated to be unique then that will work but it negates the purpose of the Primary ID field. I thought I was just missing something and there was some way to do this without the user ever seeing the primary ID.

I'm trying to relate an INVENTORY table to an INVOICE table using a LINE ITEM table. All records in the INVENTORY table have a unique ID number, which seems like the best field to use to base the relationship on. The problem is, the users have no need to ever see that number and I don't want it taking up space on the LINE ITEM portal on the INVOICE layout. If I DO add the ID to the portal, then I can get the other fields to autofill using a lookup. But without the field on the portal, I can't find a way to let the user select the record by choosing the INVENTORY::ItemName and have the price populate, since that's not the field I use for the relationship. And if I change the relationship that just seems to make the unique ID pointless and could cause problems later if the ItemName is changed or another item has the same name. My current relationship is: INVENTORY:ID = LINE_ITEMS:ID LINE_ITEMS:INVOICE_NUM =I NVOICE_NUM -with only one occurance of the LINE_ITEMS table on the relationship graph. I have the relationship set to allow creation of records in the LINE_ITEMS table from either of the other two tables. And I have a value list assigned as a drop down that shows records from the INVENTORY table. So the user can select an item in the portal on the invoice but the price won't look up unless I add the ID field to the portal and select the right record via the ID instead of the name. So, how do you base a relationship on a primary key and use that to select a record in a portal if you can't see that primary key field?

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.

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?