Base: Entering data in a form, in a field linked to another table, how to add a new record in second table?

Inkblotr · January 16, 2019, 2:41am

When entering a new record in the “Sales Entry-Maintenance” form, I get to the “Sold_To” field (it is currently a list box) which displays the customer table records just fine. But suppose this sale is to a new customer, not in the Customer table already, how can I program the ability to add a new record in the Customer table, AND do it without having to abandon the current new Sales record, exit the Sales form and go into the “Customer Maintenance” form to add it there?

I still feel like I’m new to OOBase, but I’m gaining experience daily…

Thank you very much, in advance!

Ratslinger · January 16, 2019, 3:51am

Hello,

This is the ‘horse-before-the-cart’ syndrome. Development before design. Happens much more often than thought.

Unfortunately, with the current process, to get this new customer into the list box and not lose the existing data would require a macro. You would enter the new customer and the macro would refresh just that control.

My choice would have a main form for the selected customer and a sub form for the sales (mentioned in comment of last question). Not only would it eliminate the need to choose a customer but it would automatically place this number in the appropriate field. So now before starting a sale first, you must choose or enter a customer. How this is all done would depend upon the method used.

Have attached a sample with your original form modified. Have added a button (macro attached to it - very small) which will refresh the list in the list box. Also modified the list selection to present in alphabetical sequence by last name. Now you can add a new customer in the middle of entering an order then come back to the order, refresh the list & continue on.

Sample ------- RefreshListBox.odb

Note: You must enable macros for this to work.

Inkblotr · January 16, 2019, 4:14am

@Ratslinger You made me laugh out loud! After I posted this question, I went to the Handbook example Media_with_Macros.ODB file and started trying (again) to analyze it and that effort took me into macros! I’m a retired IT infrastructure nerd, having started with dBase, Clipper, FoxBase, etc in the DOS days before Windows. I skipped learning BASIC because I went deep into DataCenter Infrastructure engineering. Also never picked up Windows scripting. So now I guess I have to pay the piper and learn both SQL and BASIC! Ugh! BTW, horses always come before carts, unless they are backing up, I think you meant “cart-before-the-horse” I understand your meaning and am “guilty as charged”! Thank you for going to the trouble this time, especially! I will study what you’ve done and see where that takes me (deeper into the rabbit hole, no doubt)!

Thank you very much again! I appreciate you and your efforts to help me.

Ratslinger · January 16, 2019, 4:25am

Guilty of placing horse in wrong place! Just trying to get you an answer and thinking (or NOT thinking) too fast. You’re three hours ahead of me. Guess I was just trying to catch up. BTW I started on 16K mainframes.

I’m sure you want to hear this, but basic doesn’t need to be the choice. Python is used quite often, then have seen some Javascript, Java and C++ used. You will find Basic samples most often.

Inkblotr · January 16, 2019, 4:35am

@Ratslinger Is there something I can add to the SQL query that displays the Customer_LN + Customer_FN that will trim the trailing spaces out between the names?

Also, is this how the process would work when entering a new Sales record? Finding I need to add a new Customer I will a) minimize the Sales form, b) launch the Customer form, c) enter the new Customer record, d) close the Customer form, e) return to the Sales form, f) click the refresh button to find the new customer record? This is a do-able process…

Just remembered, before my dBase experience, I wrote “macros” in Lotus 1-2-3. Wow, that was a long time ago… Makes me feel old…

Ratslinger · January 16, 2019, 5:06am

For the spaces LTRIM() removes spaces on left & RTRIM() removes spaces on right. So you need:

SELECT RTRIM("Customer_LN") + ' ' + RTRIM("Customer_FN"), "Customer_ID" FROM "Customer" ORDER BY ("Customer_LN" + "Customer_FN") ASC

Notice the added space between first & last name.

Yes, the stated process is correct.

My first code was in Easycoder!

Inkblotr · January 16, 2019, 3:04pm

Awesome - that’s what I needed. I woke up this morning thinking about RTRIM(), LTRIM(), and TRIM() since I used them back in my dBase years. It is nice to know they are still used. I guess some nomenclature is timeless.

Since you are 3 hours behind me, I suppose you live up North in another state that begins with “A”. I’ve never visited up there but I hear it is beautiful.

Easycoder, huh? I’ve had very little contact with mainframes during my career, except to provide them with network connectivity. I just looked up the word and found today there is a WordPress plug-in called EasyCoder. https://easycoder.software/

I’m about to post a question about the Handbook Media example, there’s an error or errors in the Macros that I have no expertise to fix. I may try to reuse the code though…

Thanks again!!!

Ratslinger · January 16, 2019, 5:31pm

Easycoder (I used in 60’s & early 70’s) is an assembler like language & the Honeywell equivalent of IBM’s Autocoder. Your link is something completely different. A bit off on the area - it’s SoCal.