AdityaBhandar
Qrew Member
Joined 4 years ago
User Profile
User Widgets
Contributions
Overwriting/Amending the records in a table
Hello To explain this, say there are four columns as below. In order to avoid entry of duplicates, I concatenated Customer and Sub ID and made it unique. Customer Sub ID Sales Key 10001 A1 $100 10001A1 10001 A2 $100 10001A2 10002 A3 $100 10002A3 10002 A2 $100 10002A2 10002 A1 $100 10002A1 Now, if I upload a CSV file to update sales for Cusomer: 10002 & sub id's A1& A2, QB rejects as duplicate records. Question: If I don't make it(customer & sub id) key, the table will get duplicate records. If I allow it, QB by default will not allow updates of sales for the key. How can I overcome this challenge? ------------------------------ Siva ------------------------------Error for Date(formula) field
Hello, can anyone please help me with this logic? Formula for field type: Date(forumla) If([Match]="Y",ToDate("GetFieldValues(GetRecords("{12.EX.'" & [NUM1] & "'}",[_DBID_UPLOAD]),15)"),ToDate("Jan 30, 2000")) Error: Expecting Datetime/workdate but found textlist I am trying to say, if field Match="Y", then use date from UPLOAD table by matching NUM1 in details with NUM1 in Overrides. Field ID 15 in table: UPLOAD is date. ------------------------------ Siva ------------------------------Re: Calling a field from another table
Jeremy, To try your formula I created a small example Table 1: Sales (DBID:_DBID_CUSTOMER_SALES) with Fields Customer, Sales in $ E.g.: Customer Salesin$ 1001 $100 Table 2: Customer with Fields: Customer (field id: 6), Havesales E.g.: Customer HAVESALES 1001 GetRecords("{6.EX.'" & [Customer] & "'}",[_DBID_CUSTOMER_SALES]) However the syntax says it is wrong. ------------------------------ Siva ------------------------------Re: Calling a field from another table
Hi Jeremy, What is x.EX? Table1: Field 9 should compare value derived from VLOOKUP from table 1 and write a specific value in Field 9. So, does your formula, GetFieldValues(GetRecords("{x.EX.'" & [Field 1] & "'}",[_DBID_TABLE_1]),y) derives value from table 1 into field 9 of table 2? Table 1 Field 8 Field 9 Vlookup Field 1 in table 2 Field id: x Field id: y Tabel 2 Field1 ------------------------------ Siva ------------------------------Re: Calling a field from another table
"Are you looking to pull one value from one record or pull up for example a summary or the most recent record's value, etc?" Yes Evan! this is exactly what I am trying to do. Mimicing Vlookup and based on the value, I want to initialize certain value. To keep it other way, If Vlookup from another table is X and if this matches with value in column C(same table), then write Y into the cell. ------------------------------ Siva ------------------------------Derive the field value from another table
Hello Can you please help me write the QB formula for below region derivation? Say there are two tables 1. Sales table: Customer Region Sales 1001 NA $100 1002 SA $200 1003 EU $300 1004 AS $400 How can derive Region from customer table. if it is Excel, I would have written as If(VLOOKUP(Customer,Customertable(Customer:Country),2,false)="US", "NA" If(VLOOKUP(Customer,Customertable(Customer:Country),2,false)="MX", "SA" etc 2. Customer table Customer Country 1001 US 1002 BR 1003 FR 1004 IN ------------------------------ Siva ------------------------------Re: Key field
Thank you for the reply Mark! So, QB does not give me an option while adding a field if I want to make that field a key in combination of other field? I remember seeing that option. Is it never there to start with? In my example, Step 1: There is a table with 3 fields: Region Customer Sales Step 2: user loads 10 records say as below from excel Region Customer Sales NA 1001 100 NA 1002 100 NA 1003 100 Eur 1004 100 Eur 1005 100 Eur 1006 100 Asia 1007 100 Asia 1008 100 Asia 1009 100 Aus 1010 100 Step 3: User loaded another excel file as below Region Customer Sales NA 1001 200 NA 1011 100 QB has to update one record and append one more record. Is that possible? ------------------------------ Siva ------------------------------Key field
Hello I remember QB asking if the field I am creating is a key field or not; but I am not able to get back that option. I can go to fields and change the key field. But I want to concatenate 2 fields and make it a key field. I saw this option too but I am not able to find it again. Region Customer Sales NA 1001 200 NA 1002 300 Key: Region + Customer If I upload a file with NA and 1001, I expect QB to not to create a new record or say duplicate but overwrite NA 1001 combination. Thanks! ------------------------------ Siva ------------------------------
