Is this type of basic database function available in Memento?

[Tom]

Hi again. OK I have a couple of questions, which I've searched and searched in the docs and this forum but yet can't seem to find an answer. Hopefully I can explain it in a way that makes sense.

So let's say, for simplicities sake, I have two Memento tables (Libraries) in my 'database' - one is an Orders table and the other is a Products table. These are structured as follows:

Orders
-----------
Country (Single choice list)
Product Name (Link-to-entry Products via 'Product Name")
Qty Integer
Manufacturer       Text
Product ID Num   Text
Description Text
etc. etc. etc.

Products
--------------
Country Text
Product Name as above in Orders
Inventory Amount Ditto
Manufacturer ..
Product ID Num ..
Description ..
etc. etc. etc.

So the main table is the Orders table; that's the one the user opens and uses. Products is kinda like a lookup table. And I know this example isn't the best for referential integrity or only keeping one copy of data in a table, but it illustrates my point and questions. So first question:

1. The user opens the Orders table and create a new record. First thing on that new record is they have to selecting a country (via a drop down) - let's say they chose Canada.  Once they do that, what I want to do is somehow get Memento to 'filter' the "Product Name" link-to-entry field  so that when they click 'select' for the Product Name linked field next in Orders, it ONLY shows them Products that are available for Canada (or whatever country they choose).   I know I could easily write a script to do this, but how can I get it to filter the product name link for the chosen country IN REAL TIME as soon as they choose their country? Only way I can see to do this is via JavaScript just before or after they save the entire record itself... That's not what I want though. Isn't there a way to filter a link-to-entry field based upon another field as they are entering their data?

2. Let's say I figure out issue #1. Now they select, using the Product Name link-to-entry field, a single product from Products. Again, what I'd like to do is have Memento, after they select their product, to use the other fields  in that chosen Product linked record (which will be previously filled in) and set/initialize the value in same named fields in the Orders table. I.E. Once they pick product Widget A, Memento would take the Manufacturer, Product ID Num, Description, etc. values from the chosen 'Widget A' Products record and set the same field-named value in now being entered Order record. Basically Products is like a lookup table, using it's data to 'pre-fill', so to speak, the Order table record being entered.  Again, I know how to write a script to do this, but where, when and how do I place it? I can't see a way to do that in real time -- Only way I see is to have the user leave those fields blank and then run the script to fill them in just before or after the record is saved. Is there no way to do this type of thing when the user is actually entering the new data? Looking at the comments and documentation, and I'm probably being ignorant, but I just can't see a way to do this very basic type of database function in Memento!

Sorry for what probably is a 'dumb' question;  but I've tried at length to figure it out on my own with no luck - so I figured "Why Not?" - I might as well go ahead and ask. I hope I'm just ignorant and there is an easy way to do this type of thing. Thanks in advance.

-T

[Bill Crews]

First of all, there are no dumb questions. Secondly, knowing Memento JavaScript, I tend to think it easy to do things that aren't easy for others. But the good news is that over time, the developer has added a field type here or there to help people avoid JavaScript or Calc script, and one of them will I think solve your #2 problem and your duplicate data problem all in one go. The Lookup field is one you can place in your Products table. You provide the name of the link-to-entry field (like "Product Name" (I call it just Product.)) and the field name (like "Product ID Num"), it does an automatic lookup of the value in Orders, keeping only the copy of the data, so if you ever have to change a Product ID Num in Orders, it'll automatically change to match in Products.

Now, why one would want the master copy of a product ID in an orders table, I don't know. Wouldn't you go to Products to change a Product ID and let the orders get automatically changed by Memento? The same for Manufacturer and Description.

Now, is the Country logically associated with the point of order (or sale), or is it associated with the product itself or the manufacturer? I would keep it where it belongs and use a Lookup field to get it to the other place. I think the IRS will care which.

I assume Qty is the quantity ordered and Inventory Amount the quantity of product on hand. If so, that's fine.

By the way, what I do is link one-to-many an order to its product. (Actually, I have a separate Order Line Items table, link line items to a product and the order, and the order to the customer.) This allows me to view orders in Orders or Order Line Items and see products & customers in an entries list screen where I can use sorting, grouping, filtering, charting, & aggregation instead of being stuck trying to look at them in the link list in an entry view card. There's little joy in doing that. But that fits my situation. It may not fit yours.

--
You received this message because you are subscribed to the Google Groups "mementodatabase" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mementodataba...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/0e84168e-2d59-409e-b5ae-6e8144302315n%40googlegroups.com.

[Er Mo]

Hallo
zu 1. Ich würde die " Produkt " Bibliothek nach Ländern Filter und diese als Registerkarte anlegen . So kann schnell zwischen den Ländern gewechselt werten .

zu 2.Es gibt den Feldtyp " Schaltfläche " die ein Skript ausfüren kann . Mit diesen kann ich das Ausgewälte Produkt nehmen und die restlichen Daten in die Felder Schreiben . So sind sie nacht den Drucken sichtbar und werden mit gespeichert .

Hello
to 1. I would filter the "Product" library by country and create it as a tab. So you can quickly switch between countries.
to 2.There is a field type "button" that can run a script. With these I can take the selected product and write the remaining data in the fields. So they are visible after the prints and are saved with them.

Ernst

[Tom]

Bill, thank you for your explanation on that - it helped greatly. Actually, after reading your message, it hit me suddenly HOW to use the lookup field!  In a frenzy of activity, I added the necessary lookup fields... And it worked, and worked really well! Now all my referenced fields are showing up in the main table, and NOT taking up duplicate space. And I have to apologize - I used the metaphor of Orders and Products because I thought that would be easier to describe than what I am really doing - which is designing a pretty advanced Coin Collecting database for myself. I have one main table describing each individual coin in the collection (i.e. my fictious Orders) and a fairly LARGE table (along with a few others) that has all the attributes of a specific coin series, like series name, mint marks, size, weight, designer, obverse/reverse images, etc. (i.e.  My  fictious  Products table) When one pulls up a specific coin in their collection, it now (with the LOOKUP fields) displays all the info/attributes of that series of coin (for instance, a Lincoln Cent 1909-1943) as referenced fields rather than duplicating that info in the main Coin Collection table.

The only thing  that still escapes me (at least for now anyway) is to figure out how to have the user select the country (first field they enter - a drop down) and then automatically - as they continue entering info for that new record they are adding - when they click the Select button for the link-to-entry field (linking to attributes via Coin Series field), that linked field ONLY shows the series links for the country they picked at the start of their entry. In DB terms, I want to have the link-to-entry field, when clicked, do something like 'SELECT * FROM Coin_Attributes WHERE COUNTRY = "e.field('Country')". Does that make sense?  I would think something like this could be done in Memento, just not seeing how to do it at the moment. Anyone have any insights there?

Thanks a bunch!

-T

[Bill Crews]

Ernst answered a similar question in a previous forum post within the last month, and I think his suggestion was to select to set the item values as tabs. Then you can tap a tab and see only those entries, without even defining a filter.

I haven't done it myself, but it sounds sensible to me. Find his list regarding "tabs" in the forum.

[Tom]

Oops, I know this is old but I forgot to get back to you: Thanks to you (and all others who responded)  everything really came together. I now have a really cool Coin Collection DB that I think rivals any "commercial" product out there! Now I'm just trying to load it up with my old exported data - which turns out to be harder than building the db itself! Anyway, thanks so much for your help.

Oops, I do have ONE more question: ;-) The lookup fields worked really well. However, I do notice this: In my coins main table, I have a link to a "Coins Attribute" table which has various data on each coin series, including images (like coin face, edge of coin, etc.) I use that to pull certain info off to display on the input screen (hence the lookup field). But look up fields for images, instead of getting the image itself  in the lookup field from the attributes table, I instead get basically just a directory/filename label, which I assume is the path and filename of the image? Is there any way to retrieve, from the image field in a linked record, in a lookup field, the REAL image itself? Not just the path/filename?

Again, thanks in advance.

-T